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:

Answer 4

Using EF core in net core 6

To see if the provider is an EF provider, use the following code:

if (queryable.Provider is Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider)
{
    // Queryable is backed by EF and is not an in-memory/client-side queryable.
}

One could get the opposite by testing the provider against System.Linq.EnumerableQuery (base type of EnumerableQuery<T> - so you don't have to test generics).

This is useful if you have methods like EF.Functions.Like(...) which can only be executed in the database - and you want to branch to something else in case of client-side execution.