Practical .NET

Issuing SQL in Entity Framework

If you're considering a move into the world of LINQ and Entity Framework, you have to consider the possibility that LINQ and Entity Framework won't let you issue some bizarrely complicated SQL statement. Don't worry -- should that ever happen, you have options.

• By Peter Vogel
• 05/17/2011

Two caveats before we begin: First, in this column I'm not going to make an effort to come up with SQL statements that can't be handled through LINQ. Second, I'm assuming that you've already generated an Entity Framework model (added an ADO.NET Entity Model to your project, connected to a database, and worked through the Wizard to add some tables to the model).

Getting to ADO.NET in EF 1
If you want to retrieve data from your database with an arbitrary SQL statement, you can use the information embedded in your Entity Framework model to integrate with the ADO.NET objects you're familiar with. These three lines of code (with these directives) retrieve an SQLConnection object from my Entity Framework model:

using System.Data.Objects;
using northwndModel;
using System.Data.SqlClient;
using System.Data.EntityClient;
using System.Data;


northwndEntities ne = new northwndEntities();
EntityConnection econn = (EntityConnection)ne.Connection;
SqlConnection sconn = (SqlConnection)econn.StoreConnection;

With the SqlConnection object in hand, I'm free to issue any SQL statement that I want against my database. Just remember that if you do an update through ADO.NET, any objects already retrieved through Entity Framework won't reflect the changes -- use the ObjectContext's Refresh method to sync up your entities with your data. This example would update any Customer objects previously retrieved with the latest data from the database:

ne.Refresh(RefreshMode.StoreWins, ne.Customers);

Integrating with EF 4
But, in Entity Framework 4, there's an easier way to issue queries and you can do it in an object-oriented kind of way: Use the ExecuteStoreQuery method. ExecuteStoreQuery is a generic method that allows you to specify a class to hold retrieved data. The method will automatically create any necessary instances of the class and set any properties on the resulting objects with values from matching column names in the retrieved data.

If, for instance, I want to join the Customers and Orders tables together and retrieve the CustomerId from both tables (and the OrderId from the Orders table), I begin by adding a class with a property for every column that I want:

public class CustomerOrder
{
    public string CustomerId;
    public string OrderCustomerId;
    public int OrderId;
}

I can now issue an SQL statement that retrieves the OrderId and the two CustomerIds (aliasing one of the CustomerId columns so that it will be used to update the right property):

northwndEntities ne = new northwndEntities();
var resESQ = ne.ExecuteStoreQuery<CustomerOrder>(
   "Select Customers.CustomerId, " +
   "       Orders.OrderId, "
   "       Orders.CustomerId As OrderCustomerId " + 
   " From Customers Join Orders on " +
   "  Customers.CustomerId = Orders.CustomerId;");

The result is a collection that can be processed with a foreach loop or manipulated with LINQ.

Parameterized Queries
If you're using a parameterized query, then you can pass values for parameters as the second parameter to the ExecuteStoreCommand method. This example uses two parameters in its query:

parms[0] = "BC";
parms[1] = "Victoria";
var resESQ = ne.ExecuteStoreQuery<CustomerOrder>("Select... " + 
" Where Region = {0} and City = {1}", parms);

That second parameter can use a variety of formats. In the previous example I used the same {} delimiters that string class' Format method uses. But you can also use object parameters as this example does:

var resESQ = ne.ExecuteStoreQuery<CustomerOrder>("Select... " + 
" Where Region = @p0 and City = @p1", parms);

If you're familiar with ADO.NET, you're probably most comfortable with using SQLParameter objects:

SqlParameter[] parms = new SqlParameter[2];
parms[0] = new SqlParameter("City","Victoria");
parms[1] = new SqlParameter("Region", "BC"); ;
var resESQ = ne.ExecuteStoreQuery<CustomerOrder>("Select... " + 
" Where Region = @Region and City = @City", parms);

And, if you're passing just one parameter, you don't even need to create an array for your parameters:

SqlParameter parm = new SqlParameter("Region", "BC"); ;
var resESQ = ne.ExecuteStoreQuery<CustomerOrder>("Select... ." +
" Where Region = @Region", parm);

Leveraging Entity Framework
If you're combining columns from several different classes, creating a custom class is the way to go. But if what you want to retrieve is one of your model's entities -- if none of your "interesting" SQL is in your Select clause -- why not use the classes that Entity Framework has generated for you? Among the other benefits listed above, you get Entity Framework's change tracking, which means you can use Entity Framework to handle your updates.

To leverage the Entity Framework entities, in addition to specifying the class to use, you must also pass ExecuteStoreQuery the name of the EntitySet (table) the objects come from and a MergeOption that controls how new objects retrieved by the method are combined with ones retrieved earlier. This example specifies that my Customer entity class is to be used by ExecuteStoreQuery. Once I've retrieved the results, I update them and save it all back to the database:

northwndEntities ne = new northwndEntities();
var resESQ = ne.ExecuteStoreQuery<Customer>("Select * from Customers",
 "Customers",
System.Data.Objects.MergeOption.PreserveChanges);
foreach (Customer cust in resESQ)
{
 cust.City = "Saqinaw";
}
ne.SaveChanges();

Handling Updates
If your "interesting" SQL is an update query, then you can use the ObjectContext's ExecuteStoreCommand method, like this:

ne.ExecuteStoreCommand("Update Customers Set City = 'Goderich'");

Do recognize that, with ExecuteStoreCommand, you've bypassed Entity Framework so you'll need to use the Refresh method to keep your model in sync with your data.

You may never find that you'll need these tools -- that LINQ and Entity Framework will do everything you need. But it's good to know that they're there when things start to get "interesting."