Warning: Post contains vagueries and, almost certainly, inaccuracies - this is me just starting to think about these things :-)

LINQ to SQL and LINQ to Entities both provide object-relational-mapping capabilities made very accessible from C#/VB via LINQ but they're very different frameworks so I wanted to try and write something about that before planning to go off and try to delve some more into LINQ to Entities (having already spent quite a bit of time on LINQ to SQL).

For me, the top 3 things to know about LINQ to SQL would be;

1. It ships with Visual Studio 2008 as part of .NET Framework V3.5.
2. It operates against SQL Server databases (including CE). If you are not using SQL Server then this framework is not for you.
3. It takes object<-> relational mapping information in the form of .NET attributes or an XML file in order to provide a productive, ORM layer with a pretty close match-up between the .NET types that you program against and the relational tables/views that are in the DB.

For me, the top 3 things to know about LINQ to Entities would be;

1. It will not ship with Visual Studio 2008 and .NET Framework V3.5. It comes "later".
2. It operates against a range of relational stores - i.e. extensibility to other stores is a key feature of the framework.
3. It takes mapping information(possibly as XML files, but I believe it's extensible) to provide a productive, ORM layer against a conceptual model with a large degree of abstraction from the underlying logical model of your DB.

Before all these LINQ things came along, .NET data access was done through ADO.NET and I think it's interesting to line them up against ADO.NET as it highlights the differences between the two frameworks.

To my mind, LINQ to SQL is very much a consumer of ADO.NET in that it fits into the existing ADO.NET picture by sitting on top of the SQL provider (SqlClient). Here's a picture of how I think LINQ to SQL works in my head;

Apologies if I got any details wrong - it's not meant to be the definitive picture but it's meant to say that LINQ to SQL essentially grabs expressions, turns them into T-SQL and then fires them through the existing SQL Provider bits. So, LINQ to SQL is doing all of the work of going from a System.Linq.Expressions.Expression to a piece of T-SQL that can then be fed into a SqlCommand. It also has to do the work of taking the relational result-sets that are returned from executing that command and turning them back into objects in accordance with the provided mapping.

This is not a task to be underestimated (i.e. it's pretty hard). You can grab yourself a copy of Reflector one day and walk through the LINQ to SQL code and look at how it happens but I warn you that it might induce a nasty headache as it's all about recursively traversing a LINQ Expression tree to generate T-SQL from it.

It's not a job that I'd want to attempt :-) and, fortunatly, I don't need to worry about it because along comes the Entity Framework.

The Entity Framework is a different beast in that it plugs in new bits to ADO.NET. It adds a new provider (the Entity Provider) altogether and a new language that this provider understands (Entity SQL). So, you can expect to see EntityCommand, EntityDataReader, EntityAdapter and so on.

The unusual thing about this new provider is that it's a layered provider in that its job is to sit above existing providers. You might see ADO.NET today as looking like this picture;

where the application code formulates commands and executes them on connections in order to send the command text pretty much straight to the data source and, ultimately, get Readers back which can be used to populate DataSets and DataTables if necessary.

That ADO.NET architecture abstracts some of the details of accessing different underlying stores in the sense of providing the generic DataSet and DataTable and the store-specific "Adapter", "Reader", "Command", "Connection" that are implemented inside of a provider (providers for ADO.NET include the SqlClient bits but also OLEDB, ODBC, Oracle and so on).

However, that architecture does not fully abstract you from the specifics of the store that you're talking to in at least a couple of ways;

1. Queries are written in the "language of the store" - i.e. T-SQL, PL/SQL and so on.
2. Queries target the logical model of the store - i.e. directly against tables, views or perhaps invoke procedures and functions.

Now, with the Entity Framework, this model looks a bit different in that the Entity Provider is a generic piece of kit that sits above other providers. The picture becomes more like;

This additional provider provides those additional pieces of abstraction that the original ADO.NET architecture did not in that it;

1. Abstracts the underlying data store. The Entity Provider queries against a conceptual model that you define. There could be a 1:1 mapping between that conceptual model and the logical model of your store or there could be a wide degree of variation between those two models and the Entity Provider will do that translation for you (amongst other things).
2. Abstracts the language used to manipulate the underlying data store. The query language that the Entity Provider understands is a store-neutral language known as Entity-SQL.

So, clearly, there's a need to translate a couple of things at that Entity Provider layer.

There's a need to move from Entity-SQL into the language of the store such as T-SQL or PL/SQL. There's also the need (potentially, because it depends on the mapping) to move from the conceptual model that's being queried to the logical model of the store both when formulating a query and when presenting a result-set.

The EntityProvider can do the translation from conceptual model to logical model but, I guess, it can't really do the translation from Entity-SQL to the real query language because that's going to depend on the underlying provider that it doesn't actually know the details of.

So, post this conversion from the conceptual to the logical , the EntityProvider needs a way to talk to the "real" provider and tell it about the query that it wants translating. I guess that the picture looks a bit like this;

Again, don't shoot me on the accuracy of that diagram as I don't have any Entity Framework assemblies to decompile or anything to check this against at the time of writing. I'm just trying to build a picture of how this "probably" works :-)

One of the interesting questions that springs up for me in that diagram above is how the Entity Provider layer passes the query on to the "real" provider layer.

An existing ADO.NET provider only knows how to take a command as a piece of text and execute it so you get into a bit of a conundrum. Given that the EntityProvider doesn't know how to generate the language of the "real" provider (e.g. T-SQL) then how can it possibly pass the right kind of command to the "real" provider.

The answer is (I think) that it can't and so to make this work there need to be additions to ADO.NET. From looking at Dave's great notes up here (particularly here) and looking at the slide-decks and the sample provider what actually happens is that the EntityProvider turns the query that it receives into a "Command Tree" which is a data structure that captures the details of the "query" that was passed into the Entity Provider. This tree is then passed to the "real" provider which turns that into store-specific SQL for that provider's store.

So, this means that a provider has to have additional functionality in order to support this idea of taking a command tree and turning it into store specific SQL. That means that an existing 2.0 provider doesn't just become "Entity Framework enabled" without that work being done.

It also means that there must be a bit of a puzzle over what to do with existing ADO.NET providers such as the OLEDB provider or the ODBC provider because it's not easy to amend them to do this work because they don't know what the language of their store is - i.e. their whole reason to exist is to be agnostic. That's a bit of a puzzle! :-)

Getting back to LINQ, the picture when we're using LINQ to Entities must look something like;

Of all the pictures I've pasted above, this last one is the most dubious as I really don't have a firm idea just yet of where the different pieces sit but it must be something a little like this? I'll refine my pictures when I've got some new Entity Framework bits to live with.

Regardless of the correctness of the picture, we've got a query captured as an expression tree from a LINQ perspective and then that needs to be turned into what I'd call a conceptual command tree which is a transformation from the LINQ query to an Entity Provider query dealing with the conceptual and then there's a transformation from that to a logical command tree which is then handed to the "real" provider which generates the right kind of SQL for that thing.

This is a long enough post so I'll wrap up here. Hopefully, some of these pictures will become more "concrete" as I get to look in a lot more detail at the LINQ to Entities bits