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 stands for language integrated query. It allows you to use "SQL style" query language directly within C# to extract information from data sources.

• That data source could be a SQL server database - this is Linq to SQL
• That data source could be an data context of entity framework objects - Linq to entities.
• That data source could be ADO.net data sets - Linq to Dataset.

That data source could also be an XML file - Linq to XML.
Or even just a Collection class of plain objects - Linq to Objects.

LINQ describes the querying technology, the rest of the name describes the source of the data being queried.

For a bit of extra background:

Datasets are ADO.net objects where data is loaded from a database into a .net Dataset and Linq can be used to query that data after it's loaded.

With Linq to SQL you define .net classes that map to the database and Linq-to-SQL takes care of loading the data from the SQL server database

And finally the Entity framework is a system where you can define a database and object mapping in XML, and can then use Linq to query the data that is loaded via this mapping.

Answer 3

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 that 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).