Raw SQL in Entity Framework Core

Welcome everyone to a new post on the Entity Framework Core course. Initially, this post was going to be part of another one, but after asking the community, it seems it makes sense to have its own separate post.

In this post, we are going to see how to execute Raw SQL from Entity Framework Core.

1 - Why Raw SQL?

The first thing we need to understand is why we would want to write SQL queries within our code. The main reason is due to the limitations and issues that automatically generated SQL queries can cause us.

Most of these issues are usually related to performance, and while it is true that since .NET 7 Entity Framework Core has improved a lot when building queries, when queries become a bit more complex, there are still problems.

The limitation I mentioned occurs, for example, when your database has some specific feature that Entity Framework does not support; in that case, you can always run a direct raw SQL query.

2 - Using Raw SQL from Entity Framework Core

As always, this code is available on GitHub.

The easiest way to see how this works is through an example.

One thing to keep in mind is that most of the time when you use SQL from Entity Framework, it’s going to be for queries. Is it possible to make updates, inserts or deletes? YES, but it’s not the most common, since by default, EF works very well in those cases.

To simplify the example, let's use a basic query that we already have in our system, which is to read a record by ID.

[HttpGet("raw-sql/{userId}")]
public async Task<User?> RawSql(int userId)
{
    return await _context.Users
        .FromSqlInterpolated($"Select * from Users where id = {userId}")
        .FirstAsync();
}

In this case, we are using the context to access the DbSet, and each DbSet has the ability to execute one of the following methods: FromSql (obsolete since .NET Core 3), FromSqlRaw, and finally FromSqlInterpolated, which is where we write the SQL query.

The difference between FromSqlRaw and FromSqlInterpolated is that in the former you must build the query and then pass the parameters, while in the latter, you can use interpolated parameters directly.

You might think this mechanism is not very secure, but the truth is that using FromSqlInterpolated automatically parameterizes input values, which helps prevent SQL injection.

As an additional note, it’s worth mentioning that you can also access _context.Database.SqlQueryRaw<T>("select * from ...") from the context, but personally, I prefer to access from the DbSet in most cases.

By the way, needless to say, these methods can be used not only for queries but also to execute any type of SQL, such as update, delete or insert.