What is the difference between "LINQ to Entities", "LINQ to SQL" and "LINQ to Dataset"

Question

I've been working for quite a while now with LINQ. However, it remains a bit of a mystery what the real differences are between the mentioned flavours of LINQ.

The successful answer will contain a short differentiation between them. What is the main goal of each flavor, what is the benefit, and is there a performance impact...

P.S. I know that there are a lot of information sources out there, but I'm looking for a kind of a "cheat sheet" which instructs a newbie where to head for a specific goal.

Answer 1

• all of them are LINQ - Language Integrated Query - so they all share a lot of commonality. All these "dialects" basically allow you to do a query-style select of data, from various sources.

• Linq-to-SQL is Microsoft's first attempt at an ORM - Object-Relational Mapper. It supports SQL Server only. It's a mapping technology to map SQL Server database tables to .NET objects.

• Linq-to-Entities is the same idea, but using Entity Framework in the background, as the ORM - again from Microsoft, but supporting multiple database backends

• Linq-to-DataSets is LINQ, but using is against the "old-style" ADO.NET 2.0 DataSets - in the times before ORM's from Microsoft, all you could do with ADO.NET was returning DataSets, DataTables etc., and Linq-to-DataSets queries those data stores for data. So in this case, you'd return a DataTable or DataSets (System.Data namespace) from a database backend, and then query those using the LINQ syntax

Answer 2

LINQ is a broad set of technologies, based around (for example) a query comprehension syntax, for example:

var qry = from x in source.Foo           where x.SomeProp == "abc"           select x.Bar; 

which is mapped by the compiler into code:

var qry = source.Foo.Where(x => x.SomeProp == "abc").Select(x => x.Bar); 

and here the real magic starts. Note that we haven't said what Foo is here - and the compiler doesn't care! As long as it can resolve some suitable method called Where that can take a lambda, and the result of that has some Select method that can accept the lambda, it is happy.

Now consider that the lambda can be compiled either into an anonymous method (delegate, for LINQ-to-Objects, which includes LINQ-to-DataSet), or to an expression-tree (a runtime model that represents the lambda in an object model).

For in-memory data (typically IEnumerable<T>), it just executes the delegate - fine and fast. But for IQueryable<T> the object-representation of the expression (a LambdaExpression<...>) it can pull it apart and apply it to any "LINQ-to-Something" example.

For databases (LINQ-to-SQL, LINQ-to-Entities) this might mean writing TSQL, for example:

SELECT x.Bar FROM [SomeTable] x WHERE x.SomeProp = @p1 

But it could (for ADO.NET Data Services, for example) mean writing an HTTP query.

Executing a well-written TSQL query that returns a small amount of data is faster than loading an entire database over the network and then filtering at the client. Both have ideal scenarios and plain-wrong scenarios, though.

The goal and benefit here is to allow you to use a single, static-checked syntax to query a wide range of data-sources, and to make the code more expressive ("traditional" code to group data, for example, isn't very clear in terms of what it is trying to do - it is lost in the mass of code).