Executing SQL Queries in EF Core

So far we have seen different ways of writing LINQ queries using Entity Framework Core, but sometimes we cannot express certain complex scenarios as LINQ queries or the LINQ query might not be as efficient as the SQL query, in those cases we will have to execute SQL queries directly.

Entity Framework Core allows us to execute SQL queries against the underlying database with some restrictions. To execute SQL queries directly we need to use the FromSql extension method with the context object as follows.

      var employeeList = _context.Employee

          .FromSql("SELECT * FROM dbo.Employee")

          .ToList();

If you check the profiler you will notice that the same SQL query is triggered against the SQL Server database instead of the custom EF Core query.

SELECT * FROM dbo.Employee

We can also parameterize the query by passing custom values in the WHERE clause as follows.

       var employeeList = _context.Employee

           .FromSql("SELECT * FROM dbo.Employee WHERE EmployeeId = {0}", 1)

           .ToList();

The corresponding SQL query in the Profile is

exec sp_executesql N'SELECT * FROM dbo.Employee WHERE EmployeeId = @p0

',N'@p0 int',@p0=1

Also we can perform other operations like ORDER BY in the raw SQL Query as follows.

       var employeeList = _context.Employee

           .FromSql("SELECT * FROM dbo.Employee WHERE Age > {0} ORDER BY Name", 30)

           .ToList();

And the corresponding SQL query in the Profile will be

exec sp_executesql N'SELECT * FROM dbo.Employee WHERE Age > @p0 ORDER BY Name

',N'@p0 int',@p0=30

Notice that we are using a String.Format kind of syntax to pass parameters to the SQL Queries, from EF Core 2.0 onwards we can also use the string interpolation syntax as follows which will also produce the same result.

      int age = 30;

      var employeeList = _context.Employee

          .FromSql($"SELECT * FROM dbo.Employee WHERE Age > {age} ORDER BY Name")

          .ToList();