SQL-054: GroupImageRepository.Delete¶

This statement is an update operation invoked through ORM (NHibernate LINQ): a load-then-modify sequence whose UPDATE is emitted as a dirty-check flush at commit, not by an explicit repository call. Executed by GroupImageRepository.Delete.

Classification¶

Field	Value
SQL ID	SQL-054
Operation type	Update
Execution method	ORM (NHibernate LINQ)
Database	Oracle
Schema	[unqualified]**
Command type	N/A (ORM-generated)
Business object	`GroupImageRepository`
Criteria property	N/A — ORM
Lifecycle method	`Delete`

Parameters¶

Parameter	Direction	Type	Max length	Description
`:ImageGroupId`	Input	long	N/A	`imageDelete.ImageGroupId` (bound as `groupImageId` inside `GetImageGroup`)
`:Id`	Input	long	N/A	`Image.Id` — the `image_seq_num` FK value read from `Image_Group`
`:DocumentDate`	Input	DateTime?*	N/A	`Image.DocumentDate` (current entity state)
`:SecurityLevel`	Input	int*	N/A	`Image.SecurityLevel` (current entity state)
`:ImageDeleteCode`	Input	string*	N/A	`Image.ImageDeleteCode` (current entity state)
`:ClaimNumber`	Input	string*	N/A	`Image.ClaimNumber` (current entity state)
`:PolicyNumber`	Input	long*	N/A	`Image.PolicyNumber` (current entity state)
`:DocumentTypeGroup`	Input	string*	N/A	`Image.DocumentTypeGroup` (current entity state)
`:Text`	Input	string*	N/A	`Image.Text` (current entity state)
`:ExternalViewCode`	Input	string*	N/A	`Image.ExternalViewCode` (current entity state)
`:IsDiscoverable`	Input	bool (YesNoType → 'Y'/'N')*	N/A	`Image.IsDiscoverable` (current entity state)
`:UpdateUser`	Input	string*	N/A	`Image.UpdateUser` (current entity state)
`:CreateDate`	Input	DateTime?*	N/A	`Image.CreateDate` (current entity state)
`:CreateUser`	Input	string*	N/A	`Image.CreateUser` (current entity state)
`:newVersion`	Input	DateTime?*	N/A	new `Image.UpdateDate` version value
`:oldVersion`	Input	DateTime?*	N/A	`Image.UpdateDate` as loaded (optimistic concurrency)

Tables¶

Table	Access	Columns used
Image_Group	SELECT	`image_group_seq_num`, `group_association_code`, `image_seq_num`
image	SELECT, UPDATE	SELECT: `image_seq_num`, `update_date`, `document_date`, `security_level_num`, `image_delete_code`, `claim_num`, `policy_num`, `image_file_code`, `comment_text`, `external_view_code`, `discovery_flag`, `update_user`, `create_date`, `create_user`, `image_category_code`, `towid`, `image_document_type_code`, `image_document_name_code`; UPDATE SET: `update_date` (version), `document_date`, `security_level_num`, `image_delete_code`, `claim_num`, `policy_num`, `image_file_code`, `comment_text`, `external_view_code`, `discovery_flag`, `update_user`, `create_date`, `create_user`; WHERE: `image_seq_num`, `update_date`*

LINQ statement¶

Source: Data/SAIF.Data.NHibernate/Repositories/Groups/GroupImageRepository.cs:29, using GetImageGroup (:41, same statement as SQL-023) and GroupImageQuery (:93). The write is committed by the caller (Services/SAIF.Services/Groups/Imaging/ImageService.cs:139).

[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1062:Validate arguments of public methods", MessageId = "0")]
public void Delete(ImageDelete imageDelete)
{
    var groupImage = GetImageGroup(imageDelete.ImageGroupId);
    if (groupImage.Image.ImageDeleteCode.IsNullOrEmpty() || groupImage.Image.ImageDeleteCode == " ")
    {
        groupImage.Image.ImageDeleteCode = "Y";
    }
    groupImage.Image.UpdateUser = imageDelete.UserId;
    groupImage.Image.UpdateDate = imageDelete.UpdateDate;
}

public ImageGroup GetImageGroup(long groupImageId)
{
    return GroupImageQuery().Single(i => i.Id == groupImageId);
}

private IQueryable<ImageGroup> GroupImageQuery()
{
    return Session.Query<ImageGroup>();
}

// Services/SAIF.Services/Groups/Imaging/ImageService.cs:139
public void DeleteImage(ImageDelete imageDelete)
{
    unitOfWork.Begin();
    imageDeleter.Delete(imageDelete);
    unitOfWork.Commit();
}

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
-- 1. GetImageGroup(imageDelete.ImageGroupId) (same statement as SQL-023)
SELECT
  IG.image_group_seq_num,
  IG.group_association_code,
  IG.image_seq_num
FROM Image_Group IG
WHERE IG.image_group_seq_num = :ImageGroupId
-- NHibernate expects at most one row

-- 2. Lazy initialization of groupImage.Image (References image_seq_num)
SELECT
  I.image_seq_num,
  I.update_date,
  I.document_date,
  I.security_level_num,
  I.image_delete_code,
  I.claim_num,
  I.policy_num,
  I.image_file_code,
  I.comment_text,
  I.external_view_code,
  I.discovery_flag,
  I.update_user,
  I.create_date,
  I.create_user,
  I.image_category_code,
  I.towid,
  I.image_document_type_code,
  I.image_document_name_code
FROM image I
WHERE I.image_seq_num = :Id

-- 3. Dirty-check flush at unitOfWork.Commit() (caller)
UPDATE image
SET
  update_date = :newVersion,
  document_date = :DocumentDate,
  security_level_num = :SecurityLevel,
  image_delete_code = :ImageDeleteCode,
  claim_num = :ClaimNumber,
  policy_num = :PolicyNumber,
  image_file_code = :DocumentTypeGroup,
  comment_text = :Text,
  external_view_code = :ExternalViewCode,
  discovery_flag = :IsDiscoverable,
  update_user = :UpdateUser,
  create_date = :CreateDate,
  create_user = :CreateUser
WHERE image_seq_num = :Id
  AND update_date = :oldVersion

Translation notes: - No repository Update call is made. The UPDATE (statement 3) comes from NHibernate's dirty check on the stateful session when the caller commits (Services/SAIF.Services/Groups/Imaging/ImageService.cs:141-143, via ImageDeleter.Delete at Services/SAIF.Services/Groups/Imaging/ImageDeleter.cs:35). The skill's execution-method vocabulary has no separate "dirty-check flush" value, so this is recorded as ORM (NHibernate LINQ) (the LINQ load that obtains the entity), with this note explaining the resulting write. - The soft delete sets image_delete_code = 'Y' only when the current value is null, empty, or " " (in-memory condition); UpdateUser and UpdateDate are always assigned. The UPDATE is shown with every updatable column because ImageMap does not declare DynamicUpdate(). - Image.UpdateDate is the mapped version property (Version(i => i.UpdateDate).Column("update_date")). NHibernate increments the version on flush, so the value assigned from imageDelete.UpdateDate may be replaced by NHibernate's generated timestamp. The exact behaviour depends on the NHibernate version. - image_file_code is mapped both as Map(DocumentTypeGroup) (updatable) and inside the read-only DocumentCategory / DocumentType references, so it is selected once and appears in the UPDATE only through DocumentTypeGroup. - ImageGroup.Group (FK group_association_code) is never navigated in Delete, so group_association is never joined. Image.DocumentCategory / DocumentType / DocumentName references are declared .ReadOnly() on ImageMap; their FK columns are selected but excluded from the UPDATE, and the referenced code tables (image_category_code, image_document_type_code, image_document_name_code, IMGSYS) are never joined. - The image mapping comes from the external SAIF.Framework.Data.NHibernate package (SAIF.Data.NHibernate.Mappings.Imaging.ImageMap), decompiled from the locally cached package version 2.0.22076.3; the project references version 2.0.22130.1, which is not in the local decompile cache. Because of that version gap, the image column names and types above are marked * (inferred from the cached decompile, not confirmed against the referenced version).

Operations and processes¶

Group image soft delete

Business rules¶

SDN-BR-040

Note: Statements marked with * were inferred from code structure, naming conventions, or contextual evidence where direct confirmation was not available in source artifacts. Statements marked with ** could not be confirmed; treat as unknown until verified.

Sources¶

Data/SAIF.Data.NHibernate/Repositories/Groups/GroupImageRepository.cs:29