Introduction
Learn how to write queries with Microsoft Entity Framework Core 8 (EF Core 8) using Microsoft SQL-Server which do not have corresponding models in the DbContext.
This is known as raw SQL where there are several methods available as described in the following documentation.
Also presented, a class, DbContextToFileLogger for logging EF Core queries to a log file and an extension method for IQueryable, TagWithDebugInfo which uses TagWith along with exposing the method name and line number of the executing query.
All code is written in a console project using dependency injection.
So, if not familiar with dependency injection with console projects, take time to learn this.
EF Core Power Tools Microsoft Visual Studio extension was used to reverse engineer an existing database which recognizes stored procedures and writes code to work with stored procedures.
See part 1 which is basically the same but in less detail. I short this article is better.
Writing raw queries
First off, the goal is to read holidays for a specific year from a Microsoft SQL Server database table which contain holidays from the year 2000 to the year 2099. The results are for display only.
Note
There is also an alternate that uses a NuGet package to get the same information found in this project (another GitHub repository) which is not EF Core.
The table model for database table
The model in this case does not meet the requirements for displaying holidays, see the next model which fills the requirements but can not be used with a conventional EF Core as the model has no matching table which is the focus of this article.
public partial class Calendar
{
public DateOnly CalendarDate { get; set; }
public int CalendarYear { get; set; }
[Column("CalendarMonth")]
public int Month { get; set; }
[Column("CalendarDay")]
public int Day { get; set; }
public string DayOfWeekName { get; set; }
public DateOnly FirstDateOfWeek { get; set; }
public DateOnly LastDateOfWeek { get; set; }
public DateOnly FirstDateOfMonth { get; set; }
public DateOnly LastDateOfMonth { get; set; }
public DateOnly FirstDateOfQuarter { get; set; }
public DateOnly LastDateOfQuarter { get; set; }
public DateOnly FirstDateOfYear { get; set; }
public DateOnly LastDateOfYear { get; set; }
public bool BusinessDay { get; set; }
public bool NonBusinessDay { get; set; }
public bool Weekend { get; set; }
public bool Holiday { get; set; }
public bool Weekday { get; set; }
[Column("CalendarDateDescription")]
public string Description { get; set; }
public string HolidayComputed { get; set; }
}
Model for raw query (no matching database table)
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; }
}
First attempt
Here SqlQueryRaw is used which returns a list of type Holiday by passing and int for year to the query in a FormattableString.
This works as expected but with one possible issue depending on the environment which is known as SQL-Injection.
If the code is for a single computer which the owner is the developer, this is most likely safe but suppose another person wants the code? Well, this leads to an important immutable rule every developer to adhere to, assume at some point in the future code as presented or any query no matter if EF Core, Dapper or ADO should be written to guard against SQL-Injection.
This leads to attempt 2.
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 dbo.Calendar
WHERE CalendarYear = {year}
AND Holiday = 1;
""")
.TagWithDebugInfo("Holidays Unprotected")
.ToListAsync();
}
Second attempt
The key difference between attempt 1 and this attempt is using SqlQuery here which EF Core will parameterize values sent to the database while attempt 1 used SqlQueryRaw which does not parameterize values sent to the database. As an aside, the SQL statement has been moved to another class to clean up the code.
private static async Task TheSolution(Context context, int year)
{
var currentYear = await context.Database
.SqlQuery<Holiday>(SqlStatements.GetHolidays(year))
.TagWithDebugInfo("Holidays Protected")
.ToListAsync();
}
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 dbo.Calendar
WHERE CalendarYear = {year}
AND Holiday = 1;
""";
Stored procedures
There are good reasons for stored procedures and there are developers who will always uses stored procedures.
The historical performance benefit of stored procs have generally been from the following (in no particular order):
- Pre-parsed SQL
- Pre-generated query execution plan
- Reduced network latency
- Potential cache benefits
Still an advantage? Most DBMS' (the latest editions) will cache the query plans for INDIVIDUAL SQL statements, greatly reducing the performance differential between stored procs and ad hoc SQL. There are some caveats and cases in which this isn't the case, so you'll need to test on your target DBMS.
Above was taken from the following.
In the end, each developer needs to evaluate project and database requirements and consult with a DBA to use or not use stored procedures.
In the code provided, the SQL statement used for the above were placed into a stored procedure as shown below.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_HolidaysByYear] (@SelectedYear AS INT) AS
BEGIN
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 dbo.Calendar
WHERE CalendarYear = @SelectedYear
AND Holiday = 1;
END
GO
Executing code.
private static async Task TheSolutionStoredProcedure(Context context, int year)
{
List<HolidaysByYearResult> currentYear = await context
.Procedures.uspHolidaysByYearAsync(year);
}
Summary
Information has been presented to allow a developer to make an informed decision on how to interact with an SQL Server table with EF Core where the queries do not match tables in the targeted database.
✔️ Emphasis on preventing SQL injection and an option to use stored procedures.
Although all code is presented in a console project, the code can be easily transferred to other project types. A console project was used for easy of learning, dependency injection is fairly well out of sight as well as logging.
Note in the case of logging the following is used to create the log folder in the project file.
<Target Name="MakeMyDir" AfterTargets="Build">
<MakeDir Directories="$(OutDir)LogFiles\$([System.DateTime]::Now.ToString(yyyy-MM-dd))" />
</Target>
Top comments (0)