Practical .NET

Retrieve and Update Entity Framework with Dynamic Queries

You don't have to give up on creating dynamic queries just because you're using Entity Framework. Entity SQL and ObjectQuery will let you generate queries at runtime and still let you update your data through Entity Framework.

• By Peter Vogel
• 03/16/2015

There was a time, before LINQ and Entity Framework, when developers concatenated SQL statements together and passed them to ADO.NET to be executed. This was a more flexible system than LINQ with EF because it let you generate a query at runtime from the user's input.

LINQ and EF do take that flexibility away (and give, in return, object-oriented data access, compile-time syntax checking, automatically generated updates and IntelliSense support). But LINQ isn't the only way to leverage Entity Framework: You can also use Entity SQL (eSQL), which will let you generate queries at runtime while still giving you object-oriented data access through your EF model and automatic updates through the EF SaveChanges method. You do have to give up compile-time checking of your queries, though.

In an earlier column, I looked at using plain old SQL with EF -- an excellent solution if you want to customize the objects you retrieve and don't need to do updates. However, if you're willing to learn eSQL you can get that updating capability back. The good news is that eSQL looks very much like plain old SQL.

Creating a Query
To issue an eSQL query you need to access your database through the ObjectContext object. The issue here is, if you're using a current version of Entity Framework, then you're almost certainly accessing your database through the DbContext object. Your first step, therefore, is to retrieve the ObjectContext that's inside your DbContext object. Here's the code to do that in Visual Basic (AdventureWorksLTEntities is my DbContextObject):

Dim db As New AdventureWorksLTEntities
Dim oc As ObjectContext
oc = CType(db, IObjectContextAdapter).ObjectContext

The equivalent C# code looks like this:

AdventureWorksLTEntities db = new AdventureWorksLTEntities();
ObjectContext oc;
oc = ((IObjectContextAdapter) db).ObjectContext

If you're having compile-time errors with this code make sure you have an Imports or using statement for the System.Data.Entity.Core.Objects namespace (and remove any Imports or using statements for System.Data.Objects).

The next step is to create an ObjectQuery object tied to an entity in your EF model (in my sample code, I work with Customer entities from the AdventureWorks database). When you create the ObjectQuery, you must pass it the string containing your eSQL statement, the ObjectContext you've retrieved from the DbContext object and, optionally, a merge option (see "Merge Options" at the end of this article for a discussion of the merge options). Creating the ObjectQuery is, by the way, the only time you'll need to use the ObjectContext.

In this example, I'm using the OverwriteChanges option to create an ObjectQuery:

Dim sCusts As ObjectQuery(Of Customer)
Dim sql As String
sql = "Select Value cust " & 
  " From AdventureWorksLTEntities.Customers AS cust " &
  " Where cust.LastName = 'Gee'"
sCusts = New ObjectQuery(Of Customer)(sql, oc, MergeOption.OverwriteChanges)

Using the ObjectQuery Results
With the ObjectQuery created, you're ready to retrieve your entities. The first step is to open a connection to the database using the original DbContext object. Once you've opened the connection, you can process the results in your ObjectQuery as if they came from a LINQ statement. If you make changes to those entities, you can call the SaveChanges method on your DbContext object to send the changes back to your database.

This code, for example, corrects the customers' last name and then saves the resulting changes:

db.Database.Connection.Open()
For Each c As Customer In sCusts
  c.LastName = "Bee"
Next
db.SaveChanges()

You can use an ObjectQuery almost everywhere you would use the results of a LINQ query. This example applies a LINQ statement to the ObjectQuery's results and uses the output to set the DataSource property on a grid:

gView.DataSource = From c In sCusts
                   Where c.FirstName = "Ben"
                   Select c

Obviously, eSQL looks very much like plain old SQL with Select, From and Where clauses. The From clause is slightly different than what you would find in SQL because, instead of referencing tables in your database, the clause references the EF model with its collections. The Select clause looks different because it contains that Value keyword (I'm using that to convert the default return type of an eSQL statement, a DbDataRecord, into a Customer object to use in my code). But other than those two differences, if you know SQL then you know this eSQL statement.

There's more flexibility in eSQL than I've suggested here: I'm not obliged to return a whole Entity, for example, and I can use eSQL against any collection, not just EF (I'll look at some of that flexibility in a later column). However, when you need to dynamically construct updateable queries at runtime -- and are willing to learn a slightly different query language -- then eSQL could be your answer.