SQL-227: Retrieve LegalCase via SaveSubpoena¶

SubpoenaSaveService.SaveSubpoena loads LegalCase by primary key to attach it to dataSubpoena.LegalCase. It runs only when the subpoena is new or its case ID changed (isNew || subpoena.CaseId != dataSubpoena.LegalCase.Id).

Classification¶

Field	Value
SQL ID	SQL-227
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	`SaveSubpoena`

Parameters¶

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

Tables¶

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

dataSubpoena.LegalCase.WorkerAttorneyFirm.Addresses.Any() (L127-129) is evaluated in memory and triggers separate lazy loads of the attorney-firm provider location and its addresses; these are not joined here.

LINQ statement¶

Source: Services/SAIF.Services/Legal/Subpoenas/SubpoenaSaveService.cs:124

dataSubpoena.LegalCase = _unitOfWork.GetRepository<LegalData.LegalCase>()
    .Query().SingleOrDefault(x => x.Id == subpoena.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 || subpoena.CaseId != dataSubpoena.LegalCase.Id (L122).

SQL-227: Retrieve LegalCase via SaveSubpoena¶

Classification¶

Parameters¶

Tables¶

LINQ statement¶

Sample SQL¶

Operations and processes¶

Business rules¶

Sources¶