SQL-237: Retrieve OnlineMessages within the active date window

OnlineMessagesApiService.GetAllActiveMessages assigns the query to _onlineMessages, a field typed IEnumerable<OnlineMessages>. Because the static type is IEnumerable, only the date-window predicate below is translated to SQL by NHibernate; every subsequent filter applied where _onlineMessages is consumed (GetMessagesForUser, GetTutorialList) runs as LINQ-to-Objects, not SQL. The statement re-executes each time _onlineMessages is enumerated.

Classification

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

Parameters

Parameter	Direction	Type	Max length	Description
:now1, :now2, :now3	Input	DateTime	N/A	each DateTime.Now occurrence, evaluated client-side at translation time (three separate binds whose values may differ by ticks)

Tables

Table	Access	Columns used
ONLINE_MESSAGE	SELECT	all 14 mapped columns; filters TERMINATION_DATE, START_DATE

ONLINE_MESSAGE_PERMISSION, ONLINE_USER_TYPE_CODE, and ONLINE_MESSAGE_PRIORITY_CODE are not part of this SQL — see Translation notes.

LINQ statement

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

_onlineMessages =
        uow.GetRepository<Data.Model.Messages.OnlineMessages>()
            .Query()
            .Where(x => (x.TerminationDate ?? DateTime.Now) >= DateTime.Now && x.StartDate <= DateTime.Now);

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
-- Deferred: _onlineMessages is an IEnumerable<OnlineMessages> holding the IQueryable;
-- this SQL runs each time it is enumerated (GetMessagesForUser L248-259, GetTutorialList L266+)
SELECT M.MESSAGE_SEQ_NUM,
       M.UPDATE_NUM,
       M.APPLICATION_NAME,
       M.AUTHOR_TXT,
       M.MESSAGE_TXT,
       M.TITLE_TXT,
       M.SPANISH_MESSAGE_TXT,
       M.SPANISH_TITLE_TXT,
       M.AUTO_EXPAND_FLAG,
       M.START_DATE,
       M.TERMINATION_DATE,
       M.UPDATE_DATE,
       M.UPDATE_USER,
       M.MESSAGE_PRIORITY_CODE
FROM   ONLINE_MESSAGE M
WHERE  NVL(M.TERMINATION_DATE, :now1) >= :now2
  AND  M.START_DATE <= :now3

Translation notes: - DateTime.Now is evaluated client-side by NHibernate and sent as a bind parameter (:now); it is not translated to SYSDATE. - ?? on a nullable date → NVL(...) (NHibernate Oracle dialect maps coalesce to nvl). - Because the static type is IEnumerable, every subsequent Where is LINQ-to-Objects, not SQL. The SQL filter above is only the date window; the following run in memory instead: x.UserPermissions.Any(...) and priorityLookup.Contains(x.PriorityCode.Code) (L248-250, L266-268), applicationName.Contains(...), and OrderByDescending(o => o.StartDate). UserPermissions (HasMany ... KeyColumn("MESSAGE_SEQ_NUM"), OnlineMessagesMap.cs:34) is lazy-loaded per message in separate statements; x.PriorityCode.Code is read from the FK MESSAGE_PRIORITY_CODE without a load, so ONLINE_MESSAGE_PRIORITY_CODE is not joined.

Operations and processes

• OnlineMessagesApiService.GetAllActiveMessages — see Operations and processes

Sources

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