The main difference is as you say, LINQ to SQL queries are converted into SQL. That means that there is code you can write which isn t actually convertible or has some subtly different semantics - and you only find that out at execution time.

For example:

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

will compile fine, but fail at execution time because LINQ to SQL doesn t know what to do with GetHashCode().

Basically I find LINQ to SQL a lot harder to predict than LINQ to Objects. That s not to say it s not useful - it s just a slightly different world. MS has done an amazing job at letting you write queries which very often just do what you expect them to, but it can t do everything.

LINQ to SQL will use the column DB server s collation for Where and OrderBy. LINQ to Objects will use string comparisons. So the former might be case-insensitive while the latter is case-sensitive. LINQ to Entities coalesces nulls. I presume L2S does the same, but I haven t tested. So in L2E you can do:

let foo = item.Property.SomeNullableType

... and foo will be null if Property is null. But in LINQ to Objects you d have to do something like:

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

... or you d get a null exception.

MSDN reference here and here should help you out.

One difference that I run into is differences in grouping.

When you group in linq to objects, you get a hierarchically shaped result (keys, with child objects).

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

When you group in linq to sql, if you ask for the child objects (more than aggregates), linq to sql will re-query each group using the key to get those child objects. If you have thousands of groups, that can be thousands of roundtrips.

  //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)
  });