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.

Entity Framework Core provides mechanisms

FromSql
- FromSqlRaw (EF Core 3.0)
- FromSqlInterpolated (EF Core 3.0)
ExecuteSqlCommand
- ExecuteSqlRaw (EF Core 3.0)
- ExecuteSqlInterpolated (EF Core 3.0)

FromSql

Execute Query

var context = new SchoolContext(); 
var students = context.Students.FromSql("SELECT * FROM Students").ToList(); //Replace with FromSqlRaw in EF Core 3.0
var students = context.Students.FromSql("SELECT * FROM Students Where FirstName = 'MJ'").ToList(); //Replace with FromSqlRaw in EF Core 3.0
var students = context.Students.FromSql("SELECT * FROM Students Where FirstName = @p0", "MJ").ToList(); //Replace with FromSqlRaw in EF Core 3.0
var students = context.Students.FromSql("SELECT * FROM Students Where FirstName = {0}", "MJ").ToList(); //Replace with FromSqlRaw in EF Core 3.0
var students = context.Students.FromSql($"SELECT * FROM Students Where FirstName = {firstName}").ToList(); //Replace with FromSqlInterpolated in EF Core 3.0

Execute SP

var students = context.Students.FromSql("exec Sp_GetStudents").ToList(); //Replace with FromSqlRaw in EF Core 3.0
var students = context.Students.FromSql("EXECUTE Sp_GetStudents").ToList(); //Replace with FromSqlRaw in EF Core 3.0
var students = context.Students.FromSql("Sp_GetStudents").ToList(); //Replace with FromSqlRaw in EF Core 3.0

with Parameter without parametrize (sql injection risk)

var students = context.Students.FromSql("exec Sp_GetStudents 'MJ'").ToList(); //Replace with FromSqlRaw in EF Core 3.0
var students = context.Students.FromSql("Sp_GetStudents 'MJ'").ToList(); //Replace with FromSqlRaw in EF Core 3.0
var students = context.Students.FromSql("Sp_GetStudents 'MJ', 'Ebrahimi'").ToList(); //Replace with FromSqlRaw in EF Core 3.0

Indexed parametrize

var name = "MJ";
var students = context.Students.FromSql("Sp_GetStudents @p0", name ).ToList(); //Replace with FromSqlRaw in EF Core 3.0
var students = context.Students.FromSql("Sp_GetStudents {0}", name ).ToList(); //Replace with FromSqlRaw in EF Core 3.0

Note : While this syntax may look like String.Format syntax, the supplied value is wrapped in a DbParameter and the generated parameter name inserted where the {0} placeholder was specified. (Prevent SQL Injection)

//Multiple Parameter
var firstName = "MJ";
var lastName = "Ebrahimi";
var students = context.Students.FromSql("Sp_GetStudents @p0, p1",  firstName, lastName).ToList(); //Replace with FromSqlRaw in EF Core 3.0
var students = context.Students.FromSql("Sp_GetStudents {0}, {1}",  firstName, lastName).ToList(); //Replace with FromSqlRaw in EF Core 3.0
// or parameters: new[] { firstName, lastName } (params object[])

Note : Parameters name dose not important. you can use parameters: new[] { x, y }

Indexed parametrize using string interpulation

var students = context.Students.FromSql($"Sp_GetStudents {name}").ToList(); //Replace with FromSqlInterpolated in EF Core 3.0 
var students = context.Students.FromSql($"Sp_GetStudents {firstName}, {lastName}").ToList(); //Replace with FromSqlInterpolated in EF Core 3.0

Note : Entity Framework Core will only parameterize interpolated strings if they are supplied inline to the FromSql method call. Interpolated strings declared outside of the FromSql method call will not be parsed for parameter placeholders and acts as concatenated string directly to the database, which is a SQL injection risk.

var sql = $"SELECT * From Authors Where AuthorId = {id}";
var author = db.Authors.FromSql(sql).FirstOrDefault(); //Must FromSqlInterpolated but now treat as Replace with FromSqlRaw in EF Core 3.0

Named parametrize using SqlParameter

var param = new SqlParameter("@FirstName", "MJ");
//or
/*var param = new SqlParameter() {
                    ParameterName = "@FirstName",
                    SqlDbType =  System.Data.SqlDbType.VarChar,
                    Direction = System.Data.ParameterDirection.Input,
                    Size = 50,
                    Value = "MJ"
};*/
var students = context.Students.FromSql("Sp_GetStudents @FirstName", param).ToList(); //Replace with FromSqlRaw in EF Core 3.0

Limitations :

The SQL query must return data for all properties of the entity type. (Result must be an entity type or keyless type)
The column names in the result set must match the column names that properties are mapped to.

Note this behavior is different from EF6. EF6 ignored property to column mapping for raw SQL queries and result set column names had to match the property names.

In EF Core if any columns are missing, or are returned with names not mapped to properties, an InvalidOperationException will be raised.
The SQL query can't contains related data. (not join)

However, in many cases you can compose on top of the query using the Include operator to return related data (see Including related data).

Note :

All entities in the result will be tracked by the DbContext.
FromSql can only be used to execute raw SQL queries or stored procedures to get the data. You can’t use it for INSERT/UPDATE/DELETE.

If you want to execute INSERT, UPDATE, DELETE queries, use the ExecuteSqlCommand.
Before EF Core 3.0, these method names were overloaded to work with either a normal string or a string that should be interpolated into SQL and parameters.

Starting with EF Core 3.0, Use FromSqlRaw, ExecuteSqlRaw, and ExecuteSqlRawAsync to create a parameterized query where the parameters are passed separately from the query string. (without $)

Use FromSqlInterpolated, ExecuteSqlInterpolated, and ExecuteSqlInterpolatedAsync to create a parameterized query where the parameters are passed as part of an interpolated query string. (with $)

In EF Core we can't query any custom data from stored procedure or sql query. FromSql used for specified entity type. ExecuteSqlCommand used for execute NonQuery command and return count of affected records as int.

So in before EF Core 3.0 (Query types used for quering view or stored procedure)

var sprocResults = await _dbContext.Query<MyDTO>()
                .FromSql("Get_MySp @Param1 = {0}, @Param2 = {1}",   p1, p2)
                .AsNoTracking()
                .ToListAsync();

Startin from EF Core 3.0 (Changed name from query types to key-less entity types)

var sprocResults = await _dbContext.Set<MyDTO>()
                .FromSqlRaw("Get_MySp @Param1 = {0}, @Param2 = {1}", p1, p2)
                .AsNoTracking()
                .ToListAsync();

More info : https://www.dotnettips.info/post/2777

ExecuteSqlCommand

var context = new SchoolContext(); 
var rowsAffected = context.Database.ExecuteSqlCommand("Update Students set FirstName = 'MJ' where StudentId = 1;");

context.Database.ExecuteSqlCommand("CreateStudents @p0, @p1", "MJ", "Ebrahimi"); // or parameters: new[] { "MJ", "Ebrahimi" }

Named parametrize using SqlParameter

var name = new SqlParameter("@CategoryName", "Test");
context.Database.ExecuteSqlCommand("exec AddCategory @CategoryName", name);

FromSql vs ExecuteSqlCommand ?

FromSql return IQeuryable that can use against Where().Order().FirstOrDefault().

EF Core will treat it as subquery and compose over it in the database. (select over select)

var author = db.Authors.FromSql($"SELECT * From     Authors Where AuthorId = {id}").FirstOrDefault(); //    Replace with FromSqlInterpolated in EF Core 3.0

Translated (for Sqlite) to :

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

FromSql with stored procedure is not composable, therefore LINQ command dose not compose.

Before EF Core 3.0, FromSql method tried to detect passed SQL can be composed or if not, It did [Client Evaluation] when the SQL was non-composable like a stored procedure.

Starting with EF Core 3.0, by disabling client evaluation, if you are using a stored procedure with composition then you will get an exception for invalid SQL syntax.

Mitigation : If you are using a stored procedure with composition, you can add AsEnumerable/AsAsyncEnumerable/ToList method right after the FromSql method call to make sure that EF Core doesn't try to compose over a stored procedure. (perform as linq to object)
FromSql with track entities but ExecuteSqlCommand dose not.