Skip to content

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 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

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

-- 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

Sources

  • Services/SAIF.Services/Legal/Bills/BillSaveService.cs (L100, L110, L315, L395)