Skip to content

SQL-224: Retrieve LegalCase by primary key

LegalNotesService.CreateNote and FormatCaseHeader both load a LegalCase by primary key — the former from note.CaseId, the latter from a caseId parameter. Both call sites issue the identical statement.

Classification

Field Value
SQL ID SQL-224
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 CreateNote, FormatCaseHeader

Parameters

Parameter Direction Type Max length Description
:caseId Input long N/A note.CaseId (L221) / caseId (L290)

Tables

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

legalCase.Claim.ClaimNumber / SaifClaimCheckCharacter (L293-296) and the AutoMapper CaseInfo mapping (L291) read navigations in memory, triggering separate lazy loads (for example CL_INJURY_CLAIM by CLAIM_ID) that are not part of this statement.

LINQ statement

Source: Services/SAIF.Services/Legal/Notes/LegalNotesService.cs:221, also :290.

// Services/SAIF.Services/Legal/Notes/LegalNotesService.cs:221
var legalCase = UnitOfWork.GetRepository<LegalCase>().Query().SingleOrDefault(x => x.Id == note.CaseId);

// Services/SAIF.Services/Legal/Notes/LegalNotesService.cs:290
var legalCase = UnitOfWork.GetRepository<LegalCase>().Query().SingleOrDefault(x => x.Id == 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. - Both call sites issue the same statement.

Operations and processes

Sources

  • Services/SAIF.Services/Legal/Notes/LegalNotesService.cs (L221, L290)