SQL-195: Retrieve ErrorLog¶

This statement covers the App_Error reads in ErrorLogRepository: GetErrorLogEntry (single row by id), GetErrorLogs (a composed query with up to nine conditionally-applied predicates, falling back to a single-row lookup when ErrorId > 0 or no criteria are set), and ApplicationNames (a distinct list of application names). Unlike the rest of this batch, ErrorLogRepository is registered against the SQL Server provider (database NETFRAMEWORK, user APPM, MsSqlConfiguration.MsSql2008), so this statement's dialect uses @ binds, + string concatenation, and no ROWNUM wrapping — the prior page incorrectly documented it as Oracle.

Classification¶

Field	Value
SQL ID	SQL-195
Operation type	Read
Execution method	ORM (NHibernate LINQ)
Database	SQL Server
Schema	[unqualified]**
Command type	N/A (ORM-generated)
Business object	`ErrorLogRepository`
Criteria property	N/A — ORM
Lifecycle method	`ApplicationNames (property), GetErrorLogEntry, GetErrorLogs`

Parameters¶

Parameter	Direction	Type	Max length	Description
`@id`	Input	Integer	N/A	`id` — filters `App_Error_Seq_Num`
`@environmentList`	Input	String[]	N/A	Captured list, expanded to `IN` — filters `Environment`
`@applicationName`	Input	String	N/A	`criteria.ApplicationName` — filters `App_Name`
`@fromdate` / `@today` / other date binds	Input	DateTime	N/A	Computed client-side from `DateTime.Now`/`DateTime.Today` (exactly one predicate applies, per `FilterTimeRange`) — filters `Error_Date`
`@fromDate`	Input	DateTime	N/A	`criteria.FromDate` — filters `Error_Date` (Range)
`@thruDatePlus1`	Input	DateTime	N/A	`testDate.AddDays(1)` — filters `Error_Date` (Range)
`@userName`	Input	String	N/A	`criteria.UserName` — filters `User_Name`
`@searchString`	Input	String	N/A	`criteria.SearchString` — filters `State_Info` / `Error_Info`

Tables¶

Table	Access	Columns used
APP_ERROR	SELECT	`App_Error_Seq_Num`, `App_Name`, `Error_Date`, `Environment`, `Machine_Name`, `Page_Name`, `Error_Source`, `State_Info`, `Error_Info`, `User_Name`, `Deleted_Flag`; filters `App_Error_Seq_Num`, `Environment`, `App_Name`, `Error_Date`, `User_Name`, `State_Info`, `Error_Info`

LINQ statement¶

Source: Services/SAIF.Services/OnlineSupport/Repositories/ErrorLogRepository.cs:53. Further composing fragments are at lines 55, 70, 79, 83, 90, 99, 100, 103, 107, 111, 115, 120, 124, 132, 138, 144, and 155.

// Services/SAIF.Services/OnlineSupport/Repositories/ErrorLogRepository.cs:53
var query = _unitOfWork.GetRepository<ErrorLog>().Query()
    .Where(x => x.Id == id);

// Services/SAIF.Services/OnlineSupport/Repositories/ErrorLogRepository.cs:55
var result = query.OrderByDescending(x => x.Id).ToList();

// Services/SAIF.Services/OnlineSupport/Repositories/ErrorLogRepository.cs:70
var query = _unitOfWork.GetRepository<ErrorLog>().Query();

// Services/SAIF.Services/OnlineSupport/Repositories/ErrorLogRepository.cs:79
query = query.Where(x => !environmentList.Contains(x.Environment.ToLower()) && x.Environment != "");

// Services/SAIF.Services/OnlineSupport/Repositories/ErrorLogRepository.cs:83
query = query.Where(x => environmentList.Contains(x.Environment.ToLower()) && x.Environment != "");

// Services/SAIF.Services/OnlineSupport/Repositories/ErrorLogRepository.cs:90
query = query.Where(x => x.ApplicationName == criteria.ApplicationName);

// Services/SAIF.Services/OnlineSupport/Repositories/ErrorLogRepository.cs:99
var fromdate = DateTime.Now.AddHours(-2);

// Services/SAIF.Services/OnlineSupport/Repositories/ErrorLogRepository.cs:100
query = query.Where(x => x.ErrorDate >= fromdate);

// Services/SAIF.Services/OnlineSupport/Repositories/ErrorLogRepository.cs:103
query = query.Where(x => x.ErrorDate > DateTime.Today);

// Services/SAIF.Services/OnlineSupport/Repositories/ErrorLogRepository.cs:107
query = query.Where(x => x.ErrorDate > DateTime.Now.AddHours(-24));

// Services/SAIF.Services/OnlineSupport/Repositories/ErrorLogRepository.cs:111
query = query.Where(x => x.ErrorDate >= DateTime.Today.AddDays(-7));

// Services/SAIF.Services/OnlineSupport/Repositories/ErrorLogRepository.cs:115
query = query.Where(x => x.ErrorDate >= DateTime.Today.AddMonths(-1));

// Services/SAIF.Services/OnlineSupport/Repositories/ErrorLogRepository.cs:120
query = query.Where(x => x.ErrorDate >= criteria.FromDate);

// Services/SAIF.Services/OnlineSupport/Repositories/ErrorLogRepository.cs:124
query = query.Where(x => x.ErrorDate <= testDate.AddDays(1));  // add one day to accomodate time

// Services/SAIF.Services/OnlineSupport/Repositories/ErrorLogRepository.cs:132
query = query.Where(x => x.UserName == criteria.UserName);

// Services/SAIF.Services/OnlineSupport/Repositories/ErrorLogRepository.cs:138
query = query.Where(x => x.StateInformation.Contains(criteria.SearchString) ||
                         x.ErrorInformation.Contains(criteria.SearchString));

// Services/SAIF.Services/OnlineSupport/Repositories/ErrorLogRepository.cs:144
var result = query.OrderByDescending(x => x.Id).ToList();

// Services/SAIF.Services/OnlineSupport/Repositories/ErrorLogRepository.cs:155
var data = _unitOfWork.GetRepository<ErrorLog>().Query()
    .Select(x => x.ApplicationName).OrderBy(x => x).Distinct().ToList();

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
-- Dialect: SQL Server (MsSql2008) — binds shown as @name
-- (1) L53-55 — GetErrorLogEntry
SELECT AE.App_Error_Seq_Num,
       AE.App_Name,
       AE.Error_Date,
       AE.Environment,
       AE.Machine_Name,
       AE.Page_Name,
       AE.Error_Source,
       AE.State_Info,
       AE.Error_Info,
       AE.User_Name,
       AE.Deleted_Flag
FROM   App_Error AE
WHERE  AE.App_Error_Seq_Num = @id
ORDER BY AE.App_Error_Seq_Num DESC

-- (2) L70-144 — GetErrorLogs (every predicate is conditional on FilterCriteria; shown all-on)
SELECT AE.App_Error_Seq_Num, AE.App_Name, AE.Error_Date, AE.Environment, AE.Machine_Name,
       AE.Page_Name, AE.Error_Source, AE.State_Info, AE.Error_Info, AE.User_Name, AE.Deleted_Flag
FROM   App_Error AE
WHERE  1 = 1
  -- L79 (Environment == "O"):  AND NOT (LOWER(AE.Environment) IN (@environmentList)) AND AE.Environment <> ''
  -- L83 (any other environment group):
  AND  LOWER(AE.Environment) IN (@environmentList) AND AE.Environment <> ''
  AND  AE.App_Name = @applicationName                       -- L90
  -- L94-127: exactly one ErrorDate predicate per FilterTimeRange:
  AND  AE.Error_Date >= @fromdate                                   -- TwoHour (DateTime.Now.AddHours(-2))
  --   AE.Error_Date >  @today                                                  -- Today
  --   AE.Error_Date >  @nowMinus24h                                            -- Last24Hours
  --   AE.Error_Date >= @todayMinus7d                                           -- Week
  --   AE.Error_Date >= @todayMinus1Month                                       -- Month
  --   AE.Error_Date >= @fromDate [AND AE.Error_Date <= @thruDatePlus1]         -- Range
  AND  AE.User_Name = @userName                                     -- L132
  AND  (AE.State_Info LIKE '%' + @searchString + '%'
        OR AE.Error_Info LIKE '%' + @searchString + '%')     -- L138-139
ORDER BY AE.App_Error_Seq_Num DESC                                              -- L144 (only when anyCriteria)

-- (3) L155 — ApplicationNames
SELECT DISTINCT AE.App_Name
FROM   App_Error AE
ORDER BY AE.App_Name

Translation notes: - Dialect differs from the rest of the batch: this repository's unit of work is SQL Server, so there is no ROWNUM wrapping and + is used for string concatenation. ErrorLogMap uses GeneratedBy.Identity(), consistent with SQL Server. - When criteria.ErrorId > 0 (L66-69) or no criteria are set (L147), GetErrorLogs falls back to statement (1). - Statement (3) — Select → OrderBy → Distinct — NHibernate emits SELECT DISTINCT ... ORDER BY; the blank-name filter at L158 runs in memory. - environmentList comes from environmentManager.GetEnvironmentListForGroupCode (Oracle ERROR_GROUP_ENVIRONMENT data via OnlineSupportRepository) — a separate statement, see SQL-194.

Operations and processes¶

ErrorLogRepository.ApplicationNames (property) — see Operations and processes
ErrorLogRepository.GetErrorLogEntry — see Operations and processes
ErrorLogRepository.GetErrorLogs — see Operations and processes

Sources¶

Services/SAIF.Services/OnlineSupport/Repositories/ErrorLogRepository.cs (L53, L70, L155)