Skip to content

SQL-139: Retrieve ClaimNoteCategory

This ORM statement performs a read operation on ClaimNoteCategory via IUnitOfWork.GetRepository<T>(), executed from Services/SAIF.Services/Claims/Notes/ClaimNotesService.cs (L191, L244, L285, L382).

Classification

Field Value
SQL ID SQL-139
Operation type Read
Execution method ORM (NHibernate LINQ)
Database Oracle
Schema [unqualified]**
Command type N/A (ORM-generated)
Business object ClaimNoteCategory
Criteria property N/A — ORM
Lifecycle method CreateNote, GetCategories, GetCategoryDesc, SaveNote

Parameters

Parameter Direction Type Max length Description
noteEdit.Category.Code Input ** N/A x.Code == noteEdit.Category.Code → column CODE
noteEdit.Type.Code Input ** N/A x.Code == noteEdit.Type.Code → column CODE
categoryCode Input ** N/A x.Code == categoryCode → column CODE

Tables

Table Access Columns used
CLAIM_ACTIVITY_CATEGORY_CODE SELECT CODE, CODE_DESCR, EXTERNAL_VIEW_DEFAULT_FLAG

LINQ statement

Source: Services/SAIF.Services/Claims/Notes/ClaimNotesService.cs:191

// Services/SAIF.Services/Claims/Notes/ClaimNotesService.cs:191
                noteData.ClaimNoteCategory = UnitOfWork.GetRepository<ClaimNoteCategory>().Query().SingleOrDefault(x => x.Code == noteEdit.Category.Code);

// Services/SAIF.Services/Claims/Notes/ClaimNotesService.cs:244-246
            var query = UnitOfWork.GetRepository<ClaimNoteCategory>().Query().OrderBy(x => x.Description);

            return Mapper.Map<NoteCategory[]>(query.ToList());

// Services/SAIF.Services/Claims/Notes/ClaimNotesService.cs:284-287
                    ClaimNoteCategory =
                        UnitOfWork.GetRepository<ClaimNoteCategory>()
                            .Query()
                            .SingleOrDefault(x => x.Code == noteEdit.Category.Code),

// Services/SAIF.Services/Claims/Notes/ClaimNotesService.cs:382-384
            var catcode = UnitOfWork.GetRepository<ClaimNoteCategory>()
                .Query()
                .SingleOrDefault(x => x.Code == categoryCode);

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
-- Services/SAIF.Services/Claims/Notes/ClaimNotesService.cs:191
SELECT
  CACC.CODE,
  CACC.CODE_DESCR,
  CACC.EXTERNAL_VIEW_DEFAULT_FLAG
FROM CLAIM_ACTIVITY_CATEGORY_CODE CACC
WHERE CACC.CODE = :Code
-- NHibernate expects at most one row

-- Services/SAIF.Services/Claims/Notes/ClaimNotesService.cs:244
SELECT
  CACC.CODE,
  CACC.CODE_DESCR,
  CACC.EXTERNAL_VIEW_DEFAULT_FLAG
FROM CLAIM_ACTIVITY_CATEGORY_CODE CACC
ORDER BY CACC.CODE_DESCR

-- Services/SAIF.Services/Claims/Notes/ClaimNotesService.cs:285
SELECT
  CACC.CODE,
  CACC.CODE_DESCR,
  CACC.EXTERNAL_VIEW_DEFAULT_FLAG
FROM CLAIM_ACTIVITY_CATEGORY_CODE CACC
WHERE CACC.CODE = :Code
-- NHibernate expects at most one row

-- Services/SAIF.Services/Claims/Notes/ClaimNotesService.cs:382
SELECT
  CACC.CODE,
  CACC.CODE_DESCR,
  CACC.EXTERNAL_VIEW_DEFAULT_FLAG
FROM CLAIM_ACTIVITY_CATEGORY_CODE CACC
WHERE CACC.CODE = :categoryCode
-- NHibernate expects at most one row

Translation notes: - noteEdit.Category.Code -> bind :Code. - HasManyToMany(ClaimNoteTypes) (ClaimNoteCategoryMap.cs) is lazy and not selected.

Operations and processes

Business rules

Sources

  • Services/SAIF.Services/Claims/Notes/ClaimNotesService.cs (L191, L244, L285, L382)