Are Linq to SQL and Linq to Objects queries the same?

Question

If we abstract out the DataContext, then are L2S and L2O queries identical?

I already have a working prototype which demonstrates this, but it is very simple and wonder if it will hold up to more advanced querying.

Does anyone know?

Answer 1

No they're not the same.

LINQ to Objects queries operate on IEnumerable<T> collections. The query iterates through the collection and executes a sequence of methods (for example, Contains, Where etc) against the items in the collection.

LINQ to SQL queries operate on IQueryable<T> collections. The query is converted into an expression tree by the compiler and that expression tree is then translated into SQL and passed to the database.

It's quite commonplace for LINQ to SQL to complain that a method can't be translated into SQL, even though that method works perfectly in a LINQ to Objects query. (In other cases, you may not see an exception, but the query results might be subtly different between LINQ to Objects and LINQ to SQL.)

For example, LINQ to SQL will choke on this simple query, whereas LINQ to Objects will be fine:

var query = from n in names
            orderby n.LastName.TrimStart(',', ' ').ToUpper(),
                    n.FirstName.TrimStart(',', ' ').ToUpper()
            select new { n.FirstName, n.LastName };

(It's often possible to workaround these limitations, but the fact that you can't guarantee that any arbitrary LINQ to Objects query will work as a LINQ to SQL query tells me that they're not the same!)