EF Core queries for unmapped types

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:DotnetLandVS2022EntityFrameworkCore8CalendarSqlQuerySampleProgram.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 Scriptspopulate.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.

Source link
lol