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.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 with the message:
'The required column '[name of first missing column]' was not present in the results of a 'FromSqlRaw' 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 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 (for SqLite):
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
Entity Framework Core includes an analyzer that will warn you if you try to adopt this pattern:
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.FromSqlRaw($"SELECT * From Authors Where AuthorId = @Id", p1).FirstOrDefault();
var p1 = new SqlParameter("@Id", id);
var author = db.Authors.FromSqlRaw($"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 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 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 Microsoft.Data.SqlClient;
to make theSqlParameter
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
}
}