Thursday, March 11, 2010

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:


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:


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.

No comments: