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

Question

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.

Answer 1

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;

Related: EF ICollection Vs List Vs IEnumerable Vs IQueryable

Answer 2

The first solution comes into my mind is checking the query provider.

If the query is materialized, which means the data is loaded into memory, EnumerableQuery(T) is used. Otherwise, a special query provider is used, for example, System.Data.Entity.Internal.Linq.DbQueryProvider for entityframework.

var materialized = query
                  .AsQueryable()
                  .Provider
                  .GetType()
                  .GetGenericTypeDefinition() == typeof(EnumerableQuery<>);

However the above are ideal cases because someone can implement a custom query provider behaves like EnumerableQuery.

Answer 3

I had the same question, for different reasons.

Judging purely on your title & initial description (which is why google search brought me here).

Pre compilation, given an instance that implements IQueryable, there's no way to know the implementation behind the interface.

At runtime, you need to check the instance's Provider property like @Danny Chen mentioned.

public enum LinqProvider
{
    Linq2SQL, Linq2Objects
}

public static class LinqProviderExtensions
{
    public static LinqProvider LinqProvider(this IQueryable query)
    {

        if (query.Provider.GetType().IsGenericType && query.Provider.GetType().GetGenericTypeDefinition() == typeof(EnumerableQuery<>))
            return LinqProvider.Linq2Objects;
        if (typeof(ICollection<>).MakeGenericType(query.ElementType).IsAssignableFrom(query.GetType()))
            return LinqProvider.Linq2Objects;

        return LinqProvider.Linq2SQL;
    }
}

In our case, we are adding additional filters dynamically, but ran into issues with different handling of case-sensitivity/nullreference handling on different providers. Hence, at runtime we had to tweak the filters that we add based on the type of provider, and ended up adding this extension method: