Choosing an Entity Framework API

Last month, a question was asked in the ADO.NET Prerelease forum that went more or less like this:

Considering that there are many APIs you can use (Entity SQL, ObjectQuery<T>, LINQ to Entities), is there any guidance that could help me decide when to use each?

The best I could do based on my knowledge at the time:

It is matter of taste.

While my answer was partially correct and had the great quality of being easy to look at, I immediately realized I should do a better job in helping people choose the appropriate API for each of their scenarios.

I won’t pretend here to give the definitive and detailed answer, just a head start. You will find more information in our docs and I am sure this topic alone will easily fill a few chapters in upcoming books about the product.

Service Layers and Query languages

We basically support two distinct programming layers and two different query languages your applications can use:

Service layers and query languages supported		Query language
		Entity SQL	LINQ Queries
Service layer	Entity Services	Yes
	Object Services	Yes	Yes

For those coming from the Object/Relational Mapping world, one easy way to look at our stack is to understand that we have two mapping tools layered one on top of the other:

1. An Entity/Relational Mapper known as Entity Services.
2. An Object/Entity Mapper named Object Services.

Of course, once you have mapped your relational tables to entities and your entities to objects, what you get is a fully functional O/R Mapper.

But as it is usual in our profession, adding a level of indirection uncovers a lot of power and flexibility :)

First Service Layer: Entity Services

The public surface of this layer is the EntityClient component, which is a new type of ADO.NET provider that gives you access to a store agnostic entity-relationship model of your data called Entity Data Model (EDM), and decouples your code from the store specific relational model that lives underneath.

Besides a pair of new classes, the EntityClient contains most of the same types as previous providers: Connection, Command, DataReader, Parameter, Adapter, Transactions and a ProviderFactory.

To be able to use this layer, you typically need three elements:

1. ADO.NET provider that is specific to your database engine and has been extended to work with the Entity Framework. Basically, the extensions involve the inclusion of a detailed provider manifest,support for command objects consisting of command trees and the ability to generate store specific SQL from those command trees. An appropriate provider for SQL Server will be included with the Entity Framework, and various provider writers are working right now to give you access to non-Microsoft relational databases.
2. Mapping information in the form of SSDL, CSDL, and MSL files that describe your storage model, your application’s conceptual model and the mapping among the two. More recently we have added EDMX, a format that packages all the mapping information in a single file at design-time.
3. Queries expressed in Entity SQL (eSQL), which is a new dialect of SQL that delivers the power of the Entity Framework. Typically, the EntityClient will take a string containing eSQL everywhere your regular provider would accept a string containing store specific SQL.

One advantage of programming against this layer is that being the first public surface intended for application development, it is also the most lightweight.

Moreover, at this level you use full eSQL queries to obtain data readers and not actual entity classes. For this reason, we call EntityClient our “value” oriented programming interface. Neither the columns included in your rows, nor the source of your rows, nor the filtering, grouping or sorting criteria, are fixed at compile time. The query is just a string that we parse at run-time, and the results are just EntityDataReaders.

All this makes Entity Services suitable for applications that today typically exploit the flexibility of writing dynamic SQL queries, like reporting, ad-hoc querying, etc.

Notice however, that even when the EntityClient closely follows the traditional ADO.NET connected object model, you cannot get an ADO.NET DataSet on top. There are two main reasons for this:

1. The DataSet does not have the necessary constructs to represent the variety of relationships the EDM can support.
2. The EntityClient does not support the metadata protocols used to create the DataSet schema.

Moreover, the Entity Framework currently lacks a string based data manipulation language, so you cannot directly express UPDATE, INSERT and DELETE operations in eSQL. Given this, our EntityAdapter is hardly any similar to the previous DataAdapters. We do not even derive it from the DbDataAdapter class!

Second Service Layer: Object Services

Object Services lives immediately on top of the EntityClient, and provides your application an Object Oriented view your data. Many public classes live in this space, but the two most important are ObjectContext and ObjectQuery<T>.

ObjectContext

This object’s main role is to encapsulate the underlying EntityConnection, and serve as a porthole for objects performing CRUD operations.

When you choose to use our code generation, you get a type-safe ObjectContext that incorporates some methods specific to your data model.

ObjectQuery<T>

ObjectQuery<T> and its builder methods let you create queries in an completely object oriented way. It also provides a type-safe way to create queries. Most of the time, the shape and source of your data, the filtering, grouping and sorting criteria are known at compile time. So we call this our object-oriented programming interface.

You can still use fragments of eSQL with many builder methods, but the idea here is that you typically use ObjectQuery<T> in an early-bound manner to build queries that get compiled in your application. Even more important, the results of those queries can be full entity classes or new types created for projections.

First Query Language: Entity SQL

Entity-SQL is a text based query language that currently gives you the most expressiveness over the Entity Framework stack on late-bound scenarios. You can use Entity-SQL to get collections of rows in the Entity Services layer, but also instances of entity classes, when used with Object Services.

I highly recommend reading Zlatko Michailov’s Entity SQL post for a head start on the language and on its main differences with traditional SQL.

Second Query Language: LINQ Query Comprehensions

The Language Integrated Query is a set of strategic language extension Microsoft is including both in C# and VB that facilitate the creation of query expressions using a terse syntax familiar to anyone who has used SQL.

LINQ is very powerful, and it is broadly applicable since it aims to solve the problem of querying any data source, including objects in memory, databases and XML files while maintaining a consistent, object-oriented and type-safe programming interface.

For the Entity Framework, ObjectQuery<T> is the center of our LINQ implementation. This class implements the necessary interfaces to fully support the creation and deferred execution of queries comprehensions against our stack.

We have invested a great amount of work in correctly mapping CLR features that can be useful in queries to our EDM and query capabilities. Still, LINQ and the Entity Framework are built and optimized against different goals and assumptions, and some concepts of LINQ and the Entity Framework simply do not map one-to-one.

We certainly plan to continue investing in better alignment. But right now the reality is that there are some things you can do with Entity SQL that still cannot be expressed in LINQ, and there are a few things you can do with LINQ that still we cannot be translated or compose over in our LINQ implementation.

Conclusion

My original answer stays correct: Using one or other API to create your applications also has to do with a matter of taste. This is specially true thanks to the flexibility of ObjectQuery<T>, which allows you to mix and match start with query building methods that take eSQL fragments inside or LINQ queries. Just be aware that you could run into some corners scenarios in which we cannot completely go from one model to the other and back.

Edit: The assertion that you can mix and match LINQ and ESQL was incorrect. Once you started one way, you have to keep in that route in ObjectQuery<T>.

Comments

• Anonymous
  December 18, 2007
  Hi Diego, Nice overview. Can you give some specifics on things that are supported in LINQ but not via eSQL and vice versa? For example, I know eSQL cannot be used to perform DML while LINQ can. And dynamic queries can be written in eSQL, while LINQ is strongly typed. Any other important differentiations?