Introduction
Learn how to return unmapped types using SqlQuery which may be useful when mapped types are not sufficient. There are several overloads for SqlQuery, here we will use the following along with SqlQueryRaw which is not recommended as unlike SqlQery will leave parameters exposed to hackers.
SqlQuery
Create the SQL statement, in this case the SQL is to get all United States holidays in a given year.
public static FormattableString GetHolidays(int year) => $"""
SELECT CalendarDate, CalendarDateDescription AS [Description], CalendarMonth, DATENAME(MONTH, DATEADD(MONTH, CalendarMonth, -1)) AS [Month], CalendarDay AS [Day], DayOfWeekName, IIF(BusinessDay = 0, 'No', 'Yes') AS BusinessDay, IIF(Weekday = 0, 'No', 'Yes') AS [Weekday] FROM DateTimeDatabase.dbo.Calendar WHERE CalendarYear = {year} AND Holiday = 1; """;
Create a model with properties to match each column in the SQL statement.
internal class Holiday
{ public DateOnly CalendarDate { get; set; } public string Description { get; set; } public int CalendarMonth { get; set; } public string Month { get; set; } public int Day { get; set; } public string DayOfWeekName { get; set; } public string BusinessDay { get; set; } public string Weekday { get; set; }
}
Note
The above was taken from the sample database provides with source code
Below is the model for the table with several column names to match column names in Holiday (but not required for using SqlQuery).
Execution
- Call SqlQuery with the expected type and the SQL statement which is shown above.
- TagWithDebugInfo is included in provided source code and is not required. What TagWithDebugInfo does is log debug information.
private static async Task RawExampleProtected(Context context, int year)
{ var currentYear = await context.Database .SqlQuery<Holiday>(SqlStatements.GetHolidays(year)) .TagWithDebugInfo("Holidays Protected") .ToListAsync(); AnsiConsole.MarkupLine(ObjectDumper.Dump(currentYear) .Replace("{Holiday}", "[yellow]{[/][lightskyblue3]Holiday[/][yellow]}[/]"));
}
TagWithDebugInfo sample from logging. Note the path, class name and line number which can be of use in a larger application with many queries and now a developer can find them by text e.g. Holidays Protected.
info: 9/1/2024 16:33:31.990 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) Executed DbCommand (46ms) [Parameters=[p0='2024'], CommandType='Text', CommandTimeout='30'] -- Executing method RawExampleProtected in C:\DotnetLand\VS2022\EntityFrameworkCore8\CalendarSqlQuerySample\Program.cs at line 50 message Holidays Protected SELECT CalendarDate, CalendarDateDescription AS [Description], CalendarMonth, DATENAME(MONTH, DATEADD(MONTH, CalendarMonth, -1)) AS [Month], CalendarDay AS [Day], DayOfWeekName, IIF(BusinessDay = 0, 'No', 'Yes') AS BusinessDay, IIF(Weekday = 0, 'No', 'Yes') AS [Weekday] FROM DateTimeDatabase.dbo.Calendar WHERE CalendarYear = @p0 AND Holiday = 1;
SqlQueryRaw
SqlQueryRaw works the same way as SqlQuery but is unsafe/unprotected in regards to parameters and for this reason using SqlQuery instead.
private static async Task RawExampleUnprotected(Context context, int year)
{ var currentYear = await context.Database.SqlQueryRaw<Holiday>( $"""
SELECT CalendarDate, CalendarDateDescription AS [Description], CalendarMonth, DATENAME(MONTH, DATEADD(MONTH, CalendarMonth, -1)) AS [Month], CalendarDay AS [Day], DayOfWeekName, IIF(BusinessDay = 0, 'No', 'Yes') AS BusinessDay, IIF(Weekday = 0, 'No', 'Yes') AS [Weekday] FROM DateTimeDatabase.dbo.Calendar WHERE CalendarYear = {year} AND Holiday = 1; """)
.TagWithDebugInfo("Holidays Unprotected") .ToListAsync();
}
Source code
- DbContextToFileLogger class write/logs EF Core operations under the executable folder in LogFiles folder.
- See the project file for how LogFiles folder is created.
- NuGet package ObjectDumper.NET is used to output results
- NuGet package Spectre.Console is used to colorize output.
Database creation
- Create the database in SSMS (SQL-Server Management Studio) under localdb or if different adjust the connection string in appsettings.json
- Run the script under Scripts\populate.sql
Summary
Instructions have been provided to return unmapped types with EF Core, the example provided used is simple for the sake of easy to learn and will work for whatever you can marry SQL to models.