Executing raw SQL with Entity Framework Core 5

Entity Framework Core 5 is an open-source, lightweight, extensible, and a cross-platform ORM. It is easy to apply and it makes database access super simple. However, sometimes working with tables and views is just not enough. How to execute raw SQL script with Entity Framework Core 5? Let’s find out.

Running the raw SQL

Running a SQL without carrying about the result is quite easy. Have a look at this example:

This SQL updates the Country based on TelNo column for profiles with Id higher then the one provided. It is just a few lines of code and it works perfectly! It also shows how we can pass a parameter to SQL, but you also can format it with curly braces.

Executing a stored procedure

A stored procedure is a perfect example of a SQL, that you might want to run directly on the database, but keep SQL on the database side. Let’s say you already have a stored procedure named UpdateProfilesCountry with one parameter. If you would just like to execute it, you could simply have a code like this:

You don’t need a DbSet to map the results, so you can use DbContext.Database.ExecuteSqlRawAsync and pass parameters if you’d like to.

If you’d like to get the full picture, read the separate article: Execute a stored procedure with Entity Framework Core 5.

Running a stored procedure with a result

While running a stored procedure or a plain SQL command can be done directly on the database level, returning the result is slightly more complicated. First of all, you need to add a keyless model to map your results. Let’s say we have a stored procedure GetGuestsForDate and we would like to return a model named GuestArrival.

We also need to add a DbSet to our PrimeDbContext.

Now we can go ahead and use it. Here is how we can accomplish that:

And if I place it in my API project, it will map results to entities:

Notice that to map results to objects, we need to use DbSet collection.

If you’d like to get the full picture, read the separate article: Select data with a stored procedure with Entity Framework Core 5

Summary

It is not only possible but also surprisingly easy to run any SQL on the database with Entity Framework Core 5. You can execute SQL on a database level, but when you care about the result, you need to add a DbSet representing your results.

All code mentioned here can be found on my GitHub, feel free to experiment with it.

Cheers!

 

3 thoughts on “Executing raw SQL with Entity Framework Core 5

  1. Passing an interpolated string to FromSqlRaw is a really bad idea… it will format the value directly into the SQL command, which makes the app vulnerable to SQL injection. If you use FromSqlInterpolated instead, the value will be passed as a parameter.

Leave a Reply

Your email address will not be published. Required fields are marked *