Skip to content

SQL-236: Retrieve OnlineMessagePriority via GetMessagePriorities

OnlineMessagesApiService.GetMessagePriorities looks up a single OnlineMessagePriority whose trimmed, lower-cased description matches a key computed client-side from the priority argument (substituting "low" when the argument is "tutorial").

Classification

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

Parameters

Parameter Direction Type Max length Description
:priorityKey Input string N/A (priority.Trim().ToLower() == "tutorial" ? "low" : priority.Trim().ToLower()), evaluated entirely client-side into a single bind

Tables

Table Access Columns used
ONLINE_MESSAGE_PRIORITY_CODE SELECT CODE, CODE_DESCR, UPDATE_DATE, UPDATE_NUM, UPDATE_USER

LINQ statement

Source: Services/SAIF.Services/OnlineMessages/OnlineMessagesApiService.cs:86

var getPriority =
    _unitOfWork.GetRepository<OnlineMessagePriority>().Query().FirstOrDefault(x => x.Description.Trim().ToLower() == (priority.Trim().ToLower() == ConstTutorial ? ConstLow : priority.Trim().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 * FROM (
  SELECT P.CODE,
       P.CODE_DESCR,
       P.UPDATE_DATE,
       P.UPDATE_NUM,
       P.UPDATE_USER
  FROM   ONLINE_MESSAGE_PRIORITY_CODE P
  WHERE  LOWER(TRIM(P.CODE_DESCR)) = :priorityKey
) WHERE ROWNUM <= 1

Translation notes: - FirstOrDefault → Oracle10 dialect wraps the query in SELECT * FROM (...) WHERE ROWNUM <= 1. - x.Description.Trim().ToLower() → LOWER(TRIM(CODE_DESCR)). The result Code feeds a C# switch against OnlineMessagePriority constants (Urgent "1", High "2", Medium "3", Tutorial "4"). - :priorityKey = (priority.Trim().ToLower() == "tutorial" ? "low" : priority.Trim().ToLower()). The whole right-hand side references only locals and constants (ConstTutorial="tutorial", ConstLow="low", L28-29), so it is evaluated client-side into a single bind.

Operations and processes

Sources

  • Services/SAIF.Services/OnlineMessages/OnlineMessagesApiService.cs (L87)