---
title: "SQL-218: Retrieve LegalBill"
summary: Read operation against LegalBill via NHibernate ORM.
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-09-24
library: SAIF.Services
reference_type: package
---

# SQL-218: Retrieve LegalBill

This page covers four related `LegalBill` reads in `BillSaveService`, all a single bill lookup by identifier: `DeleteBill` loads the bill to delete, `ResetParentBill` loads the parent bill (twice, at two call sites), and `SaveLegalBill` loads the existing bill being saved (falling back to a new instance when not found). All are issued through NHibernate LINQ against the `LegalBill` entity from `Services/SAIF.Services/Legal/Bills/BillSaveService.cs:100, 110, 315, 395`.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-218 |
| **Operation type** | Read |
| **Execution method** | ORM (NHibernate LINQ) |
| **Database** | Oracle |
| **Schema** | [unqualified]** |
| **Command type** | N/A (ORM-generated) |
| **Business object** | `BillSaveService` |
| **Criteria property** | N/A — ORM |
| **Lifecycle method** | `DeleteBill, ResetParentBill, SaveLegalBill` |

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:id` | Input | long | N/A | (1) `billId`; (2) `parentBillId`; (3) `bill.Id`; (4) `parentBillId` |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [LEGAL_BILL](../database-tables.md#legal_bill) | SELECT | all 21 mapped columns; filter LEGAL_BILL_SEQ_NUM |

## LINQ statement

Source: `Services/SAIF.Services/Legal/Bills/BillSaveService.cs:100`, `Services/SAIF.Services/Legal/Bills/BillSaveService.cs:110`, `Services/SAIF.Services/Legal/Bills/BillSaveService.cs:315`, `Services/SAIF.Services/Legal/Bills/BillSaveService.cs:395`

```csharp
// Services/SAIF.Services/Legal/Bills/BillSaveService.cs:100
var legalBill = _unitOfWork.GetRepository<BillData.LegalBill>().Query().FirstOrDefault(x => x.Id == billId);

// Services/SAIF.Services/Legal/Bills/BillSaveService.cs:110
_unitOfWork.GetRepository<BillData.LegalBill>()
    .Query()
    .FirstOrDefault(x => x.Id == parentBillId);

// Services/SAIF.Services/Legal/Bills/BillSaveService.cs:315
var legalBill = _unitOfWork.GetRepository<BillData.LegalBill>().Query().FirstOrDefault(x => x.Id == bill.Id) ??
            new BillData.LegalBill();

// Services/SAIF.Services/Legal/Bills/BillSaveService.cs:395
_unitOfWork.GetRepository<BillData.LegalBill>()
    .Query()
    .FirstOrDefault(x => x.Id == parentBillId);
```

## 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) L100 DeleteBill, (2) L110 parent bill, (3) L315 SaveLegalBill, (4) L395 ResetParentBill
SELECT * FROM (
  SELECT LB.LEGAL_BILL_SEQ_NUM,
       LB.UPDATE_NUM,
       LB.ISSUE_DATE,
       LB.RECEIVED_DATE,
       LB.APPROVED_DATE,
       LB.REQUESTED_BY_EMPLID,
       LB.APPROVED_BY_EMPLID,
       LB.INVOICE_TEXT,
       LB.COMMENT_TEXT,
       LB.PREPAYMENT_FLAG,
       LB.CREATE_USER,
       LB.CREATE_DATE,
       LB.UPDATE_USER,
       LB.UPDATE_DATE,
       LB.WITNESS_INFORMATION,
       LB.PARENT_LEGAL_BILL_SEQ_NUM,
       LB.LEGAL_CASE_SEQ_NUM,
       LB.BILL_STATUS_CODE,
       LB.BILLING_PROV_LOC_SEQ_NUM,
       LB.RENDERING_PROV_LOC_SEQ_NUM,
       LB.SPECIAL_HANDLING_CODE
  FROM   LEGAL_BILL LB
  WHERE  LB.LEGAL_BILL_SEQ_NUM = :id      -- (1) billId; (2) parentBillId; (3) bill.Id; (4) parentBillId
) WHERE ROWNUM <= 1;
```

Translation notes:
- `FirstOrDefault` → Oracle10 dialect wraps the query in `SELECT * FROM (...) WHERE ROWNUM <= 1`.
- In (3), `?? new BillData.LegalBill()` is a client-side null-coalesce. In (2) and (4), the query runs only when `parentBillId > 0` (C# guard).
- The in-memory LINQ-to-Objects comprehensions at L115-117 and L399-401 iterate `parentBill.BillServices` / `legalBill.BillServices`. Doing so triggers separate lazy collection loads of `LEGAL_BILL_SERVICE` by `LEGAL_BILL_SEQ_NUM`; those loads are not part of these statements. The `Delete(legalBill)` at L105-106 is a separate ORM DELETE.

## Operations and processes
- BillSaveService.DeleteBill — see [Operations and processes](../operations-processes.md)
- BillSaveService.ResetParentBill — see [Operations and processes](../operations-processes.md)
- BillSaveService.SaveLegalBill — see [Operations and processes](../operations-processes.md)

## Sources
- `Services/SAIF.Services/Legal/Bills/BillSaveService.cs (L100, L110, L315, L395)`