Raw SQL Queries With Entity Framework Core 5

Entity Framework Core 5 (EF Core) and LINQ allow developers to write powerful expressions that translate into SQL. Ultimately, ORMs are about balancing convenience and power. Generally, EF Core’s default features are enough to accomplish most data access-related tasks, but notice we used the word “generally”. In this post, we’ll explore how we can work with and around the features of EF Core 5 to interface with our database schema directly.

Why Do I Want To Write Raw SQL?

Many EF Core users may not want to write SQL, and it is likely why they chose an ORM in the first place. Context switching between C# and SQL can require mental energy that developers may want to reserve for other development tasks. EF Core’s LINQ abstraction comes with many advantages and some disadvantages. As our applications start to stabilize and grow in usage, we may find that the flexibility afforded by LINQ is no longer necessary, and the overhead of object tracking and SQL generation from EF Core is less than optimal. After exhausting all optimizations at the LINQ and EF Core layers, development teams will consider writing raw SQL.

Another critical reason to write raw SQL is to utilize functionality not yet supported in the EF Core abstraction. As of writing this post, EF Core does not support JSON for databases like SQL Server and PostgreSQL. The EF Core team prioritizes features based on maximum impact on the community. Generally, niche database features are left to the community to implement or not implemented at all.

Writing raw SQL affords teams an option to increase performance when they’ve exhausted all EF Core 5 optimizations or workaround EF Core 5 does not implement the functionality.

Using Entities

EF Core 5 makes it straightforward to use existing entities to write raw SQL using the FromSqlRaw and FromSqlRawInterpolated methods.

var results = db.Movies
    .FromSqlRaw("select * from movies order by id desc")
    .Take(100)
    .ToList();

The requirements to use this approach to writing raw SQL include: The entity must be accessible via a DbSet property. No arbitrary entity shapes. Column names must match the entity properties. The query cannot return additional data not part of the original entity. The advantage of this approach includes the following behaviors: Ability to track entities returned from a raw SQL query. The composition of the query using LINQ syntax. Using Include to load related data. The way that EF Core treats our SQL as a sub-query. Let’s look at the example above and what EF Core generates in our logging output.

Executed DbCommand (24ms) [Parameters=[@__p_1='100' (DbType = String)], CommandType='Text', CommandTimeout='30']
SELECT "m"."Id", "m"."Name", "m"."StreamingService"
FROM (
  select * from movies order by id desc
) AS "m"
LIMIT @__p_1

By turning our SQL into a subquery, EF Core 5 can take advantage of SQL views, stored procedures, and table value functions while giving us the ability to use EF Core features we would typically use on DbSet properties. From the top Select statement, we can also see that our subquery needs to return a similar shape to our entity.

While a fascinating approach, this is likely not the best use case for raw SQL.

Non-Schema Entities

There are features in EF Core that allow us to map entities to our DbContext that don’t map to a table but instead map directly to a SQL query. For example, we have a SQL statement that still utilizes our Movie model but with an additional Number property.

public class MovieWithNumber
{
    public int Id { get; set; }
    public int Number { get; set; }
}

Inside the OnModelCreating method found in our DbContext definition can now appropriately map the entity.

// DbSet
public DbSet<MovieWithNumber> MovieWithNumber { get; set; }

// within OnModelCreating
modelBuilder
    .Entity<MovieWithNumber>()
    .ToSqlQuery("select Id, 1 as Number from Movies order by id desc")
    .HasKey(m => m.Id);

Now it’s a matter of using it like we would any other DbSet property.

var results = db.MovieWithNumber
    .Take(100)
    .ToList();

EF Core will generate the following SQL.

Executed DbCommand (25ms) [Parameters=[@__p_0='100' (DbType = String)], CommandType='Text', CommandTimeout='30']
SELECT "m"."Id", "m"."Number"
FROM (
  select Id, 1 as Number from Movies order by id desc
) AS "m"
LIMIT @__p_0

It’s important to remember this approach can be dangerous as this is a SQL Query. Any attempts to add a MoviesWithNumber entity to our database will end in an exception because the table doesn’t exist.

Raw SQL With Dapper

Dapper is a Micro-ORM that leans heavily on the user’s ability to write SQL queries. What Dapper does well is taking the results of a query and mapping them to entities based on a set of name conventions.

Let’s start by installing Dapper to our project.

dotnet add package Dapper

We could use Dapper outside of our DbContext, but that introduces a few concerns we may want to avoid.

SQL exposed outside of our DbContext. Lack of reusability across our application.

The best way I’ve found is to expose our SQL query results using a method on our DbContext.

public async Task<IEnumerable<int>> GetMovieIds()
{
    await using var connection = Database.GetDbConnection();
    await Database.OpenConnectionAsync();

    var results =
        await connection
            .QueryAsync<int>("select id from movies order by id desc");

    return results;
}

The advantage to DbContext method approach is we can utilize the Database property to retrieve and open the same connection that our EF Core context is using, thus reducing the need for DbConnection creation mechanisms.

Now we can call the method from our code base directly from our instance.

var db = new Database();
var results = await db.GetMovieIds();

For folks not wanting to take “yet another dependency”, there is also the option to use ADO.NET directly.

await using var connection = Database.GetDbConnection().CreateCommand();

Personally, the mapping of SQL results to an entity is enough of a pain to take a dependency on Dapper.

Conclusion

EF Core is a powerful abstraction, but like many abstractions, it’s not perfect. EF Core 5, while configurable, doesn’t support all the features of our underlying storage engine. SQL is a powerful tool that we shouldn’t disregard because we’d prefer writing C#. We’ve walked through a few utilizing raw SQL and found that EF Core 5 has accounted for this need through configuration and extension methods. We can mix other data access mechanisms with EF Core to provide our users with the most performant experience. We saw how straightforward it was to integrate Dapper with our EF Core data access.

Developers can read more about raw SQL queries at the official EF Core documentation site.

Thanks for reading, and please let me know in the comments what you think.