How can I determine if a LINQ query is going to be LINQ to SQL vs. LINQ to Objects?

Usually the distinction between LINQ to SQL and LINQ to Objects isn't much of an issue, but how can I determine which is happening?

It would be useful to know when writing the code, but I fear one can only be sure at run time sometimes.

Solution

It's not micro optimization to make the distinction between Linq-To-Sql and Linq-To-Objects. The latter requires all data to be loaded into memory before you start filtering it. Of course, that can be a major issue.

Most LINQ methods are using deferred execution, which means that it's just building the query but it's not yet executed (like Select or Where). Few others are executing the query and materialize the result into an in-memory collection (like ToLIst or ToArray). If you use AsEnumerable you are also using Linq-To-Objects and no SQL is generated for the parts after it, which means that the data must be loaded into memory (yet still using deferred execution).

So consider the following two queries. The first selects and filters in the database:

var queryLondonCustomers = from cust in db.customers
                           where cust.City == "London"
                           select cust;

whereas the second selects all and filters via Linq-To-Objects:

var queryLondonCustomers = from cust in db.customers.AsEnumerable()
                           where cust.City == "London"
                           select cust;

The latter has one advantage: you can use any .NET method since it doesn't need to be translated to SQL (e.g. !String.IsNullOrWhiteSpace(cust.City)).

If you just get something that is an IEnumerable<T>, you can't be sure if it's actually a query or already an in-memory object. Even the try-cast to IQueryable<T> will not tell you for sure what it actually is because of the AsQueryable-method. Maybe you could try-cast it to a collection type. If the cast succeeds you can be sure that it's already materialized but otherwise it doesn't tell you if it's using Linq-To-Sql or Linq-To-Objects:

bool isMaterialized = queryLondonCustomers as ICollection<Customer> != null;