EF Core Raw SQL Queries
Tags: dotnet, efcore, sql, database
Published: 2023-09-18 08:40:35

---
title: EF Core Raw SQL Queries
published: true
date: 2023-04-15 00:00:00 UTC
tags: dotnet,efcore,sql,databases
canonical_url: https://www.milanjovanovic.tech/blog/ef-core-raw-sql-queries
cover_image: https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xg4q3dznr12c38d0vgja.png
---

**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:

```csharp
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:

```sql
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`:

```csharp
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:

```sql
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`:

```csharp
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:

```sql
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** :

```csharp
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.

* * *

**P.S. Whenever you're ready, there are 2 ways I can help you:**

1. [**Pragmatic Clean Architecture:**](https://www.milanjovanovic.tech/pragmatic-clean-architecture?utm_source=dev.to&utm_medium=website&utm_campaign=cross-posting) This comprehensive course will teach you the system I use to ship production-ready applications using Clean Architecture. Learn how to apply the best practices of modern software architecture. [**Join 950+ students here.**](https://www.milanjovanovic.tech/pragmatic-clean-architecture?utm_source=dev.to&utm_medium=website&utm_campaign=cross-posting)

2. [**Patreon Community:**](https://www.patreon.com/milanjovanovic) Think like a senior software engineer with **access to the source code** I use in my YouTube videos and **exclusive discounts** for my courses. [**Join 820+ engineers here.**](https://www.patreon.com/milanjovanovic)