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:

await primeDbContext.Database.ExecuteSqlInterpolatedAsync(
    $"UPDATE Profiles SET Country = 'Poland' WHERE LEFT(TelNo, 2) = '48' AND Id > {minimalProfileId}");

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:

await primeDbContext.Database.ExecuteSqlInterpolatedAsync(
    $"UpdateProfilesCountry {minimalProfileId}");

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.

[Keyless]
public class GuestArrival
{
    public string Forename { get; set; }

    public string Surname { get; set; }

    public string TelNo { get; set; }

    public DateTime From { get; set; }

    public DateTime To { get; set; }

    public int RoomNumber { get; set; }
}

We also need to add a DbSet to our PrimeDbContext.

public class PrimeDbContext : DbContext
{
    public PrimeDbContext(DbContextOptions<PrimeDbContext> options)
        : base(options)
    {
    }

    // from stored procedures
    public virtual DbSet<GuestArrival> GuestArrivals { get; set; }
}

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

var guests = primeDbContext.GuestArrivals.FromSqlInterpolated($"GetGuestsForDate '{date}'").ToList();

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!