LINQ to SQL, LINQ to Entities

Just read a fascinating thread on the MSDN forums:

Very inefficient SQL generation in EF?

(although one of the posters there complained about how the link to this thread is not permanent...)

In the new world of .NET data access, it is no longer acceptable to deal with DataTables in the Business Layer of your application. DataTable is meant to bridge between the world of datastore (read: database) and business entities. In former days, Visual Studio's offering in the realm of closing the gap between data records and business entities was the strongly-typed DataSet (ADO.NET, if you please). When you added an SQL Server database to your project (this is still true of VS 2008), the IDE immediately started a wizard to generate a .xsd file defining classes derived from DataTable (and its relatives, DataRow and so on) that would be strongly-typed to match your database's schema. In essence, this wizard was about halfway to mapping your data from the hierarchal-relational model to the object model. It was one step short of having your records translated to full-fledged objects.

In order to achieve the full translation, a new mapper was needed: one that would translate your hierarchal-relational model to business entities. The disadvantage of such a mapper would be the way it limits querying: if the object you are talking to is not a table or view, you can't query it.

With the advent of LINQ, the story changed. Microsoft developed LINQ to SQL to fill the gap between table and business entity, and with the capabilities of LINQ, you can write code to query collections of objects. It's nearly as easy to write code in LINQ as it is to query tables of records in regular SQL. (Sometimes it's actually quicker to code in LINQ, but complex hacker-style queries are still best in SQL.)

The people who make Visual Studio actually published TWO object-relational mappers with VS2008: LINQ to SQL and LINQ to Entities. LINQ to SQL is heavily tied to SQL Server; LINQ to Entities is part of the Entity Framework, which is supposed to support many data providers.

It seems that the Entity Framework is still in the experimental stage, not yet market ready. The people on the forum linked above were unimpressed with the way LINQ to Entities translates your query into SQL. LINQ to SQL, on the other hand, rates pretty well on the performance end with payoffs in flexibility.

The bad news is that LINQ to SQL is no longer under development; MS sees the Entity Framework as its next-generation ADO.NET. The good news is that the LINQ to SQL team has been integrated into the Entity Framework team, so there's hope that at least the SQL Server implementation of LINQ to Entities will be as good as LINQ to SQL.

This being the case, LINQ to SQL will stick around a while longer, at least until the Entity Framework team manages to port the good things (read: provider-specific optimizations) from LINQ to SQL into their SQL Server provider.