When to use Entity Sql vs LINQ to Entities

Microsoft now has so many different data access technologies, how do you choose? ADO.Net served us well for many years, and then Microsoft introduced LINQ - Language INtegrated Query. Dot-Net 2.0 offered LINQ to SQL. With .Net 3.5 and 4.0 we have the Entity framework and LINQ to Entities.

In addition there is also Entity SQL.

When would you use this? My take on it is that if you already know which Entity Model you're using at the time you're writing the code then LINQ to Entities is easier because you can take full advantage of intellisense. If you want to do more abstract coding that will work on any model then Entity SQL will allow you to write the SQL statement dynamically at run time.

For example, with LINQ to Entities you can do this:

NorthwindEntities context = new NorthwindEntities();
IQueryable<Product> qry = from p in context.Products
where p.UnitsInStock < p.ReorderLevel
select p;

In the above example when you type "IQueryable<", "context.", and "p." you get intellisense that helps you select the right entity and field names.

So when you know ahead of time that you want to get data from a specific table in a specific model then LINQ to Entities is easier.

Let's say that you want to do something more generic. For example, let's say you want to select the maximum value from some column of some unknown type in some table. You can do it with Entity SQL like this:

public T MaxValue<T>(ObjectContext context, MetaColumn pkCol)
{
string maxQuery = "SELECT Max(p." + pkCol.Name +
") FROM " + pkCol.Table.Name + " as p";

ObjectQuery<DbDataRecord> query =
new ObjectQuery<DbDataRecord>(maxQuery, context);

DbDataRecord rec = query.First();
return (T)rec[0];
}

We use generics to specify the type, T, of the result, which could be an int or a double, or a string, or any type that would work with the Max function. We're given an ObjectContext and the MetaColumn for which we want to find the max.

Let's say that the MetaColumn is the Id column in the Products table. The Entity SQL would end up being:

SELECT Max(p.Id) FROM Product as p

But it would work with any column of any type in any table. The ObjectQuery gives you a generic collection of generic columns which use can cast to the type that you need using generics.

So when the query is fully known at compile time then LING to Entities is easier, but when you need to construct a query at run time then Entity SQL gives you that flexibility.