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!)

Answer 2

The query syntax is the same. If you use Enumerable.ToQuerable, even the types are the same. But there are some differences:

• some queries will only work on L2O and will result in an runtime error in L2S (e.g. if an expression tree contains a function that cannot be converted to SQL. This cannot be detected at compile time)
• some queries return different results on L2S and L2O (example: Max([empty sequence]) will throw an exception in L2O but return null in L2S)

So in the end, you will have to test against a database to be sure, but I think L2O is pretty good for simple, fast unit-tests.

Answer 3

Frustratingly, all IQueryably<T> implementations are, essentially, leaky abstractions - and it is not safe to assume that something that works in LINQ-to-Objects will still work under any other provider. Apart from the obvious function mappings, things like:

• LINQ-to-SQL can't possibly support all functions / overloads - listed here Data Types and Functions (LINQ to SQL)
• plus it depends on the actual database server; Skip/Take etc work differently on SQL Server 2000 than 2005+, and not every such translation works on SQL Server 2000
• EF doesn't support Single or Expression.Invoke (sub-expression invocation), or UDF usage
• Astoria supports different use of Single/First; as I recall it supports Where(pred).Single() - but not Single(pred) (which is the preferred usage for LINQ-to-SQL)

So you can't really use IEnumerable<T> for your unit tests simulating a database, even via AsQueryable() - it simply isn't robust. Personally, I keep IQueryable<T> and Expression away from the repository interface for this reason - see Pragmatic LINQ.