---
title: "SQL-225: Retrieve LegalCase for opposing counsel and image-alert checks"
summary: Load a LegalCase by primary key, and separately count LegalCase rows by claim ID with the image-alerts flag set.
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-09-24
library: SAIF.Services
reference_type: package
---

# 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](../database-tables.md#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`.

```csharp
// 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.

```sql
-- 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](../operations-processes.md)
- LegalService.LegalCaseExistsAndHasImageAlertsFlag — see [Operations and processes](../operations-processes.md)

## Sources
- `Services/SAIF.Services/Legal/LegalService.cs (L186, L216)`