EF Core Raw SQL Queries

EF Core is getting many new and exciting features in the upcoming version.

EF7 introduced support for returning scalar types using SQL queries.

And now we're getting support for querying unmapped types with raw SQL queries in EF8.

This is exactly what Dapper offers out of the box, and it's good to see EF Core catching up.

In this week's newsletter, I'm going to cover how to use EF Core for:

• Raw SQL queries
• Composing SQL queries with LINQ
• Executing data modifications with SQL

Let's dive in!

EF Core And SQL Queries

EF7 added support for raw SQL queries returning scalar types. EF8 is taking this a step further with raw SQL queries that can return any mappable type, without having to include it in the EF model.

You can query unmapped types with the SqlQuery and SqlQueryRaw methods.

The SqlQuery method uses string interpolation to parameterize the query, protecting against SQL injection attacks.

Here's an example query returning an OrderSummary list:

var startDate = new DateOnly(2023, 1, 1);

var ordersIn2023 = await dbContext
    .Database
    .SqlQuery<OrderSummary>(
        $"SELECT * FROM OrderSummaries AS o WHERE o.CreatedOn >= {startDate}")
    .ToListAsync();

This will be the SQL sent to the database:

SELECT * FROM OrderSummaries AS o WHERE o.CreatedOn >= @p0

The type used for the query result can have a parameterized constructor. The property names don't need to match the column names in the database, but they do have to match the names of the values in the result set.

You can also execute raw SQL queries and return results with:

• Views
• Functions
• Stored procedures

Composing SQL Queries With LINQ

An interesting thing about SqlQuery is that it returns IQueryable, which can be further composed with LINQ.

You can add a Where statement after calling SqlQuery:

var startDate = new DateOnly(2023, 1, 1);

var ordersIn2023 = await dbContext
    .Database
    .SqlQuery<OrderSummary>("SELECT * FROM OrderSummaries AS o")
    .Where(o => o.CreatedOn >= startDate)
    .ToListAsync();

However, the generated SQL isn't optimal:

SELECT s.Id, s.CustomerId, s.TotalPrice, s.CreatedOn
FROM (
    SELECT * FROM OrderSummaries AS o
) AS s
WHERE s.CreatedOn >= @p0

Another possibility is to combine an OrderBy statement with Skip and Take:

var startDate = new DateOnly(2023, 1, 1);

var ordersIn2023 = await dbContext
    .Database
    .SqlQuery<OrderSummary>(
        $"SELECT * FROM OrderSummaries AS o WHERE o.CreatedOn >= {startDate}")
    .OrderBy(o => o.Id)
    .Skip(10)
    .Take(5)
    .ToListAsync();

This would be the generated SQL for the previous query:

SELECT s.Id, s.CustomerId, s.TotalPrice, s.CreatedOn
FROM (
    SELECT * FROM OrderSummaries AS o WHERE o.CreatedOn >= @p0
) AS s
ORDER BY s.Id
OFFSET @p1 ROWS FETCH NEXT @p2 ROWS ONLY

In case you're wondering, the performance is similar to LINQ queries using Select projections.

I ran some benchmarks, and didn't notice any significant performance improvement.

This feature will be very useful if you're more comfortable with writing SQL or you want to fetch data from views, functions, and stored procedures.

SQL Queries For Data Modifications

If you want to modify data in the database with SQL, you will typically write a query that doesn't return a result.

The SQL query can be an UPDATE or DELETE statement, or even a stored procedure call.

You can use the ExecuteSql method to execute this type of query with EF Core:

var startDate = new DateOnly(2023, 1, 1);

dbContext.Database.ExecuteSql(
    $"UPDATE Orders SET Status = 5 WHERE CreatedOn >= {startDate}");

ExecuteSql also protects from SQL injection by parameterizing arguments, just like SqlQuery.

With EF7 you can write the above query with LINQ and the ExecuteUpdate method. There's also the ExecuteDelete method for deleting records.

In Summary

EF7 introduced support for raw SQL queries returning scalar values.

EF8 will add support for raw SQL queries returning unmapped types with SqlQuery and SqlQueryRaw.

I like the direction that EF is going, introducing more flexibility for querying the database.

The performance isn't as good as Dapper, unfortunately. But it's close enough that network costs will play the bigger factor.

I will probably be using only EF moving forward since it covers more use cases.

Thank you for reading, and have an awesome Saturday.