DEV Community

Cover image for EF Core queries for unmapped types part 1
Karen Payne
Karen Payne

Posted on • Updated on

EF Core queries for unmapped types part 1

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 SqlQuery will leave parameters exposed to hackers.

See part 2 which goes more indepth.

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;
     """;
Enter fullscreen mode Exit fullscreen mode

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; }
}
Enter fullscreen mode Exit fullscreen mode

Note
The above was taken from the sample database provides with source code

Calendar table and query

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]}[/]"));
}
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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();
}
Enter fullscreen mode Exit fullscreen mode

Source code

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

  1. Create the database in SSMS (SQL-Server Management Studio) under localdb or if different adjust the connection string in appsettings.json
  2. 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.

Top comments (0)