Skip to content

SQL-173: Retrieve EdiFile via DeleteFile

DeleteFile looks up a single EdiFile row by ID before deleting it; the delete (and its mapped cascade to child EDI segments and data elements) is flushed at commit, separately from this read.

Classification

Field Value
SQL ID SQL-173
Operation type Read
Execution method ORM (NHibernate LINQ)
Database Oracle
Schema OPS$JIMMIL
Command type N/A (ORM-generated)
Business object EdiFile
Criteria property N/A — ORM
Lifecycle method DeleteFile

Parameters

Parameter Direction Type Max length Description
:fileId Input long N/A x.Id == fileId → column ID

Tables

Table Access Columns used
EDI_FILE SELECT, DELETE (at Commit) ID, DESCRIPTION, CREATE_DATE

LINQ statement

Source: Services/SAIF.Services/Utilities/Edi/EdiFileService.cs:74. Repository repo obtained at Services/SAIF.Services/Utilities/Edi/EdiFileService.cs:72.

var file = repo.Query().SingleOrDefault(x => x.Id == fileId);

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 EF.ID,
       EF.DESCRIPTION,
       EF.CREATE_DATE
FROM   OPS$JIMMIL.EDI_FILE EF
WHERE  EF.ID = :fileId;
-- NHibernate expects at most one row

-- Then, if found, repository Delete(file) at L77 flushes at Commit (L79):
-- DELETE FROM OPS$JIMMIL.EDI_FILE WHERE ID = :fileId;   (preceded by cascade deletes, see notes)

Translation notes: - SingleOrDefault/Single add no row limit; NHibernate throws if more than one row is returned. - EdiFileMap maps HasMany(x => x.Segments).KeyColumn("FILE_ID").Cascade.AllDeleteOrphan(), and EdiSegmentMap cascades to EDI_DATA_ELEMENT via SEGMENT_ID; deleting the file therefore loads and deletes child OPS$JIMMIL.EDI_SEGMENT / OPS$JIMMIL.EDI_DATA_ELEMENT rows at flush. Those cascade statements are separate from this read.

Operations and processes

Sources

  • Services/SAIF.Services/Utilities/Edi/EdiFileService.cs (L74)