Practical .NET

Dynamic Data Access with Plain Old SQL and SqlQuery

You don't have to give up using dynamic SQL just because you're using Entity Framework. The Entity Framework SqlQuery method will give you back the flexibility of dynamic SQL and still let you work with your data in an object-oriented way.

• By Peter Vogel
• 02/03/2015

In the bad old days of ADO.NET and SQL, developers frequently concatenated strings together in order to create SQL queries at run time. Provided that you took care to protect your application from SQL injection attacks by using SqlParameter objects to handle any input from the user, this strategy resulted in very flexible applications.

LINQ takes away that flexibility by limiting you to building your queries at compile time. In return, LINQ gives you object-oriented data access, compile-time syntax checking, automatically generated updates and IntelliSense support. Still, there are times when the ability to construct a data access query at run time can be useful. Entity Framework provides a solution that still gives you object-oriented data access: SQL statements used with the SqlQuery method. Compared to using LINQ, you must give up much of your compile-time syntax checking, automatic updates, and most IntelliSense support. However, SqlQuery helps to make up for those sacrifices by giving you the DbRawSqlQuery object in exchange.

Retrieving Objects with SQL
I discussed SqlQuery in an earlier column about retrieving results from stored procedures. However, SqlQuery works equally well with SQL statements, allowing you to submit SQL queries to an Entity Framework model and get back objects in return.

To use SqlQuery, just assemble your SQL statement as a string and then pass it to the SqlQuery method (SqlQuery is available from the Database property of your Entity Framework DbContext object). When calling SqlQuery, you also specify the class to be used with the results of your query. SqlQuery returns a DbRawSqlQuery object that you can use to manage your query (as with a LINQ query, your SQL query isn't passed to the database engine for processing until you work with the results of the query). When your results are returned from the database, DbRawSqlQuery creates an object for each row and sets the objects' properties from the values in the columns returned by the SQL statement.

As an example, the following code retrieves the CustomerID and LastName columns from a table called Customer, specifying that the results are to be used to create a DbRawSqlQuery object that returns CustomerShort objects. The code then uses the DbRawSqlQuery object's ToList method to have the query submitted to the database. As the results are returned, a CustomerShort object is created for each row. The resulting collection of objects is used to update a combobox:

Dim db As New AdventureWorksLTEntities
Dim sql As String
Dim sCusts As DbRawSqlQuery(Of CustomerShort)

sql = "Select c.CustomerID, c.LastName FROM Customer as c"
sCusts = db.Database.SqlQuery(Of CustomerShort)(sql)
ComboBox1.DataSource = sCusts.ToList

One warning: The objects produced by this query aren't tracked by Entity Framework. Any changes you make to these objects won't be propagated back to the database when you call SaveChanges on your DbContext object.

Class Limitations
In order for SqlQuery to populate the objects' properties with the right column values, it's essential the property names on your class match the column names of the result that your SQL statement is returning. Here's my CustomerShort class that works with the SQL statement in my example:

Public Class CustomerShort
  Public Property CustomerId As Integer
  Public Property LastName As String
End Class

You can't, for example, use the Column attribute to tie a property with one name to a column with a different name.

Needing to have a class already created at compile time does put some limitations on how dynamic your SQL can be. However, if you have the same columns listed in all of the versions of your SQL statement's Select clause, then you can use the same class with every one of your SQL statements. It's also a "no harm, no foul" situation if your class has more or less properties than the SQL statement has columns: Columns with no matching properties are ignored, as are properties with no matching columns. This means that, if the columns in your Select clause do vary, your best solution may be just to define a class with a property for every column that might ever appear in your Select clause to use with SqlQuery.

Exploiting SqlQuery
You can also pass parameters to your SqlQuery query by including placeholders in your SQL statement. In the following code, my SQL statement includes a Where clause that tests against the value in the variable CustomerName. The SQL statement has a placeholder ({0}) that will be filled in with the value I pass as the second parameter to the SqlQuery method:

sCusts = db.Database.SqlQuery(Of CustomerShort)(
                        "SELECT c.CustomerID, c.LastName " &
                        "FROM SalesLT.Customer as c Where LastName = {0}",
                        CustomerName)

The parameters you pass are automatically wrapped in Parameter objects to protect you from SQL injection attacks.

However, the jewel in the crown of the SqlQuery method may be the DbRawSqlQuery object that the SqlQuery method returns: Like the output of a LINQ query, DbRawSqlQuery has an enormous number of methods associated with it that you can use to manage your query.

For example, if you'd prefer not to have your application wait while the data is retrieved you can use DbRawSqlQuery's ToListAsync method to allow your application do something else in parallel while your data is being retrieved. To use the DbRawSqlQuery's ToListAsync, you just need to flag the method containing your code with the Async keyword and prefix the method call with the Await keyword. That's what this code does:

Private Async Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
  Dim db As New AdventureWorksLTEntities
  Dim sCusts As DbRawSqlQuery(Of CustomerShort)
  Dim sql As String

  sql = "Select c.CustomerID, c.LastName FROM Customer as c"
  sCusts = db.Database.SqlQuery(Of CustomerShort)(sql)
  ComboBox1.DataSource = Await sCusts.ToListAsync

Now, as soon as processing reaches the Await keyword, control is returned to the calling code (in this case, a form). The data will then be fetched by SqlQuery (and the combobox populated) in parallel with whatever the calling code is doing. Be aware, though, that using Async and Await like this will only let you handle the simplest of scenarios. For more complex scenarios you'll want to leverage the Task object returned by ToListAsync.

SqlQuery gives you back the flexibility of dynamic SQL when you need it and, to compensate with what you have to give up, throws in the DbRawSqlQuery object. As you explore the DbRawSqlQuery object, you may find that's where the real power in using SqlQuery resides.