---
title: "SQL-221: Check for duplicate LegalBillService"
summary: Look up an existing LegalBillService for the same bill/provider/case to detect a duplicate service line.
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-09-24
library: SAIF.Services
reference_type: package
---

# SQL-221: Check for duplicate LegalBillService

`BillRateService.CheckForDuplicateService` looks up `LegalBillService` rows against the same billing/rendering provider and case, first with an exact match on bill-service code, service-from date, and billed amount, then (if none found) with a looser match on provider/case/date only. Both queries join `LEGAL_BILL` to reach the provider and case filters.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-221 |
| **Operation type** | Read |
| **Execution method** | ORM (NHibernate LINQ) |
| **Database** | Oracle |
| **Schema** | [unqualified]** |
| **Command type** | N/A (ORM-generated) |
| **Business object** | `LegalBillService` |
| **Criteria property** | N/A — ORM |
| **Lifecycle method** | `CheckForDuplicateService` |

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:providerLocationId` | Input | long | N/A | method argument, matched against billing or rendering provider |
| `:caseId` | Input | long | N/A | method argument, matched against the bill's legal case |
| `:billId` | Input | long | N/A | method argument, excluded bill (`<>`) |
| `:billServiceCode` | Input | string | N/A | `serviceLine.BillService.Code` (statement 1 only) |
| `:serviceFromDate` | Input | DateTime | N/A | `serviceLine.ServiceFromDate` |
| `:billedAmount` | Input | decimal | N/A | `serviceLine.BilledAmount` (statement 1 only) |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [LEGAL_BILL_SERVICE](../database-tables.md#legal_bill_service) | SELECT | all 18 mapped columns; filters BILL_SERVICE_CODE, SERVICE_FROM_DATE, BILLED_AMOUNT |
| [LEGAL_BILL](../database-tables.md#legal_bill) | JOIN | LEGAL_BILL_SEQ_NUM, BILLING_PROV_LOC_SEQ_NUM, RENDERING_PROV_LOC_SEQ_NUM, LEGAL_CASE_SEQ_NUM |

`PROVIDER_LOCATION`, `LEGAL_CASE`, and `BILL_SERVICE_CODE` are not joined. `LegalBill.BillingProvider.Id`, `RenderingProvider.Id`, and `LegalCase.Id` are FK identifiers on `LEGAL_BILL` (the first-level navigation `LegalBill` is joined, but its second-level navigations short-circuit to its own FK columns), and `x.BillService.Code` is the FK identifier `LEGAL_BILL_SERVICE.BILL_SERVICE_CODE`. `LEGAL_BILL` itself IS joined: its FK columns are needed and are not available on `LEGAL_BILL_SERVICE`.

## LINQ statement

Source: `Services/SAIF.Services/Legal/Bills/BillRateService.cs:79`, also `:92`.

```csharp
// Services/SAIF.Services/Legal/Bills/BillRateService.cs:79
var legalBillService = _unitOfWork.GetRepository<BillData.LegalBillService>().Query()
      .FirstOrDefault(x => (x.LegalBill.BillingProvider.Id == providerLocationId || x.LegalBill.RenderingProvider.Id == providerLocationId)
                        && x.LegalBill.LegalCase.Id == caseId
                        && x.LegalBill.Id != billId
                        && x.BillService.Code == serviceLine.BillService.Code
                        && x.ServiceFromDate == serviceLine.ServiceFromDate
                        && x.BilledAmount == serviceLine.BilledAmount);

// Services/SAIF.Services/Legal/Bills/BillRateService.cs:92
legalBillService = _unitOfWork.GetRepository<BillData.LegalBillService>().Query()
    .FirstOrDefault(x => (x.LegalBill.BillingProvider.Id == providerLocationId || x.LegalBill.RenderingProvider.Id == providerLocationId)
                      && x.LegalBill.LegalCase.Id == caseId
                      && x.LegalBill.Id != billId
                      && x.ServiceFromDate == serviceLine.ServiceFromDate);
```

## 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) L79 — duplicate check including service code and billed amount
SELECT * FROM (
  SELECT S.LEGAL_BILL_SERVICE_SEQ_NUM,
       S.UPDATE_NUM,
       S.AMENDED_FLAG,
       S.SERVICE_FROM_DATE,
       S.SERVICE_THRU_DATE,
       S.BILLED_AMOUNT,
       S.PAID_AMOUNT,
       S.BILLED_UNITS,
       S.ADJUSTMENT_REASON_TEXT,
       S.PARENT_BILL_SERVICE_SEQ_NUM,
       S.CREATE_USER,
       S.CREATE_DATE,
       S.UPDATE_USER,
       S.UPDATE_DATE,
       S.LEGAL_BILL_SEQ_NUM,
       S.BILL_SERVICE_CODE,
       S.BILL_SERVICE_RATE_TYPE_CODE,
       S.ADJUSTMENT_REASON_CODE
  FROM   LEGAL_BILL_SERVICE S
    INNER JOIN LEGAL_BILL LB ON LB.LEGAL_BILL_SEQ_NUM = S.LEGAL_BILL_SEQ_NUM
  WHERE  (LB.BILLING_PROV_LOC_SEQ_NUM = :providerLocationId
          OR LB.RENDERING_PROV_LOC_SEQ_NUM = :providerLocationId)
    AND  LB.LEGAL_CASE_SEQ_NUM = :caseId
    AND  LB.LEGAL_BILL_SEQ_NUM <> :billId
    AND  S.BILL_SERVICE_CODE = :billServiceCode
    AND  S.SERVICE_FROM_DATE = :serviceFromDate
    AND  S.BILLED_AMOUNT = :billedAmount
) WHERE ROWNUM <= 1

-- (2) L92 — possible-duplicate check (provider/case/bill/from-date only)
SELECT * FROM (
  SELECT S.<all 18 mapped LEGAL_BILL_SERVICE columns, as in (1)>
  FROM   LEGAL_BILL_SERVICE S
    INNER JOIN LEGAL_BILL LB ON LB.LEGAL_BILL_SEQ_NUM = S.LEGAL_BILL_SEQ_NUM
  WHERE  (LB.BILLING_PROV_LOC_SEQ_NUM = :providerLocationId
          OR LB.RENDERING_PROV_LOC_SEQ_NUM = :providerLocationId)
    AND  LB.LEGAL_CASE_SEQ_NUM = :caseId
    AND  LB.LEGAL_BILL_SEQ_NUM <> :billId
    AND  S.SERVICE_FROM_DATE = :serviceFromDate
) WHERE ROWNUM <= 1
```

Translation notes:
- `FirstOrDefault` → Oracle10 dialect wraps each query in `SELECT * FROM (...) WHERE ROWNUM <= 1`.
- Only the first-level navigation `x.LegalBill` needs a join. The second-level `.BillingProvider.Id` / `.RenderingProvider.Id` / `.LegalCase.Id` are short-circuited to FK columns on the joined `LEGAL_BILL`, so `PROVIDER_LOCATION` and `LEGAL_CASE` are never joined.

## Operations and processes
- BillRateService.CheckForDuplicateService — see [Operations and processes](../operations-processes.md)

## Sources
- `Services/SAIF.Services/Legal/Bills/BillRateService.cs (L79, L92)`