SQL-209: Retrieve InjuryClaim¶

This page covers two related InjuryClaim reads in CollectionsService: GetClaimId looks up a claim by its claim number and returns its identifier, and CreateTask looks up a claim by identifier using the result of GetClaimId(...). Because GetClaimId(...) does not reference the outer query's lambda parameter, NHibernate's partial evaluator runs it client-side before translating the outer predicate — so the GetClaimId statement executes first, and its result becomes the bind parameter for the CreateTask statement. Both are issued through NHibernate LINQ against the InjuryClaim entity from Services/SAIF.Services/Collections/CollectionsService.cs:1419, 1383.

Classification¶

Field	Value
SQL ID	SQL-209
Operation type	Read
Execution method	ORM (NHibernate LINQ)
Database	Oracle
Schema	[unqualified]**
Command type	N/A (ORM-generated)
Business object	`CollectionsService`
Criteria property	N/A — ORM
Lifecycle method	`CreateTask, GetClaimId`

Parameters¶

Parameter	Direction	Type	Max length	Description
`:claimNumber`	Input	string	N/A	(1) `lcCase.SaifClaimNum`
`:claimId`	Input	long	N/A	(2) result of `GetClaimId(lcCase.SaifClaimNum)`, computed client-side

Tables¶

Table	Access	Columns used
CL_INJURY_CLAIM	SELECT	all 100 mapped columns (entity load); filter on CLAIM_ID / SAIF_CLAIM_NUM

LINQ statement¶

Source: Services/SAIF.Services/Collections/CollectionsService.cs:1419, Services/SAIF.Services/Collections/CollectionsService.cs:1383

// Services/SAIF.Services/Collections/CollectionsService.cs:1419
return UnitOfWork.GetRepository<InjuryClaim>().Query().Single(x => x.ClaimNumber == claimNumber).Id;

// Services/SAIF.Services/Collections/CollectionsService.cs:1383
taskData.Claim = UnitOfWork.GetRepository<SAIF.Data.Model.Claims.InjuryClaim>().Query().SingleOrDefault(x => x.Id == GetClaimId(lcCase.SaifClaimNum));

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) L1419 — GetClaimId (evaluated FIRST: it is invoked client-side while
--     NHibernate partially evaluates the L1383 predicate, because it does not reference x)
SELECT IC.CLAIM_ID,
       IC.UPDATE_NUM,
       IC.SAIF_CLAIM_NUM,
       IC.SAIF_CLAIM_CHK_VALUE,
       IC.WCD_CLAIM_NUM,
       IC.OWCP_CLAIM_NUM,
       IC.UPDATE_DATE,
       IC.UPDATE_USER,
       IC.RECEIVED_DATE,
       IC.RECEIVED_801_DATE,
       IC.TIN_AUTHORIZED_USE_FLAG,
       IC.AGE_DOI_YEARS,
       IC.GED_FLAG_CODE,
       IC.EDUCATION_YEARS,
       IC.EMPR_RELATIVE_FLAG,
       IC.INTERESTED_PARTY_FLAG_CODE,
       IC.WORKER_DEPARTMENT_DESCR,
       IC.EMPR_PROVIDED_WORKER_AREA_TXT,
       IC.EMPR_PROV_WORKER_DEPT_DESCR,
       IC.EMPR_PROV_PAYROLL_CLASS_CODE,
       IC.EMPR_PROV_PAYROLL_SUB_CODE,
       IC.EMPR_PROVIDED_SHIFT_CODE,
       IC.SHIFT_START_TIME,
       IC.SHIFT_END_TIME,
       IC.SAIF_PROV_PAYROLL_CLASS_CODE,
       IC.SAIF_PROV_PAYROLL_SUB_CODE,
       IC.WORKER_TENURE_DAY,
       IC.WORKER_TENURE_MONTH,
       IC.WORKER_TENURE_YEAR,
       IC.INPATIENT_FLAG_CODE,
       IC.INITIAL_TREATMENT_DATE,
       IC.EMPLOYMENT_INJURY_CODE,
       IC.HANDICAP_WORKER_RESERVE_FLAG,
       IC.MULTIPLE_WORKER_FLAG_CODE,
       IC.NCE_DATA_FLAG,
       IC.KNOWLEDGE_DATE,
       IC.INMATE_NUM,
       IC.INMATE_SUPERVISOR_KNOW_DATE,
       IC.DISABILITY_DATE,
       IC.WORKER_HIRE_DATE,
       IC.LEAVE_WORK_TIME,
       IC.POLICY_SUB_CODE,
       IC.CDA_FLAG,
       IC.LAST_RESERVE_REVIEW_DATE,
       IC.LAST_RESERVE_REVIEW_USER,
       IC.LAST_VOC_RESERVE_REVIEW_DATE,
       IC.LAST_VOC_RESERVE_REVIEW_USER,
       IC.AWARD_AUTO_RECALCULATION_FL_CD,
       IC.WORKER_OCCUPATION_DESCR,
       IC.ACTIVE_CLAIM_FLAG,
       IC.INJURY_DATE,
       IC.INJURY_TIME,
       IC.HARDWARE_CAUSED_FLAG_CODE,
       IC.PERSON_CAUSED_FLAG_CODE,
       IC.CITY_NAME,
       IC.STATE_CODE,
       IC.ZIP_CODE,
       IC.ZIP_CODE_EXT,
       IC.HIGH_SEAS_FLAG,
       IC.SAIF_EVENT_DESCR,
       IC.HEALTH_INSURANCE_PROV_NAME,
       IC.HEALTH_INSURANCE_ID,
       IC.HEALTH_INSURANCE_GROUP_NUM,
       IC.MULTIPLE_EMPR_FLAG,
       IC.MULTIPLE_EMPR_WAGE_AMT,
       IC.LEAVE_WORK_DATE,
       IC.COUNTY_CODE,
       IC.FEDERAL_8F_FLAG,
       IC.CDA_ISSUE_DATE,
       IC.DCS_ISSUE_DATE,
       IC.MEDICARE_ORM_TERM_DATE,
       IC.PREFERRED_WORKER_ID,
       IC.OUTSTANDING_RESERVE_FLAG,
       IC.EXTERNAL_VIEW_FLAG,
       IC.FULLY_FRAUDULENT_FLAG,
       IC.DENIED_FINAL_FLAG,
       IC.POLICY_NUM,
       IC.CLAIM_RESP_CODE,
       IC.COMPENSABILITY_RESP_CODE,
       IC.FORM_CODE,
       IC.CLAIM_STATUS_CODE,
       IC.CLAIM_TYPE_CODE,
       IC.JURISDICTION_CODE,
       IC.PRIMARY_BODY_PART_CODE,
       IC.PRIMARY_BODY_SIDE_CODE,
       IC.PRIMARY_INJURY_NATURE_CODE,
       IC.COVERAGE_CODE,
       IC.DCS_FULL_PART_CODE,
       IC.FEDERAL_INJURY_LOCATION_CODE,
       IC.OWCP_SERVICE_OFFICE_CODE,
       IC.INJURED_ON_PREMISES_CODE,
       IC.INJURY_EVENT_CODE,
       IC.INJURY_SOURCE_CODE,
       IC.INJ_OCCUPATIONAL_DISEASE_CODE,
       IC.REQUEST_TYPE_CODE,
       IC.MCO_ELIGIBILITY_REASON_CODE,
       IC.MCO_ELIGIBILITY_STATUS_CODE,
       IC.VOCATIONAL_RESP_CODE,
       IC.WORKER_ID,
       IC.MCO_ID
FROM   CL_INJURY_CLAIM IC
WHERE  IC.SAIF_CLAIM_NUM = :claimNumber;

-- (2) L1383 — CreateTask
SELECT IC.<all 100 mapped CL_INJURY_CLAIM columns, as in (1)>
FROM   CL_INJURY_CLAIM IC
WHERE  IC.CLAIM_ID = :claimId;   -- :claimId = result of (1).Id

Translation notes: - SingleOrDefault/Single add no row limit; NHibernate throws if more than one row is returned. - GetClaimId(...) at L1383 is a local method call that does not reference the lambda parameter. NHibernate's partial evaluator therefore runs it before translation, which executes statement (1). Its .Id is read in memory from the loaded entity (L1419). Single throws if no claim matches. - The two statements are executed back-to-back for the same claim, so the entity is effectively loaded twice (the second hit may be served by the session first-level cache for the entity itself, but the LINQ query is still issued).

Operations and processes¶

CollectionsService.CreateTask — see Operations and processes
CollectionsService.GetClaimId — see Operations and processes

Sources¶

Services/SAIF.Services/Collections/CollectionsService.cs (L1383, L1419)