Executing Raw SQL Queries

Entity Framework Core provides mechanisms for executing raw SQL queries directly against the database in circumstances where you cannot use LINQ to represent the query (e.g. a Full Text Search), if the generated SQL is not efficient enough, or if you want to make use if existing stored procedures.

DbSet.FromSql

The DbSet.FromSql method 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.FromSql("SELECT * FROM Books").ToList();
}

All columns that map to properties on the entity must be included in the SQL statement, and the DbSet must be included in the model (i.e. it can not be configured as ignored).

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 FromSql method:

using (var context = new SampleContext())
{
    var books = context.Books
        .FromSql("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
        .FromSql("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 FromSql 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.FromSql("SELECT BookId, Title FROM Books").ToList();
}

You must project the result of the FromSql 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 using query types.

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 System.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);
}

Asynchronous version: ExecuteSqlCommandAsync

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
    }
}