Skip to content

SQL-021: AuditQueryProvider.GetPhysicalMedicineCount

This statement is a read operation invoked through ORM (NHibernate LINQ). Executed by AuditQueryProvider.GetPhysicalMedicineCount.

Classification

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

Parameters

Parameter Direction Type Max length Description
claimId Input long N/A Method argument of AuditQueryProvider.GetPhysicalMedicineCount bound into the ORM expression; NHibernate generates the bind variable at runtime

Tables

Table Access Columns used
CBP_AUDITED_SERVICE SELECT BILL_ID, SERVICE_DATE, BILL_SERVICE_CODE
CBP_BILL JOIN BILL_ID, CLAIM_ID
CBP_SERVICE_PHYSICAL_MEDICINE SELECT (subquery) CBP_SERVICE_CODE

LINQ statement

Source: Data/SAIF.Data/Queries/AuditQueryProvider.cs:142.

var services = Factory.GetRepository<CbpAuditedService>().Query()
           .Where(x => x.Bill.ClaimId == claimId &&
           Factory.GetRepository<CbpServicePhysicalMedicine>().Query()
  .Select(y => y.CbpServiceCode).Distinct()
  .Contains(x.BillServiceCode))
  .Select(x => x.ServiceDate).Distinct();

return services.Count();

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
SELECT COUNT(*)
FROM (
  SELECT DISTINCT CAS.SERVICE_DATE
  FROM CBP_AUDITED_SERVICE CAS
    INNER JOIN CBP_BILL CB
      ON CB.BILL_ID = CAS.BILL_ID
  WHERE CB.CLAIM_ID = :claimId
    AND CAS.BILL_SERVICE_CODE IN (
      SELECT DISTINCT CSPM.CBP_SERVICE_CODE
      FROM CBP_SERVICE_PHYSICAL_MEDICINE CSPM
    )
)

Translation notes: - The nested repository query is translated as an uncorrelated IN subquery; the whole statement executes once at services.Count(). - NHibernate may instead emit SELECT COUNT(DISTINCT CAS.SERVICE_DATE) in place of the derived table; the result is identical.

Operations and processes

Business rules

Sources

  • Data/SAIF.Data/Queries/AuditQueryProvider.cs:142