SQL-225: Retrieve LegalCase for opposing counsel and image-alert checks

LegalService.GetOpposingCounselInfo loads a LegalCase by primary key. LegalCaseExistsAndHasImageAlertsFlag runs a separate deferred query, counting LegalCase rows for a claim ID where the image-alerts flag is set.

Classification

Field	Value
SQL ID	SQL-225
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	GetOpposingCounselInfo, LegalCaseExistsAndHasImageAlertsFlag

Parameters

Parameter	Direction	Type	Max length	Description
:caseId	Input	long	N/A	statement (1) filters LEGAL_CASE_SEQ_NUM by primary key
:claimId	Input	long	N/A	statement (2) filters CLAIM_ID

Tables

Table	Access	Columns used
LEGAL_CASE	SELECT	all 17 mapped columns (statement 1); CLAIM_ID, CLAIM_IMAGE_ALERTS_FLAG (statement 2)

legalCase.WorkerAttorneyFirm (L187 onward) is used in memory and lazy-loads the provider location through WORKER_ATTY_FIRM_PROV_LOC_ID in separate statements; it is not joined here.

LINQ statement

Source: Services/SAIF.Services/Legal/LegalService.cs:186, also :216.

// Services/SAIF.Services/Legal/LegalService.cs:186
var legalCase = _unitOfWork.GetRepository<LegalCase>().Query().FirstOrDefault(x => x.Id == caseId);

// Services/SAIF.Services/Legal/LegalService.cs:216
var legalCases = _unitOfWork.GetRepository<LegalCase>()
    .Query().Where(x => x.ClaimId == claimId && x.IsImageAlert);

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
-- (1) L186 — GetOpposingCounselInfo
SELECT * FROM (
  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
) WHERE ROWNUM <= 1

-- (2) L216-219 — LegalCaseExistsAndHasImageAlertsFlag (deferred; executed by .Count() at L219)
SELECT COUNT(*)
FROM   LEGAL_CASE LC
WHERE  LC.CLAIM_ID = :claimId
  AND  LC.CLAIM_IMAGE_ALERTS_FLAG = 'Y'

Translation notes: - FirstOrDefault → Oracle10 dialect wraps the query in SELECT * FROM (...) WHERE ROWNUM <= 1. - (2): legalCases == null is always false for an IQueryable, so .Count() always executes and emits COUNT(*). The boolean IsImageAlert is stored as a Y/N flag column; the literal assumes the codebase's YesNo convention — verify the CLAIM_IMAGE_ALERTS_FLAG custom type in LegalCaseMap.

Operations and processes

• LegalService.GetOpposingCounselInfo — see Operations and processes
• LegalService.LegalCaseExistsAndHasImageAlertsFlag — see Operations and processes

Sources

• Services/SAIF.Services/Legal/LegalService.cs (L186, L216)