---
title: "SQL-237: Retrieve OnlineMessages within the active date window"
summary: Read ONLINE_MESSAGE rows whose termination/start dates put them in the active window; permission, priority, and application filters run in memory afterward.
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-09-24
library: SAIF.Services
reference_type: package
---

# 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](../database-tables.md#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`

```csharp
_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.

```sql
-- 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](../operations-processes.md)

## Sources
- `Services/SAIF.Services/OnlineMessages/OnlineMessagesApiService.cs (L50)`