SQL-108: Retrieve Case

This ORM statement performs a read operation on Case via IUnitOfWork.GetRepository<T>(), executed from Services/SAIF.Services/Collections/CollectionsService.cs (L122, L139, L883, L1134, L1153).

Classification

Field	Value
SQL ID	SQL-108
Operation type	Read
Execution method	ORM (NHibernate LINQ)
Database	Oracle
Schema	[unqualified]**
Command type	N/A (ORM-generated)
Business object	Case
Criteria property	N/A — ORM
Lifecycle method	GetCaseDataById, GetCaseSummaryById, GetRequestById, SaveRequest

Parameters

Parameter	Direction	Type	Max length	Description
caseId	Input	**	N/A	x.Id == caseId → column LEGAL_COLLECTION_SEQ_NUM

Tables

Table	Access	Columns used
LEGAL_COLLECTION	SELECT	LEGAL_COLLECTION_SEQ_NUM, UPDATE_NUM, POLICY_NUM, SAIF_CLAIM_NUM, RECEIVED_DATE, RECORDING_COUNTY_CODE, STATE, COURT_CASE_NUM, CURRENT_BALANCE_AMT, UPDATE_USER, UPDATE_DATE, CREDIT_REP_USER, INTEREST_CALC_OPTION_CODE, INTEREST_ANNUAL_RATE, JUDGMENT_CODE, JUDGMENT_YEARS, LEGAL_COLLECTION_CODE, LEGAL_COLLECTION_STATUS_CODE

LINQ statement

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

// Services/SAIF.Services/Collections/CollectionsService.cs:122-124
            var caseData = UnitOfWork.GetRepository<DataModel.Case>()
                .Query()
                .SingleOrDefault(x => x.Id == caseId) ?? new DataModel.Case();

// Services/SAIF.Services/Collections/CollectionsService.cs:139-140
            var caseData = UnitOfWork.GetRepository<DataModel.Case>()
                .Query().Where(x => x.Id == caseId).ToList().SingleOrDefault() ?? new DataModel.Case();

// Services/SAIF.Services/Collections/CollectionsService.cs:883-884
            var caseData = UnitOfWork.GetRepository<DataModel.Case>()
                .Query().SingleOrDefault(x => x.Id == caseId) ?? new DataModel.Case();

// Services/SAIF.Services/Collections/CollectionsService.cs:1134-1135
                var tmpCaseData = UnitOfWork.GetRepository<DataModel.Case>()
                    .Query().SingleOrDefault(x => x.Id == caseId);

// Services/SAIF.Services/Collections/CollectionsService.cs:1153-1154
                var caseData = UnitOfWork.GetRepository<DataModel.Case>()
                                .Query().SingleOrDefault(x => x.Id == caseId) ?? new DataModel.Case();

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
-- Services/SAIF.Services/Collections/CollectionsService.cs:122
SELECT
  LC.LEGAL_COLLECTION_SEQ_NUM,
  LC.UPDATE_NUM,
  LC.POLICY_NUM,
  LC.SAIF_CLAIM_NUM,
  LC.RECEIVED_DATE,
  LC.RECORDING_COUNTY_CODE,
  LC.STATE,
  LC.COURT_CASE_NUM,
  LC.CURRENT_BALANCE_AMT,
  LC.UPDATE_USER,
  LC.UPDATE_DATE,
  LC.CREDIT_REP_USER,
  LC.INTEREST_CALC_OPTION_CODE,
  LC.INTEREST_ANNUAL_RATE,
  LC.JUDGMENT_CODE,
  LC.JUDGMENT_YEARS,
  LC.LEGAL_COLLECTION_CODE,
  LC.LEGAL_COLLECTION_STATUS_CODE
FROM LEGAL_COLLECTION LC
WHERE LC.LEGAL_COLLECTION_SEQ_NUM = :caseId
-- NHibernate expects at most one row

-- Services/SAIF.Services/Collections/CollectionsService.cs:139
SELECT
  LC.LEGAL_COLLECTION_SEQ_NUM,
  LC.UPDATE_NUM,
  LC.POLICY_NUM,
  LC.SAIF_CLAIM_NUM,
  LC.RECEIVED_DATE,
  LC.RECORDING_COUNTY_CODE,
  LC.STATE,
  LC.COURT_CASE_NUM,
  LC.CURRENT_BALANCE_AMT,
  LC.UPDATE_USER,
  LC.UPDATE_DATE,
  LC.CREDIT_REP_USER,
  LC.INTEREST_CALC_OPTION_CODE,
  LC.INTEREST_ANNUAL_RATE,
  LC.JUDGMENT_CODE,
  LC.JUDGMENT_YEARS,
  LC.LEGAL_COLLECTION_CODE,
  LC.LEGAL_COLLECTION_STATUS_CODE
FROM LEGAL_COLLECTION LC
WHERE LC.LEGAL_COLLECTION_SEQ_NUM = :caseId

-- Services/SAIF.Services/Collections/CollectionsService.cs:883
SELECT
  LC.LEGAL_COLLECTION_SEQ_NUM,
  LC.UPDATE_NUM,
  LC.POLICY_NUM,
  LC.SAIF_CLAIM_NUM,
  LC.RECEIVED_DATE,
  LC.RECORDING_COUNTY_CODE,
  LC.STATE,
  LC.COURT_CASE_NUM,
  LC.CURRENT_BALANCE_AMT,
  LC.UPDATE_USER,
  LC.UPDATE_DATE,
  LC.CREDIT_REP_USER,
  LC.INTEREST_CALC_OPTION_CODE,
  LC.INTEREST_ANNUAL_RATE,
  LC.JUDGMENT_CODE,
  LC.JUDGMENT_YEARS,
  LC.LEGAL_COLLECTION_CODE,
  LC.LEGAL_COLLECTION_STATUS_CODE
FROM LEGAL_COLLECTION LC
WHERE LC.LEGAL_COLLECTION_SEQ_NUM = :caseId
-- NHibernate expects at most one row

-- Services/SAIF.Services/Collections/CollectionsService.cs:1134
SELECT
  LC.LEGAL_COLLECTION_SEQ_NUM,
  LC.UPDATE_NUM,
  LC.POLICY_NUM,
  LC.SAIF_CLAIM_NUM,
  LC.RECEIVED_DATE,
  LC.RECORDING_COUNTY_CODE,
  LC.STATE,
  LC.COURT_CASE_NUM,
  LC.CURRENT_BALANCE_AMT,
  LC.UPDATE_USER,
  LC.UPDATE_DATE,
  LC.CREDIT_REP_USER,
  LC.INTEREST_CALC_OPTION_CODE,
  LC.INTEREST_ANNUAL_RATE,
  LC.JUDGMENT_CODE,
  LC.JUDGMENT_YEARS,
  LC.LEGAL_COLLECTION_CODE,
  LC.LEGAL_COLLECTION_STATUS_CODE
FROM LEGAL_COLLECTION LC
WHERE LC.LEGAL_COLLECTION_SEQ_NUM = :caseId
-- NHibernate expects at most one row

-- Services/SAIF.Services/Collections/CollectionsService.cs:1153
SELECT
  LC.LEGAL_COLLECTION_SEQ_NUM,
  LC.UPDATE_NUM,
  LC.POLICY_NUM,
  LC.SAIF_CLAIM_NUM,
  LC.RECEIVED_DATE,
  LC.RECORDING_COUNTY_CODE,
  LC.STATE,
  LC.COURT_CASE_NUM,
  LC.CURRENT_BALANCE_AMT,
  LC.UPDATE_USER,
  LC.UPDATE_DATE,
  LC.CREDIT_REP_USER,
  LC.INTEREST_CALC_OPTION_CODE,
  LC.INTEREST_ANNUAL_RATE,
  LC.JUDGMENT_CODE,
  LC.JUDGMENT_YEARS,
  LC.LEGAL_COLLECTION_CODE,
  LC.LEGAL_COLLECTION_STATUS_CODE
FROM LEGAL_COLLECTION LC
WHERE LC.LEGAL_COLLECTION_SEQ_NUM = :caseId
-- NHibernate expects at most one row

Translation notes: - L139-140 materializes with .ToList(); the following .SingleOrDefault() and ?? new DataModel.Case() run in memory (no at-most-one-row expectation at SQL level). - Case has HasMany Activities/Debtors/Transactions with cascade (CaseMap.cs); collections load lazily with their own statements and are not part of this SELECT. References (CREDIT_REP_USER, INTEREST_CALC_OPTION_CODE, INTEREST_ANNUAL_RATE, JUDGMENT_CODE, JUDGMENT_YEARS, LEGAL_COLLECTION_CODE, LEGAL_COLLECTION_STATUS_CODE) contribute FK columns only; none of the referenced tables is joined.

Operations and processes

• CollectionsService.GetCaseDataById — see Operations and processes
• CollectionsService.GetCaseSummaryById — see Operations and processes
• CollectionsService.GetRequestById — see Operations and processes
• CollectionsService.SaveRequest — see Operations and processes

Sources

• Services/SAIF.Services/Collections/CollectionsService.cs (L122, L139, L883, L1134, L1153)