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.

Introduction to Entity Sql

The ADO.Net Entity Framework is the latest evolution in data access from Microsoft, making ORM (object relational mapping) part of the .Net Framework. As I learn the basic of this new technology I thought I'd take you along for the ride.

There are two methods for querying an entity model within your code, Entity SQL (eSql) and LINQ to Entities. The latter is an extension of LINQ (Language INtegrated Query), Microsoft's general-purpose object query syntax that is built into .Net languages C# and VB. There are several varieties of LINQ: LINQ to SQL, LINQ to Entities, LINQ to XML, etc. You can create your own extensions to LINQ if have a specialized set of objects you wish to query.

Entity SQL uses a syntax that is similar to SQL and returns a generic collection, ObjectQuery>T<.

The basic syntax for an eSql query is:

SELECT Entity.property FROM Entity

It is similar to transact-Sql except all column or property names must qualified in select clause. You use aliases in the FROM clause to save typing:

SELECT e.property FROM Entity AS e

If you were querying from an entity model of the Northwinds database this would be a valid query:

SELECT v.AccountNumber, v.Name FROM Vendors AS v

One thing to remember is that entity collections are plural. So eventhough the underlying Sql Server table is Vendor, singular, the entity collection is Vendors, plural.

Where do you use this Sql statement? You create an object query, which can either be of an entity type or use the generic dbDataRecord. The constructor of the ObjectQuery takes two arguments:

• The Entity Sql
• An Entity Context

AdventureWorksEntities advEnt = new AdventureWorksEntities();
String sql = "SELECT v.AccountNumber, v.Name FROM Vendors AS v";

ObjectQuery query = new ObjectQuery(sql, advEnt);

The above code will return a collection of that you can index just like an array:

foreach (DbDataRecord rec in query)
{
Console.WriteLine("{0}\t{1}", rec[0], rec[1]);
}

That gets us started with Entity Sql. In future posts I'll many of the other features of this new data access method.