Skip to content

SQL-340: Retrieve ThirdParty via GenerateThirdPartyDebtor

This ORM statement performs a read operation on ThirdParty, selecting rows for a case (Case.ID == caseId) filtered by whether Person is set, via IUnitOfWork.GetRepository<T>(), executed from Services/SAIF.Services/Collections/CollectionsService.cs (L1050, L1055). The SQL below is inferred from the LINQ expressions and the ThirdParty NHibernate mapping (THRDPARTY); it is not literal SQL text present in source. The x.Case.ID and x.Person navigations short-circuit to FK columns on THRDPARTY and never join THRDPARTY_CASE or PERSON.

Classification

Field Value
SQL ID SQL-340
Operation type Read
Execution method ORM (NHibernate LINQ)
Database Oracle
Schema [unqualified]**
Command type N/A (ORM-generated)
Business object ThirdParty
Criteria property N/A — ORM
Lifecycle method GenerateThirdPartyDebtor

Parameters

Parameter Direction Type Max length Description
:caseId Input long N/A Local caseId — result of SQL-341 (ThirdPartyCase.ID)

Tables

Table Access Columns used
THRDPARTY SELECT THRDPARTY_SEQ_NUM, CARRIER_CLAIM_NUM, CARRIER_POLICY_NUM, DISTRICT_ATTORNEY_NUM, IDENTIFIER_TEXT, CREATE_DATE, CREATE_USER, UPDATE_DATE, UPDATE_USER, UPDATE_NUM, ATTORNEY_FIRM_SEQ_NUM, CARRIER_FIRM_SEQ_NUM, THRDPARTY_CASE_SEQ_NUM, THRDPARTY_FIRM_SEQ_NUM, PERSON_SEQ_NUM

LINQ statement

Source: Services/SAIF.Services/Collections/CollectionsService.cs:1050

// Services/SAIF.Services/Collections/CollectionsService.cs:1050
thirdParties = UnitOfWork.GetRepository<ThirdParty>().Query()
    .Where(x => x.Case.ID == caseId && x.Person != null);

// Services/SAIF.Services/Collections/CollectionsService.cs:1055
thirdParties = UnitOfWork.GetRepository<ThirdParty>().Query()
             .Where(x => x.Case.ID == caseId && x.Person == null);

// Services/SAIF.Services/Collections/CollectionsService.cs:1059
foreach (var thirdParty in (thirdParties))

Sample SQL

Inferred SQL: This SQL was inferred from the LINQ statement above and the NHibernate mappings. It is not concrete SQL from source code; NHibernate generates the executed statement at runtime, so aliases, column order, join syntax, and bind-parameter names may differ.

-- Inferred from LINQ (NHibernate mapping translation); not concrete SQL
SELECT
  T.THRDPARTY_SEQ_NUM,
  T.CARRIER_CLAIM_NUM,
  T.CARRIER_POLICY_NUM,
  T.DISTRICT_ATTORNEY_NUM,
  T.IDENTIFIER_TEXT,
  T.CREATE_DATE,
  T.CREATE_USER,
  T.UPDATE_DATE,
  T.UPDATE_USER,
  T.UPDATE_NUM,
  T.ATTORNEY_FIRM_SEQ_NUM,
  T.CARRIER_FIRM_SEQ_NUM,
  T.THRDPARTY_CASE_SEQ_NUM,
  T.THRDPARTY_FIRM_SEQ_NUM,
  T.PERSON_SEQ_NUM
FROM THRDPARTY T
WHERE T.THRDPARTY_CASE_SEQ_NUM = :caseId
  AND T.PERSON_SEQ_NUM IS NOT NULL -- applied when collectionCase.CollectionCode.Code == "THRD" (line 1051); otherwise PERSON_SEQ_NUM IS NULL (line 1056)

Translation notes:

  • Branch-dependent: the two branches differ only in the PERSON_SEQ_NUM null test; materialized by foreach at line 1059.

Operations and processes

Sources

  • Services/SAIF.Services/Collections/CollectionsService.cs (L1050, L1055)