Skip to content

SQL-339: Retrieve TempDataPersonAddress via DeleteTempRecords

This ORM statement performs a read operation on TempDataPersonAddress, selecting all rows matching the supplied RunId, via IUnitOfWork.GetRepository<T>(); the rows are then materialized and deleted one at a time (see SQL-394), executed from Services/SAIF.Services/Collections/SoilService.cs (L311). The SQL below is inferred from the LINQ expression and the TempDataPersonAddress NHibernate mapping (CLM_DBA.TEMP_DATA_PERSON_ADDRESS); it is not literal SQL text present in source.

Classification

Field Value
SQL ID SQL-339
Operation type Read
Execution method ORM (NHibernate LINQ)
Database Oracle
Schema CLM_DBA (explicit qualifier in Table())
Command type N/A (ORM-generated)
Business object TempDataPersonAddress
Criteria property N/A — ORM
Lifecycle method DeleteTempRecords

Parameters

Parameter Direction Type Max length Description
:runId Input string N/A Method argument of DeleteTempRecords, matched against TempDataPersonAddress.RunId

Tables

Table Access Columns used
TEMP_DATA_PERSON_ADDRESS SELECT RUN_ID, PERSON_ID, ADDRESS_CODE, DEATH_DATE, primary_first_name, primary_last_name, primary_middle_name, primary_name_extension_code, address_line1, address_line2, address_line3, address_line4, city_name, STATE_CODE, ZIP_CODE, direct_deposit_bank_acct_id, zip_code_ext, GENDER_CODE, update_user, UPDATE_DATE, payment_flag_code, EFF_DATE, EXP_DATE, Birth_Date, Primary_Tax_ID, CLM_NUM

LINQ statement

Source: Services/SAIF.Services/Collections/SoilService.cs:311

// Services/SAIF.Services/Collections/SoilService.cs:311
var addresses = UnitOfWork.GetRepository<TempDataPersonAddress>().Query().Where(x => x.RunId == runId);
addresses.ToList().ForEach(x => UnitOfWork.GetRepository<TempDataPersonAddress>().Delete(x));

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
  TDPA.RUN_ID,
  TDPA.PERSON_ID,
  TDPA.ADDRESS_CODE,
  TDPA.DEATH_DATE,
  TDPA.primary_first_name,
  TDPA.primary_last_name,
  TDPA.primary_middle_name,
  TDPA.primary_name_extension_code,
  TDPA.address_line1,
  TDPA.address_line2,
  TDPA.address_line3,
  TDPA.address_line4,
  TDPA.city_name,
  TDPA.STATE_CODE,
  TDPA.ZIP_CODE,
  TDPA.direct_deposit_bank_acct_id,
  TDPA.zip_code_ext,
  TDPA.GENDER_CODE,
  TDPA.update_user,
  TDPA.UPDATE_DATE,
  TDPA.payment_flag_code,
  TDPA.EFF_DATE,
  TDPA.EXP_DATE,
  TDPA.Birth_Date,
  TDPA.Primary_Tax_ID,
  TDPA.CLM_NUM
FROM CLM_DBA.TEMP_DATA_PERSON_ADDRESS TDPA
WHERE TDPA.RUN_ID = :runId

Translation notes:

  • Materialized by addresses.ToList() on line 312; the per-row Delete in the same statement is SQL-394.

Operations and processes

Sources

  • Services/SAIF.Services/Collections/SoilService.cs (L311)