Skip to content

SQL-205: Retrieve ImageAgency via ImageAgencyQuery (property)

The ImageAgencyQuery property in ImageService exposes an IQueryable<ImageAgency> base query that two call sites materialize with different filters: GetImageAgency looks up a single ImageAgency row by its identifier, and GetImageAgencies lists every ImageAgency row for a given agency. Both are issued through NHibernate LINQ against the ImageAgency entity from Services/SAIF.Services/Agencies/Imaging/ImageService.cs:297, 271, 287.

Classification

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

Parameters

Parameter Direction Type Max length Description
:agencyImageId Input long N/A agencyImageId (L271 — GetImageAgency)
:agencyId Input long N/A agencyId (L287 — GetImageAgencies)

Tables

Table Access Columns used
IMAGE_AGENCY SELECT image_agency_seq_num, agency_seq_num, image_seq_num
image** SELECT (lazy load, separate statements) unresolved — lazy-loaded per row for Image.IsDeleted() / Image.SecurityLevel in-memory filters (L273-274, L289-290)

LINQ statement

Source: Services/SAIF.Services/Agencies/Imaging/ImageService.cs:297. The base IQueryable is obtained at L297-299 and materialized at L271 and L287.

// Services/SAIF.Services/Agencies/Imaging/ImageService.cs:297
return unitOfWork
           .GetRepository<agenciesDataModel.ImageAgency>()
           .Query();

// Services/SAIF.Services/Agencies/Imaging/ImageService.cs:271
var imageAgency = ImageAgencyQuery.SingleOrDefault(x => x.Id == agencyImageId);

// Services/SAIF.Services/Agencies/Imaging/ImageService.cs:287
var imageAgencies = ImageAgencyQuery.Where(ia => ia.Agency.Id == agencyId).ToList();

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
-- Base: ImageAgencyQuery (ImageService.cs:297-299) — IQueryable only.
-- (1) L271 — GetImageAgency
SELECT IA.image_agency_seq_num,
       IA.agency_seq_num,
       IA.image_seq_num
FROM   Image_Agency IA
WHERE  IA.image_agency_seq_num = :agencyImageId;
-- NHibernate expects at most one row

-- (2) L287 — GetImageAgencies
SELECT IA.image_agency_seq_num, IA.agency_seq_num, IA.image_seq_num
FROM   Image_Agency IA
WHERE  IA.agency_seq_num = :agencyId;

Translation notes: - SingleOrDefault/Single add no row limit; NHibernate throws if more than one row is returned. - Security/deleted filtering is LINQ-to-Objects after materialization. - ia.Agency.Id is the referenced identifier, so it short-circuits to the agency_seq_num FK column; the AGENCY table is not joined. - Image (Data.Model.Imaging.Image) has no in-repo ClassMap; Legends resolved the table (image) but not its columns, so the image row above is unresolved and the table is marked **.

Operations and processes

Sources

  • Services/SAIF.Services/Agencies/Imaging/ImageService.cs (L298)