SQL-223: Retrieve LegalCase via SaveLegalBill

BillSaveService.SaveLegalBill loads LegalCase by primary key to attach it to legalBill.LegalCase. It runs only when the bill is new or its case ID changed (isNew || bill.CaseId != legalBill.LegalCase.Id).

Classification

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

Parameters

Parameter	Direction	Type	Max length	Description
:caseId	Input	long	N/A	bill.CaseId

Tables

Table	Access	Columns used
LEGAL_CASE	SELECT	all 17 mapped columns; filter LEGAL_CASE_SEQ_NUM

LINQ statement

Source: Services/SAIF.Services/Legal/Bills/BillSaveService.cs:324

legalBill.LegalCase = _unitOfWork.GetRepository<LegalData.LegalCase>()
    .Query().SingleOrDefault(x => x.Id == bill.CaseId);

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 LC.LEGAL_CASE_SEQ_NUM,
       LC.CLAIM_ID,
       LC.WCB_NUM,
       LC.DCBS_NUM,
       LC.OALJ_NUM,
       LC.OWCP_NUM,
       LC.BRB_NUM,
       LC.REQUEST_DATE,
       LC.CLAIM_IMAGE_ALERTS_FLAG,
       LC.CREATE_USER,
       LC.CREATE_DATE,
       LC.UPDATE_USER,
       LC.UPDATE_DATE,
       LC.MOST_RECENT_HEARING_SEQ_NUM,
       LC.CASE_STATUS_CODE,
       LC.WORKER_ATTY_PROV_LOC_ID,
       LC.WORKER_ATTY_FIRM_PROV_LOC_ID
FROM   LEGAL_CASE LC
WHERE  LC.LEGAL_CASE_SEQ_NUM = :caseId
-- NHibernate expects at most one row

Translation notes: - SingleOrDefault/Single add no row limit; NHibernate throws if more than one row is returned. - Runs only when isNew || bill.CaseId != legalBill.LegalCase.Id (L323). That guard reads the FK identifier of the already-loaded bill in memory.

Operations and processes

• BillSaveService.SaveLegalBill — see Operations and processes

Sources

• Services/SAIF.Services/Legal/Bills/BillSaveService.cs (L324)