Differences between LINQ objects and LINQ-SQL queries

Question

I have been using LINQ to query POCO objects for some time, but I have not tried LINQ to SQL yet. I believe that LINQ to SQL queries are somehow converted to equivalent SQL queries, and with this in mind, I wonder if this affects how LINQ to SQL queries are written or should be written.

Are there any significant differences between LINQ to Object and LINQ to SQL that affect how I should write a query for ??

Answer 1

The main difference is, as you say, LINQ to SQL queries are converted to SQL. This means that there is code that you can write that is not actually convertible or has some subtly different semantics - and you will only find this at runtime.

For example:

var query = from person in people where person.Age == person.GetHashCode() select person; 

will compile fine, but will not execute at runtime because LINQ to SQL does not know what to do with GetHashCode() .

Basically I find LINQ to SQL is much harder to predict than LINQ to Objects. This does not mean that it is not useful - it is just a slightly different world. MS did a wonderful job of letting you write queries that very often just do what you expect from them, but it can't do everything.

Answer 2

LINQ to SQL will use column server sorting for Where and OrderBy . LINQ to Objects will use string comparisons. Thus, the former may be case insensitive, while the latter may be case sensitive. LINQ to Entities combines zeros. I assume L2S does the same, but I have not tested. So in L2E you can:

 let foo = item.Property.SomeNullableType 

... and foo will be null if the property is null. But in LINQ to Objects you need to do something like:

 let foo = item.Property != null ? item.Property.SomeNullableType : null 

... or you will get a null exception.

Answer 3

The MSDN link here and here should help you.

Answer 4

One difference I come across is differences in grouping.

When you group linq into objects, you get the result in the form of a hierarchy (keys with child objects).

When you group in SQL, you only get keys and aggregates.

When you group in linq in sql, if you request children (more than aggregates), linq to sql will re-query each group with a key to get these children. If you have thousands of groups, it can be thousands of rounds.

  //this is ok var results = db.Orders .GroupBy( o => o.CustomerID ) .Select(g => new { CustomerId = g.Key, OrderCount = g.Count() }); //this could be a lot of round trips. var results = db.Orders .GroupBy( o => o.CustomerID ) .Select(g => new { CustomerId = g.Key, OrderIds = g.Select(o => o.OrderId) }); // this is ok // used ToList to separate linqtosql work from linqtoObject work var results = db.Orders .Select(o => new {o.CustomerId, o.OrderId}) .ToList() .GroupBy(o => o.CustomerId) .Select(g => new { CustomerId = g.Key, OrderIds = g.Select(o => o.OrderId) });