Skip to content

SQL-070: Retrieve Activity

This ORM statement performs a read operation on Activity via GetActivities, GetActivityData, executed from Services/SAIF.Services/Collections/CollectionsService.cs (L92), Services/SAIF.Services/Collections/CollectionsService.cs (L1266-1267).

Classification

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

Parameters

Parameter Direction Type Max length Description
:caseId Input long N/A Method argument of GetActivities
:activityId Input long N/A Method argument of GetActivityData

Tables

Table Access Columns used
LEGAL_COLLECTION_ACTIVITY** SELECT LEGAL_COLL_ACTIVITY_SEQ_NUM, UPDATE_NUM, ACTIVITY_DATE, ACTIVITY_AMT, UPDATE_USER, UPDATE_DATE, EXP_DATE, LEGAL_COLLECTION_SEQ_NUM, LEGAL_COLL_ACTIVITY_CODE (+ unresolved CaseId predicate)

LINQ statement

Source: Services/SAIF.Services/Collections/CollectionsService.cs:92, Services/SAIF.Services/Collections/CollectionsService.cs:1266-1267.

// Services/SAIF.Services/Collections/CollectionsService.cs:92
var data = UnitOfWork.GetRepository<DataModel.Activity>().Query().Where(x => x.CaseId == caseId);

// Services/SAIF.Services/Collections/CollectionsService.cs:94
return Mapper.Map<Activity[]>(data);

// Services/SAIF.Services/Collections/CollectionsService.cs:1266
return UnitOfWork.GetRepository<DataModel.Activity>().Query()
      .SingleOrDefault(x => x.Id == activityId);

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
-- GetActivities (CollectionsService.cs:92)
SELECT
  LCA.LEGAL_COLL_ACTIVITY_SEQ_NUM,
  LCA.UPDATE_NUM,
  LCA.ACTIVITY_DATE,
  LCA.ACTIVITY_AMT,
  LCA.UPDATE_USER,
  LCA.UPDATE_DATE,
  LCA.EXP_DATE,
  LCA.LEGAL_COLLECTION_SEQ_NUM,
  LCA.LEGAL_COLL_ACTIVITY_CODE
FROM LEGAL_COLLECTION_ACTIVITY LCA
WHERE LCA.CaseId = :caseId -- unresolved mapping: Activity.CaseId

-- GetActivityData (CollectionsService.cs:1266)
SELECT
  LCA.LEGAL_COLL_ACTIVITY_SEQ_NUM,
  LCA.UPDATE_NUM,
  LCA.ACTIVITY_DATE,
  LCA.ACTIVITY_AMT,
  LCA.UPDATE_USER,
  LCA.UPDATE_DATE,
  LCA.EXP_DATE,
  LCA.LEGAL_COLLECTION_SEQ_NUM,
  LCA.LEGAL_COLL_ACTIVITY_CODE
FROM LEGAL_COLLECTION_ACTIVITY LCA
WHERE LCA.LEGAL_COLL_ACTIVITY_SEQ_NUM = :activityId
-- NHibernate expects at most one row

Translation notes: - Activity.CaseId is declared on the model (Data/SAIF.Data/Model/Collections/Activity.cs) but is not mapped by the compiled ActivityMap (which maps only References(x => x.Case).Column("LEGAL_COLLECTION_SEQ_NUM")). The predicate is shown with the C# name and -- unresolved mapping; LEGAL_COLLECTION_ACTIVITY is marked **. The NHibernate LINQ provider would be expected to throw QueryException ("could not resolve property: CaseId") for this predicate at runtime. The column that holds the case key is LEGAL_COLLECTION_SEQ_NUM, but the translation does not substitute it because the LINQ does not navigate x.Case.Id. - Mapper.Map<Activity[]>(data) (line 94) enumerates the IQueryable, executing the first statement; there is no in-memory operator before mapping. - Case and ActivityCode references are selected as FK columns only; LEGAL_COLLECTION and LEGAL_COLL_ACTIVITY_CODE are not joined. - The dead duplicate Mappings/Collections/CollectionsMap.cs (not compiled by SAIF.Data.NHibernate.csproj) also lacks a CaseId mapping for Activity.

Operations and processes

Note: Statements marked with ** could not be confirmed; treat as unknown until verified.

Sources

  • Services/SAIF.Services/Collections/CollectionsService.cs (L92, L1266)