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, if you want to make use of existing stored procedures, or if you just prefer to write your own queries in SQL.

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 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 with the message:

'The required column '[name of first missing column]' was not present in the results of a 'FromSql' operation.'

Parameterized Queries

You are 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 or string interpolation:

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

// String interpolation
var author = db.Authors.FromSql($"SELECT * From Authors Where AuthorId = {id}").FirstOrDefault();

Both of these approaches result in the following SQL being generated (for SqLite):

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

var sql = $"SELECT * From Authors Where AuthorId = {id}";
var author = db.Authors.FromSql(sql).FirstOrDefault(); 

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

You can also explicitly create DbParameter objects for the provider that you are using. The first example demonstrates parameter construction for SqLite, and the second for SQL Server:

var p1 = new SqliteParameter("@Id", id);
var author = db.Authors.FromSql($"SELECT * From Authors Where AuthorId = @Id", p1).FirstOrDefault();

var p1 = new SqlParameter("@Id", id);
var author = db.Authors.FromSql($"SELECT * From Authors Where AuthorId = @Id", p1).FirstOrDefault();

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