SQL-234: TaskQueryProvider.GetCategory¶

This statement is a read operation invoked through ORM (NHibernate LINQ). Executed by TaskQueryProvider.GetCategory.

Classification¶

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

Parameters¶

Parameter	Direction	Type	Max length	Description
`code`	Input	string	N/A	Method argument of `TaskQueryProvider.GetCategory` bound into the ORM expression; NHibernate generates the bind variable at runtime

Tables¶

Table	Access	Columns used
TASK_CATEGORY_CODE	SELECT	Code, CODE_DESCR, NOTEPAD_REQUIRED_FLAG, DEFAULT_ACTION_CODE, DEFAULT_PRIORITY_CODE, TASK_SOURCE_CODE

LINQ statement¶

Source: Data/SAIF.Data/Queries/Tasks/TaskQueryProvider.cs:47.

// Data/SAIF.Data/Queries/Tasks/TaskQueryProvider.cs:47-48
return Factory.GetRepository<TaskCategory>().Query()
    .SingleOrDefault(x => x.Code.ToLower() == code.ToLower());

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
  TCC.Code,
  TCC.CODE_DESCR,
  TCC.NOTEPAD_REQUIRED_FLAG,
  TCC.DEFAULT_ACTION_CODE,
  TCC.DEFAULT_PRIORITY_CODE,
  TCC.TASK_SOURCE_CODE
FROM TASK_CATEGORY_CODE TCC
WHERE LOWER(TCC.Code) = :code  -- bound value = code.ToLower(), evaluated client-side
-- SingleOrDefault: NHibernate fetches and throws if more than one row

Translation notes: - The identifier column of TaskCategory is literally Code (FluentNHibernate default: Id(x => x.Code) without .Column(...)). - x.Code.ToLower() on the mapped member → LOWER(col) server-side; code.ToLower() does not depend on the lambda parameter, so NHibernate's partial evaluator (re-linq) computes it in memory and binds the result as a parameter. The function wrapper prevents index use on the code column. - References(DefaultAction/DefaultPriority/TaskSource) contribute their FK columns only; the referenced code tables are not joined.

Operations and processes¶

Other query providers

Business rules¶

SD-BR-091 Task codes compared case-insensitively

Sources¶

Data/SAIF.Data/Queries/Tasks/TaskQueryProvider.cs:47