why func delegate is not translated to sql

This post is a sequence for the previous article which shows why the Func delegate is not translated into the SQL statements.

Let’s investigate why our func delegate is not translated as we have expected in the previous article.

Writing the queries to the console

We can see the query that will be translated for the queryables by writing out the IQueryable to the console.

Here it is

Expression<Func<Customer, bool>> exprPred = p => p.FirstName == "Design Engineer";
var names = context.Customers.Where(exprPred);
Console.Write("Query : " + names);
/*Query : SELECT
    [Extent1].[CustomerID] AS [CustomerID],
    [Extent1].[NameStyle] AS [NameStyle],
    [Extent1].[Title] AS [Title],
    [Extent1].[FirstName] AS [FirstName],
    [Extent1].[MiddleName] AS [MiddleName],
    [Extent1].[LastName] AS [LastName],
    [Extent1].[Suffix] AS [Suffix],
    [Extent1].[CompanyName] AS [CompanyName],
    [Extent1].[SalesPerson] AS [SalesPerson],
    [Extent1].[EmailAddress] AS [EmailAddress],
    [Extent1].[Phone] AS [Phone],
    [Extent1].[PasswordHash] AS [PasswordHash],
    [Extent1].[PasswordSalt] AS [PasswordSalt],
    [Extent1].[rowguid] AS [rowguid],
    [Extent1].[ModifiedDate] AS [ModifiedDate]
    FROM [SalesLT].[Customer] AS [Extent1]
    WHERE N'Design Engineer' = [Extent1].[FirstName]
    */

We got the SQL query for the IQueryable here and it is what it was expected.

Now, let’s try the same thing for our Func delegate and see what it will output when we write it to console.

Func<Customer, bool> funcPred = p => p.FirstName == "Design Engineer";
var names = context.Customers.Where(funcPred);
Console.Write("Query : " + names);
//Query : System.Linq.Enumerable+WhereEnumerableIterator`1[QueryProviderImpl.Customer]

The output is different here. This is making a call to WhereEnumerableIterator in Enumerable class.

Where() method in Enumerable class

Let’s look at the .Where() method source code and WhereEnumerableIterator class from the Microsoft source.

public static partial class Enumerable
{
    public static IEnumerable<TSource> Where<TSource>(this IEnumerable<TSource> source, Func<TSource, bool> predicate) {
        if (source == null) throw Error.ArgumentNull("source");
        if (predicate == null) throw Error.ArgumentNull("predicate");
        if (source is Iterator<TSource>) return ((Iterator<TSource>)source).Where(predicate);
        if (source is TSource[]) return new WhereArrayIterator<TSource>((TSource[])source, predicate);
        if (source is List<TSource>) return new WhereListIterator<TSource>((List<TSource>)source, predicate);
        return new WhereEnumerableIterator<TSource>(source, predicate);
    }

    //... some other methods go here
}

As said in the previous post, there are overloads for .Where method. One for the Expression and the other for the Func. Now the above one called as we are passing a Func to the second parameter.

The .Where() Extension method on the Enumerable class is returning an instance of WhereEnumerableIterator class.

WhereEnumerableIterator class

Here is the implementation of WhereEnumerableIterator class.

// WhereEnumerableIterator.cs
class WhereEnumerableIterator<TSource> : Iterator<TSource>
{
    IEnumerable<TSource> source;
    Func<TSource, bool> predicate;
    IEnumerator<TSource> enumerator;

    public WhereEnumerableIterator(IEnumerable<TSource> source, Func<TSource, bool> predicate) {
        this.source = source;
        this.predicate = predicate;
    }

    public override Iterator<TSource> Clone() {
        return new WhereEnumerableIterator<TSource>(source, predicate);
    }

    public override void Dispose() {
        if (enumerator is IDisposable) ((IDisposable)enumerator).Dispose();
        enumerator = null;
        base.Dispose();
    }

    public override bool MoveNext() {
        switch (state) {
            case 1:
                enumerator = source.GetEnumerator();
                state = 2;
                goto case 2;
            case 2:
                while (enumerator.MoveNext()) {
                    TSource item = enumerator.Current;
                    if (predicate(item)) {
                        current = item;
                        return true;
                    }
                }
                Dispose();
                break;
        }
        return false;
    }

    public override IEnumerable<TResult> Select<TResult>(Func<TSource, TResult> selector) {
        return new WhereSelectEnumerableIterator<TSource, TResult>(source, predicate, selector);
    }

    public override IEnumerable<TSource> Where(Func<TSource, bool> predicate) {
        return new WhereEnumerableIterator<TSource>(source, CombinePredicates(this.predicate, predicate));
    }
}

The WhereEnumerableIterator class will implement the Iterator class. The Iterator is an abstract class which implements IEnumerable and IEnumerator interfaces except the MoveNext() method. So, our WhereEnumerableIterator class will implement the iterator pattern and has the MoveNext() method implemented.

What happened to our func and its execution

Let’s come back to our query on the Customers entity

Func<Customer, bool> funcPred = p => p.FirstName == "Design Engineer";
var names = context.Customers.Where(funcPredicate);

Let me pull the MoveNext() method of WhereEnumerableIterator class so that it will be clear.

public override bool MoveNext() {
    switch (state) {
        case 1:
            enumerator = source.GetEnumerator();
            state = 2;
            goto case 2;
        case 2:
            while (enumerator.MoveNext()) {
                TSource item = enumerator.Current;
                if (predicate(item)) {
                    current = item;
                    return true;
                }
            }
            Dispose();
            break;
    }
    return false;
}

As we iterate the items in the names in a foreach or when .ToList() is called on the names, every item from the customer table is pulled from the database with the enumerator.Current and compared against the predicate(the func delegate in our case) that we pass in.

The difference?

The difference in query execution is fairly large because expression trees and func delegates on the entities take a different approach in execution.

If we pass an expression tree to .Where() method then the execution will be different as the query is translated into an appropriate query based on the query provider (SQL statements for SQL Provider).

If we pass a func delegate to .Where() method then the appropriate records from the database are pulled and compared against the predicate and will return the result.

Entity Framework implements a provider model for different databases such as SQL, Oracle, MySQL, PostgreSQL etc. These query providers will have to translate the IQueryables into SQL as the query for a database will be different for different databases.

The translation for the expression trees will be interesting as it involves providers, translations, expression visitors etc. As this is not a topic for this article I’m not pulling those topics.

If you are interested in finding how the expression tree translation works look at the references section.

References

• http://putridparrot.com/blog/creating-a-custom-linq-provider/
• https://weblogs.asp.net/dixin/understanding-linq-to-sql-10-implementing-linq-to-sql-provider
• https://referencesource.microsoft.com/#System.Data.Linq/SqlClient/SqlProvider.cs
• https://blogs.msdn.microsoft.com/mattwar/2007/07/30/linq-building-an-iqueryable-provider-part-i/
• https://blogs.msdn.microsoft.com/mattwar/2007/07/31/linq-building-an-iqueryable-provider-part-ii/