--- title: "SQL-209: Retrieve InjuryClaim" summary: Read operation against InjuryClaim via NHibernate ORM. status: draft owner: SAIF Doc Gen last_reviewed: 2026-09-24 library: SAIF.Services reference_type: package --- # 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](../database-tables.md#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` ```csharp // Services/SAIF.Services/Collections/CollectionsService.cs:1419 return UnitOfWork.GetRepository().Query().Single(x => x.ClaimNumber == claimNumber).Id; // Services/SAIF.Services/Collections/CollectionsService.cs:1383 taskData.Claim = UnitOfWork.GetRepository().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. ```sql -- 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. 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](../operations-processes.md) - CollectionsService.GetClaimId — see [Operations and processes](../operations-processes.md) ## Sources - `Services/SAIF.Services/Collections/CollectionsService.cs (L1383, L1419)`