Executing Raw SQL Queries using EF Core

Here are some methods;

DbSet.FromSqlRaw

The DbSet.FromSqlRaw method (DbSet.FromSql prior to Entity Framework Core 3.0) enables you to pass in a SQL command to be executed against the database to return instances of the type represented by the DbSet:

public class Book
{
    public int BookId { get; set; }
    public string Title { get; set; }
    public Author Author { get; set; }
    public int AuthorId{ get; set; }
    public string Isbn { get; set; }
}
...
public class SampleContext : DbContext
{
    public DbSet<Book> Books { get; set; }
}

using (var context = new SampleContext())
{
    var books = context.Books.FromSqlRaw("SELECT BookId, Title, AuthorId, Isbn FROM Books").ToList();
}

The DbSet must be included in the model (i.e. it can not be configured as Ignored). All columns in the target table that map to properties on the entity must be included in the SQL statement. The column names must match those that the properties are mapped to. Property names are not taken into account when the results are hydrated into instances of the entity.

If any columns are missing, or are returned with names not mapped to properties, an InvalidOperationException will be raised .

Parameterized Queries

It’s always advised to parameterize user input to prevent the possibility of a SQL injection attack being successful. Entity Framework Core will parameterize SQL if you use format strings with FromSqlRaw or string interpolation with the FromSqlInterpolated method:

// Format string
var author = db.Authors.FromSqlRaw("SELECT * From Authors Where AuthorId = {0}", id).FirstOrDefault();
// String interpolation
var author = db.Authors.FromSqlInterpolated($"SELECT * From Authors Where AuthorId = {id}").FirstOrDefault();

Both of these approaches result in the following SQL being generated;

SELECT "a"."AuthorId", "a"."FirstName", "a"."LastName"
FROM (
    SELECT * From Authors Where AuthorId = @p0
) AS "a"
LIMIT 1

Entity Framework Core will only parameterize format strings if they are supplied inline to the FromSqlRaw method call. Format strings declared outside of the FromSqlRaw method call will not be parsed for parameter placeholders. In effect, you will be passing a concatenated string directly to the database, which is a SQL injection risk.

The following example is dangerous and should not be used:

var sql = string.Format("SELECT * From Authors Where AuthorId = {0}", id);
var author = db.Authors.FromSqlRaw(sql).FirstOrDefault(); 

The generated SQL is unparameterized:

SELECT "a"."AuthorId", "a"."FirstName", "a"."LastName"
FROM (
  SELECT * From Authors Where AuthorId = 2
) AS "a"
LIMIT 1

Stored Procedures

The SQL command can be any valid SQL statement that returns all the required fields of data. It is possible to call stored procedures via the FromSqlRaw method:

using (var context = new SampleContext())
{
    var books = context.Books
        .FromSqlRaw("EXEC GetAllBooks")
        .ToList();
}

It is also possible to pass in values to named parameters:

using (var context = new SampleContext())
{
    var authorId = new SqlParameter("@AuthorId", 1);
    var books = context.Books
        .FromSqlRaw("EXEC GetBooksByAuthor @AuthorId" , authorId)
        .ToList();
}

Non-Entity Types and Projections

In versions of EF Core prior to 2.1, it is not possible to use the FromSqlRaw method to return a subset of properties (a projection) directly from the database. Using the Books DbSet above as an example, the following will not work:

using(var context = new SampleContext())
{
    var books = context.Books.FromSqlRaw("SELECT BookId, Title FROM Books").ToList();
}

You must project the result of the FromSqlRaw method call to return a subset of properties:

using(var context = new SampleContext())
{
    var books = context.Books
        .FromSql("SELECT * FROM Books")
        .Select(b => new {
            BookId = b.BookId,
            Title = b.Title 
            }).ToList();
}

However, this may prove inefficient as all columns from the mapped table will be returned by the FromSql method call.

Support for returning ad hoc (not DbSet) types from direct SQL calls is possible from EF Core 2.1.

Database.ExecuteSqlCommand

The DbContext exposes a Database property which includes a method called ExecuteSqlCommand. This method returns an integer specifying the number of rows affected by the SQL statement passed to it. Valid operations are INSERT, UPDATE and DELETE. The method is not used for returning entities.

using(var context = new SampleContext())
{
    var commandText = "INSERT Categories (CategoryName) VALUES (@CategoryName)";
    var name = new SqlParameter("@CategoryName", "Test");
    context.Database.ExecuteSqlCommand(commandText, name);
}

Note: You will need to add using Microsoft.Data.SqlClient; to make the SqlParameter type available to your code.

The ExecuteSqlCommand method can also be used to execute stored procedures:

using(var context = new SampleContext())
{
    var name = new SqlParameter("@CategoryName", "Test");
    context.Database.ExecuteSqlCommand("EXEC AddCategory @CategoryName", name);
}

Leveraging ADO.NET via the Context.Database property

In addition to the ExecuteSqlCommand method, the DbContext.Database property provides an API that allows you to perform ADO.NET operations directly. The GetDbConnection method returns a DbConnection object representing the context’s underlying connection. From that point, you can revert to the familiar ADO.NET APIs:

using (var context = new SampleContext())
using (var command = context.Database.GetDbConnection().CreateCommand())
{
    command.CommandText = "SELECT * From Table1";
    context.Database.OpenConnection();
    using (var result = command.ExecuteReader())
    {
        // do something with result
    }
}

EF CORE..

Add to favorites