Mixing Raw SQL and Fluent Statements with Entity Framework

Download full source code.

There are times when using Entity Framework Core that you need to use handwritten SQL instead of the fluent style statements.

Sometimes the fluent statement is too complex, the performance is not what you need, or you want to use a feature that is not supported by EF Core.

For example, if you are using Postgres, you may want to use the for update skip locked capability, which is not supported by EF Core fluent statements. A query might look like -

select * from products where .... for update skip locked

To execute this with EF Core use one of the FromSql FromSqlRaw, or FromSqlInterpolated methods.

But even if you query the database using handwritten SQL, you can still map the results to your entities, and update and save them in the familiar EF Core fluent way.

Here is an example -

var product = (await salesContext.Products
    .FromSql($"select * from products 
               where product_id <= {5} 
               order by product_id limit {1}
               for update skip locked")
    .ToListAsync()).First();

There are a few things to explain here -

• .ToListAsync() executes the query and returns the results as a list even if there is only one item.
• .First() I have a limit 1 in the query, so I know the list has a single item.

Now the product can be updated and saved in the usual way because it is being tracked by the context.

product.Price += 100; 
await salesContext.SaveChangesAsync();

That’s it. It is easy to mix handwritten SQL and the usual Entity Framework statements.

The source code included here uses Postgres, see this post to see how to run it in Docker.

For clarity here is the full code Program class -

 1using EntityFrameworkAndRawSQL.Data;
 2using Microsoft.EntityFrameworkCore;
 3
 4SalesContext salesContext = new SalesContext(new DbContextOptionsBuilder<SalesContext>()
 5    .UseNpgsql("Host=localhost;Port=5432;Database=salesdb;Username=postgres;Password=postgres").Options);
 6
 7salesContext.Database.EnsureCreated();
 8await salesContext.SeedAsync();
 9
10var originalProduct = (await salesContext.Products.FromSql($"select * from products where product_id <= {5} order by product_id limit {1} for update skip locked").ToListAsync()).First();
11Console.WriteLine(originalProduct);
12
13originalProduct.Price +=100; 
14await salesContext.SaveChangesAsync();
15
16var updatedProduct = await salesContext.Products.FindAsync(originalProduct.ProductId);
17Console.WriteLine(updatedProduct);  

Download full source code.