Practical .NET

Leveraging Raw SQL in Entity Framework Core

The ability to use SQL with Entity Framework Core has always been a cool feature. There's a new feature in Entity Framework Core that opens the door for whole new set of SQL-related functionality.

• By Peter Vogel
• 04/12/2018

Entity Framework Core provides a new tool for sending raw SQL to your database: FromSql. Initially, FromSql might sound like a replacement for Entity Framework's SqlQuery. However, that's missing the point (and the power) of FromSql.

The Differences and Replacements
It's critical to understand that FromSql is much more limited than SqlQuery in terms of what it can return. SqlQuery, which was available from the DbContext's Database object, allowed you to submit any SQL statement to create any set of objects you wanted. Typical code looked like this:

var cNames = db.Database.SqlQuery("Select FirstName, LastName from Customers");

FromSql, on the other hand, is called from one of the entity object collections on your DbContext object. Typical FromSql code looks like this:

var custs = db.Customers
.FromSql("Select * from Customers")
.ToList();

Effectively, then, FromSql is tied to a specific DbContext collection so you can only return entity objects from that collection (in my example, that would be my Customer object). Nor are you allowed to skip properties, either! Your SQL statement must provide a value for every property on your entity class.

One other difference: With SqlQuery the names used in your SQL statement's Select clause had to match the names of the properties on your entity object. That means if you'd used the Column attribute to tie a property called CustomerId to a column in the table called pkCID, then the Select clause in your SQL statement would need to include an item named after the property: CustomerId. With FromSql, the items in your Select clause must use the column names that properties are mapped to. Using my previous example, that means the Select clause in my SQL statement must include an item named pkCID.

Ad hoc queries like the ones that SqlQuery supported are on the roadmap for Entity Framework Core 2.1. However, as I write this, the current version of EFC is 2.0. If, in the meantime, you're looking for a substitute (and are feeling brave) you can consider using EFC's RelationalCommand class. I say "brave" because the documentation points out that RelationalCommand is for internal use only and might be altered or disappear at any time. You can also still use Context.Database.Connection to work with ADO using the ADO Command and DataReader objects (though, since those don't return objects, that seems to be missing the point of using Entity Framework). ExecuteSqlCommand is also available for submitting Insert, Update and Delete statements.

The Benefits
If you've been using SqlQuery this must all sound pretty awful ... but the reason those limitations are in place is so that FromSql can integrate with LINQ.

While you could use LINQ with SqlQuery, the result was inherently inefficient. In this code, for example, SqlQuery would return all of the "Status 1" Customers to my application and then use LINQ to filter the results in memory:

var custs = from c in db.Database.SqlQuery("Select * from Customers Where Status = 1")
            where c.LastOrderDate < DateTime.Now
            select c;

On the other hand, the equivalent FromSql query collapses the LINQ statement and the FromSql into a single query sent to the database. The result is that, in this example, only the Customers that meet all the criteria in both the SQL statement and the LINQ Where method will be returned to my client:

var custs = db.Customers
            .FromSql("Select * from Customers Where Status = 1")
            .Where(c => c.LastOrderDate < DateTime.Now)
            .ToList();

This integration allows you to retrieve related data by using an Include statement in your LINQ code. This example returns Customers with each Customer object's SalesOrders property populated with the Customer's SalesOrders:

var custs = db.Customers
            .FromSql("Select * from Customers")
            .Include(c => c.SalesOrders)
            .ToList();

You can also use FromSql to submit stored procedure calls (just begin the SQL statement with the Execute keyword), provided the stored procedure meets the requirements of FromSql.

Efficiency aside, FromSql is exciting for another reason: It gives you access to SQL features that LINQ doesn't support, including proprietary extensions. I've written some columns in the recent past where I bemoaned that lack in LINQ (temporal tables, for example). FromSQL is going to solve that problem. For example, you can integrate table-valued functions with your LINQ statement, provided you include the function in a Select statement:

var custs = db.Customers
            .FromSql("Select * from CustomersWithOrdersOver(10000)")
            .Include(c => c.SalesOrders)
            .ToList();

EFC works its magic by tucking your SQL statement into a subquery. To ensure that EFC can do that, you'll need to avoid doing anything in your SQL statement that would prevent it from being used in a subquery (for example: putting a semicolon at the end of your statement).

To be able to use FromSql, you'll need to add the EntityFrameworkCore.Relational package to your project (another example of how EFC splits tools specific to working with Relational database out of the core EFC package). However, if you're accessing a relational database, you've probably already done that.

Managing Parameters
As always when working with raw SQL there's a temptation to just concatenate raw input into the SQL statement, creating opportunities for SQL injection attacks. FromSql provides a variety of ways to integrate parameters, all of which (under the hood) generate a DbParameter object ... and DbParameter objects protect you against SQL injection.

You can use string interpolation to insert a value into your SQL statement by prefixing the string holding the statement with a dollar sign ($) and wrapping the name of the variable holding the value in braces ({ }). That's what this code does:

int cLimit;
cLimit = 10000;
var custs = db.Customers
            .FromSql($"Select * from Customers Where CreditLimit = {cLimit}")
            .ToList();

You can also use placeholders and values as you would with the string object's Format command. That's what this code does:

var custs = db.Customers
            .FromSql("Select * from Customers Where CreditLimit = {0}", cLimit)
            .ToList();

If you're comfortable with the @ syntax that T-SQL uses for parameters, you can use a DbParameter object. This code does that:

DbParameter cLimit = new SqlParameter("limit",110);
var custs = db.Customers
            .FromSql("Select * from Customers Where CreditLimit = @limit", cLimit)
            .ToList();

That's all cool but, let me be clear: FromSql isn't a replacement for SqlQuery (though I will be glad when EFC acquires similar functionality). FromSql is the tool that allows you to mix SQL and LINQ to create efficient queries while giving you access to the full power of SQL. That's not better but it is good to have.