LINQ to Entities vs. LINQ to SQL - What should I use and when?

The CTP of ADO.NET vNext just came out recently, which includes a build of the ADO.NET Entity Framework. While the EF includes things like Entity SQL and so on, the parts of the EF that I am mainly concerned with are the EDM (Entity Data Model) itself, the Mapping Provider for ADO.NET, and most importantly: LINQ to Entities.

LINQ to Entities allows me to write C# LINQ queries that run against a conceptual data model. This is huge. I cannot stress enough to you how unbelievably forward-thinking and useful it is to be able to run queries against a conceptual object model as opposed to a database schema. The issue that a lot of folks have is in trying to decide if they need to use the Entity Framework or if they are fine with plain-vanilla LINQ to SQL (formerly called DLINQ). The following table provides a summary of the feature set (there is admittedly much more than what I've included in this small table, but this at least made the decision easy for me to make) comparison between the two systems. What you need to realize is that LINQ to Entities is a superset of LINQ to SQL. If the entities you have created in your EDM are backed by a SQL Server 2005 database, LINQ to Entities (L2E for short from now on) will create dynamic SQL statements that connect the entity model with the live database instance and provide your code with results.

Feature	LINQ to SQL	LINQ to Entities
Language Extensions Support	Y	Y
Language Integrated Database Queries	Y	Y
Many-to-Many (3way Join/Payload relationship)	N	N
Many-to-Many (No payload)	N	Y
Stored Procedures	Y	N (to be added)
Entity Inheritance	N	Y
Single Entity From Multiple Tables	N	Y
Identity Management / CRUD features	Y	Y

So rather than discuss the areas in which L2SQL and L2E are similar, I'm only going to cover the points at which they diverge.

First is the object model. When you use SQLmetal and DLINQ to create an object model from a database schema, you are consuming the database schema and immediately creating classes that represent that schema. These classes are slathered with attributes that tell DLINQ how to communicate with the database. If you need to change the object model, you must do so directly within the generated classes. If you change the database schema, you either have to figure out how to manually tweak the generated classes, or re-run SQLmetal. The Entity Framework provides a layer of abstraction above the database : you define a conceptual object model, you define the database schema in XML, and then you create a logical mapping between your conceptual object model and the database schema. Using this model, you are extremely well insulated from database changes. Most database changes can be absorbed by the schema and mapping without requiring a change to the object model - making it so you don't need to refactor and rebuild a bunch of objects that you're using throughout your application.

The second major difference is only made possible by the first. The immense power of the abstraction layer above the database provided by the Entity Framework gives you the ability to do things like entity inheritance and entity composition. This means that you can create an entity that is composed of columns originating in multiple tables without writing any complex join logic.

Building on entity composition is entity inheritance. This allows you to create entities that inherit from each other. For example, you could create an entity called VideoGame in your online retail application. There are many different kinds of video games, but doing things like:

...... WHERE gm.GmType = 402 ....

Just isn't very object-oriented. It makes many programmers cringe to have to look at stuff like that (and if it doesn't make you cringe, shame on you!). What we really should be able to do is something like this:

var ps2Games = from game in AllGames where game is Playstation2Game orderby game.Rating select game;

Now that is code that I can read, I can refactor it, it is easy to maintain, and I don't need to go track down the original author of the code to figure out what the intent of the code is. All that, and it still accomplishes the same high-performance result as the original T-SQL query. Using entity inheritance and conditional properties you can create classes that respond to the OOP-familiar is keyword but still perform the same gm.GmType = 402 schema-dependent query in the backend database.

So, to sum it up, here's my advice:

• If you want the added security of insulation and loose coupling from the underlying database schema to make your object model more resilient to change, use the Entity Framework
• If you find that you need the features of entity inheritance and entity composition, use the Entity Framework
• If you already have a large DLINQ codebase (oddly enough, I do) that is running just fine without entities, you probably don't need to spend the time to refactor out DLINQ to replace it with L2E.
• If you want to run LINQ queries against an object model, but your object model is a 1:1 mirror of the tables in your database, you probably don't need the EF.
• Not included in the chart above, ADO.NET vNext has a powerful "client-views" engine which will only get better with time and is just more incentive for adopting the new stuff.

In short, while ADO.NET vNext and the Entity Framework is certainly a hot new thing, there are a few cases where you might be better off spending your time elsewhere. For the rest of you, stay tuned because I'm going to put up some Entity Framework samples in the coming days.

p.s. Thanks to Pablo from the ADO.NET team for clarifying that LINQ to Entities does not actually use what we used to refer to as DLINQ for generating dynamic SQL statements for Entity Model mapping to database schema, that is handled by a shared engine as he mentions in a comment on this post.

tags: l2e  framework  entitylinq  xlinq  entity  l2xml  entities  netfx3  adoef  l2sql  linq  dlinq  

links: digg this    del.icio.us    technorati    reddit