SQL-219: Retrieve LegalBill¶

BillService queries LegalBill from four call sites: an inbox read filtered by bill-status filter grouping, two pending-approval reads filtered by status code (one additionally by approving employee), and a single-bill lookup by primary key. All four reach the database through NHibernate LINQ over IUnitOfWork.GetRepository<BillData.LegalBill>().Query().

Classification¶

Field	Value
SQL ID	SQL-219
Operation type	Read
Execution method	ORM (NHibernate LINQ)
Database	Oracle
Schema	[unqualified]**
Command type	N/A (ORM-generated)
Business object	`LegalBill`
Criteria property	N/A — ORM
Lifecycle method	`GetInboxItems, GetLegalBill, GetPendingBills`

Parameters¶

Parameter	Direction	Type	Max length	Description
`:employeeId`	Input	long	N/A	`employee.Id`, client-side — statement (2) filters by `APPROVED_BY_EMPLID`
`:billId`	Input	long	N/A	statement (4) filters `LEGAL_BILL_SEQ_NUM` by primary key

Literals 'PAID' (statement 1) and 'PEND' (statements 2–3) are BillStatus.PaidGrouping / BillData.BillStatus.PendingApprovalCode, sent as bind values by NHibernate but shown inline for readability.

Tables¶

Table	Access	Columns used
LEGAL_BILL	SELECT	LEGAL_BILL_SEQ_NUM, UPDATE_NUM, ISSUE_DATE, RECEIVED_DATE, APPROVED_DATE, REQUESTED_BY_EMPLID, APPROVED_BY_EMPLID, INVOICE_TEXT, COMMENT_TEXT, PREPAYMENT_FLAG, CREATE_USER, CREATE_DATE, UPDATE_USER, UPDATE_DATE, WITNESS_INFORMATION, PARENT_LEGAL_BILL_SEQ_NUM, LEGAL_CASE_SEQ_NUM, BILL_STATUS_CODE, BILLING_PROV_LOC_SEQ_NUM, RENDERING_PROV_LOC_SEQ_NUM, SPECIAL_HANDLING_CODE
BILL_STATUS_CODE	JOIN	CODE, FILTER_GROUPING_TEXT (statement 1 only)

BILL_STATUS_CODE is not joined in statements (2)–(4): x.BillStatus.Code there is the referenced entity's identifier, so it short-circuits to LEGAL_BILL.BILL_STATUS_CODE. x.LegalCase.ClaimId (BillService.cs:81) is read in memory after statement (1) and lazy-loads LEGAL_CASE separately; it is not joined here.

LINQ statement¶

Source: Services/SAIF.Services/Legal/Bills/BillService.cs:77, also :145, :150, :232.

// Services/SAIF.Services/Legal/Bills/BillService.cs:77
var inboxItemsDataBase = _unitOfWork.GetRepository<BillData.LegalBill>().Query().Where(x => x.BillStatus.FilterGroupText != BillStatus.PaidGrouping).ToList();

// Services/SAIF.Services/Legal/Bills/BillService.cs:145
legalBills = _unitOfWork.GetRepository<BillData.LegalBill>().Query().Where(x => x.BillStatus.Code == BillData.BillStatus.PendingApprovalCode && x.ApprovedByEmployeeId == employee.Id).ToList();

// Services/SAIF.Services/Legal/Bills/BillService.cs:150
   legalBills = _unitOfWork.GetRepository<BillData.LegalBill>().Query()
.Where(x => x.BillStatus.Code == BillData.BillStatus.PendingApprovalCode).ToList();

// Services/SAIF.Services/Legal/Bills/BillService.cs:232
BillData.LegalBill LegalBillFromDataBase = _unitOfWork.GetRepository<BillData.LegalBill>().Query().FirstOrDefault(x => x.Id == billId);

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) L77 — GetInboxItems
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
  INNER JOIN BILL_STATUS_CODE BS ON BS.CODE = LB.BILL_STATUS_CODE
WHERE  BS.FILTER_GROUPING_TEXT <> 'PAID'       -- BillStatus.PaidGrouping

-- (2) L145 — GetPendingBills (employee branch)
SELECT LB.<all 21 mapped LEGAL_BILL columns, as in (1)>
FROM   LEGAL_BILL LB
WHERE  LB.BILL_STATUS_CODE = 'PEND'                        -- PendingApprovalCode
  AND  LB.APPROVED_BY_EMPLID = :employeeId

-- (3) L150 — GetPendingBills (no employee)
SELECT LB.<all 21 mapped LEGAL_BILL columns, as in (1)>
FROM   LEGAL_BILL LB
WHERE  LB.BILL_STATUS_CODE = 'PEND'

-- (4) L232 — GetLegalBill
SELECT * FROM (
  SELECT LB.<all 21 mapped LEGAL_BILL columns, as in (1)>
  FROM   LEGAL_BILL LB
  WHERE  LB.LEGAL_BILL_SEQ_NUM = :billId
) WHERE ROWNUM <= 1

Translation notes: - FirstOrDefault (statement 4) → Oracle10 dialect wraps the query in SELECT * FROM (...) WHERE ROWNUM <= 1. - C# const values are shown as literals for readability; NHibernate LINQ sends them as bind parameters. - Statement (1) compares a non-identifier property (FilterGroupText) of the referenced BillStatus, so NHibernate emits an implicit inner join. The <> comparison is not null-safe: rows whose FILTER_GROUPING_TEXT is NULL are excluded.

Operations and processes¶

Business rules¶

Sources¶

Services/SAIF.Services/Legal/Bills/BillService.cs (L77, L145, L150, L232)