Sometimes you’ll find you need to issue a query or command that the Entity Framework can’t support. In fact this problem is common to most ORMs, which is why so many of them have a backdoor to the database.

The Entity Framework has a backdoor too…

.NET 3.5 SP1

In .NET 3.5 SP1 you can get to the underlying database connection through your ObjectContext.

Calling ObjectContext.Connection returns an IDbConnection, but not the one we need, it is an EntityConnection. However the EntityConnection has a StoreConnection property that does return what we need:

var entityConn = ctx.Connection as EntityConnection;
var dbConn = entityConn.StoreConnection as SqlConnection;

Once you have this connection you are free to issue a query or command in the normal ADO.NET way:

dbConn.Open();
var cmd = new SqlCommand("SELECT * FROM PRODUCTS", dbConn );
using (var reader = cmd.ExecuteReader())
{
    while (reader.Read())
    {
        Console.WriteLine("Product: ID:{0} Name:{1} CategoryID:{2}",
            reader[0].ToString(),
            reader[1].ToString(),
            reader[2].ToString()
        );
    }
}
dbConn.Close();

Pretty easy huh?

.NET 4.0:

In .NET 4.0 this is even better. There are 2 new methods hanging directly off ObjectContext.

  • ExecuteStoreCommand(..) for executing commands
  • ExecuteStoreQuery<T>(..) for executing queries

Using ExecuteStoreQuery<T>(..)

If you use ExecuteStoreQuery<T>(..), the EF will create and populate instances of the T for you. So you can write code like this:

foreach(var product in ctx.ExecuteStoreQuery<Product>(sql))
{
        Console.WriteLine("Product: ID:{0} Name:{1} CategoryID:{2}",
            product.Id,
            product.Name,
            product.CategoryId 
        );
}

To get this to work the Column names returned by the query must match the property names in the class and the class must have a default constructor. But the class doesn’t even need to be an Entity.

So for example if you have this class:

public class ProductView
{
    public int ID { get; set; }
    public string Name { get; set; }
    public string CategoryName { get; set; }
}

To materialize instances of it you just need to write your SQL such that it has ID, Name and CategoryName columns.

i.e. Something like this:

string SQL = @"SELECT P.ID, P.Name, C.Name AS CategoryName
               FROM Products P
               JOIN Categories C
               ON P.CategoryID = C.ID";

foreach (var pv in ctx.ExecuteStoreQuery<ProductView>(SQL))
{
    Console.WriteLine("{0} {1} {2}", 
                       pv.ID, 
                       pv.Name,
                       pv.CategoryName
    );
}

Now of course, this example is for demonstration purposes only, normally the query would be much more complicated, i.e. something that LINQ to Entities can’t handle natively.

In this particular example you could easily do this using standard LINQ to Entities code, see Tip 40 if you are wondering how.

Editing Entities returned from ExecuteStoreQuery<T>(..)

If the classes being created are indeed Entities, and you want to edit them, you have to provide a little more information:

var productToEdit = ctx.ExecuteStoreQuery<Product>(sql,
      "Products",
       MergeOption.PreserveChanges
).Single();
productToEdit.CategoryId = 6;
ctx.SaveChanges();

Here the second parameter is the name of the EntitySet that the Product belongs too, and the third parameter tells the EF how to merge these entities with any duplicates that might already be in the ObjectContext.

If you do this right, calling SaveChanges() will flush the changes made to productToEdit back to the database.

Using ExecuteStoreCommand():

This one is very simple, you issue some command, for example a batch update method, and you will get back whatever the underlying provider returns, typically the number of rows affected:

// 10% inflation day!
ctx.ExecuteStoreCommand(
  "UPDATE Products SET Price = Price * 1.1"
);

It is that simple.

Enjoy.