Querying data using raw SQL & Stored Procedures in Entity Framework Core

This post assumes you're already familiar with EF Core and LINQ queries.

There's many ways to deal with SQL databases in .NET. There is SqlClient for interacting with databases using raw SQL statements. There's also have Entity Framework (EF) and other Object-Relational Mapping (ORM) libraries such as Dapper, NHibernate, etc.

Entity Framework is a powerful ORM and makes it easier to deal with SQL databases in various ways such as:

• Generating SQL queries from LINQ queries
• Mapping the SQL results into .NET objects
• Generating SQL Database Schema and migrations from .NET classes annotated either by attributes or the fluent API
• Generating classes based on existing SQL Database Schema

A common counterpoint to using ORM's is that the generated queries don't perform as well compared to when using manually written SQL queries. But what if I told you, you can have both? More on that later.

Measuring and optimizing LINQ queries using LINQPad

Your application ran fine at initial launch, but now the data has exponentially grown and as a result your LINQ query performance became slow. To diagnose the performance of the query, you need to figure out which SQL statement is generated. There are built-in ways to output the SQL statements in EF Core, but by far the easiest way is to use a free tool called LINQPad.

Image credits: LINQPad

Using LINQPad, you can point to your DLL so it will understand your DbContext and from there on you can write LINQ queries in the tool. The tool will show you the resulting data, the SQL statements, and other details. Using this data writing new queries and optimizing existing queries becomes a breeze.

But what if you simply can't achieve the desired query using LINQ? Let's learn about using Raw SQL queries.

Consider purchasing a paid license to support the development of this free tool.

Using Raw SQL statements in Entity Framework Core

You tried optimizing the LINQ query, but the required performance isn't achievable using LINQ Queries. Luckily, EF provides a Raw SQL API as an alternative to the LINQ API. But why would you still use Entity Framework when using Raw SQL Statements?

• You want to use the SQL Schema migrations, without using LINQ Queries
• or you're using LINQ Queries with EF 95% of the time, but only need Raw SQL queries 5% of the time
• or you still want the mapping of SQL data to .NET classes to be handled by EF
• or you prefer the intuitive & easy to use API

One of the great things about EF is that you don't have to go ALL IN. You decide which features are useful to your project. Here's an ASP.NET Core sample to query data using Raw SQL but still have EF map the data to .NET objects:

private static void QueryPetsByType(string type)
{
    using var petContext = new PetContext();
    List<Pet> pets = petContext.Pets
        .FromSqlRaw("SELECT [PetId], [Name], [Type] FROM Pets WHERE [Type] = {0}", type)
        .ToList();

    foreach (var pet in pets)
    {
        Console.WriteLine($"Name: {pet.Name}");
    }
}

Full sample at GitHub

The sample above queries the Pets table and filters by the specified type. Once the data is returned from the database, EF still maps the data to concrete .NET objects. In this case the data is being mapped to Pet objects. Keep in mind that Change Tracking still functions even when using FromSqlRaw. The above query is a very simple example, but you can plug in whatever SQL Query you need to fulfil your requirements.

You were able to create a Raw SQL query which resolved your performance issues. Huray! But a DBA has joined your team and wants to move the query to Stored Procedures.

Using Stored Procedures in Entity Framework Core

A DataBase Administrator (DBA) has joined your team and they want to be able to tweak the SQL Query without relying on developers. Stored Procedures are a way to store TSQL statements in the database and use them like reusable functions. By storing TSQL logic in Stored Procedures and calling the Stored Procedures from EF Core, the DBA can update the queries on his own. Here's a sample on how to call Stored Procedures from EF Core:

private static void QueryPetsByType(string type)
{
    using var petContext = new PetContext();
    List<Pet> pets = petContext.Pets
        .FromSqlRaw("EXEC GetPetsByType @Type={0};", type)
        .ToList();

    foreach (var pet in pets)
    {
        Console.WriteLine($"Name: {pet.Name}");
    }
}

Full sample on GitHub

In the sample, the pets are being queried again using FromSqlRaw. This time the Raw SQL statement simply calls the Stored Procedure and the type parameter is passed to the Stored Procedure.

In this case Change Tracking also still functions as usual.

Summary

When LINQ queries can't meet the performance requirements of your application, you can leverage Raw SQL and still have EF map the data to concrete .NET objects. You can also invoke Stored Procedures in case the logic of the SQL queries needs to reside in the SQL Database.