# EF Core Raw SQL Queries

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

Published: 2023-04-15. Author: Milan Jovanović.

Canonical: https://milanjovanovic.tech/blog/ef-core-raw-sql-queries

EF Core supports raw SQL queries.
EF7 added support for queries returning scalar types, and EF8 added the `SqlQuery` and `SqlQueryRaw` methods for querying unmapped types that are not part of the EF model.
`SqlQuery` parameterizes interpolated values, which protects against SQL injection.

**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**](https://milanjovanovic.tech/blog/ef-core-vs-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](#ef-core-and-sql-queries)
- [Composing SQL queries with LINQ](#composing-sql-queries-with-linq)
- [Executing data modifications with SQL](#sql-queries-for-data-modifications)

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.

---

## Frequently asked questions

### Can you write raw SQL queries with EF Core?

Yes. EF7 added support for raw SQL queries returning scalar types, and EF8 added the SqlQuery and SqlQueryRaw methods for querying unmapped types that are not part of the EF model, which is what Dapper offers out of the box.

### Does EF Core SqlQuery protect against SQL injection?

Yes. SqlQuery uses string interpolation to parameterize the query, so interpolated values are sent to the database as parameters instead of being concatenated into the SQL text. ExecuteSql parameterizes arguments the same way.

### Can you combine raw SQL with LINQ in EF Core?

Yes. SqlQuery returns IQueryable, so you can compose it further with LINQ operators like Where, OrderBy, Skip, and Take. EF Core wraps your raw SQL in a subquery when generating the final SQL, which is not always optimal.

### Can EF Core query views and stored procedures with raw SQL?

Yes. Raw SQL queries in EF Core can return results from views, functions, and stored procedures. The result type can have a parameterized constructor, and its property names must match the names of the values in the result set.

### Is EF Core raw SQL as fast as Dapper?

Not quite. In EF8, SqlQuery performance was similar to LINQ queries using Select projections and still behind Dapper, but close enough that network costs play the bigger factor. It's most useful when you prefer writing SQL or need views and stored procedures.

### How do you run UPDATE or DELETE statements with EF Core?

Use the ExecuteSql method for SQL that modifies data without returning a result, such as an UPDATE or DELETE statement or a stored procedure call. EF7 also added the LINQ-based ExecuteUpdate and ExecuteDelete methods as an alternative.