.NET Backend Entity Framework SQL

Func<‌T> vs. Expression<‌Func<‌T>>

A few days ago I was playing with Entity Framework when suddenly an Exception popped out on the screen:

 

Additional information: LINQ to Entities does not recognize the method ‚System.String GetFullName(User)’ method and this method cannot be translated into a store expression.

 

That was because accidently I treated Expression<Func<T>> like if it was a Func<T>. Two minutes later the bug was fixed, and I was happy to move forward. But what I realized is the fact that many developers don’t get the difference between these two „beings, ” and they try to use them alternately without any explanation. Frankly, that’s kinda weird for me since it’s not that hard to understand. And I’ll do my best to proof that 😉

 

Exploring Func<T> and Expression<Func<T>>

Let’s us start with some simple code that should introduce us to the topic:

 

Func<int, int> pow = arg => arg * arg;

Expression<Func<int, int>> powExpression = arg => arg * arg;

 

These two lines look almost identical but believe me that they differ significantly. To notice that difference we need to print both objects. Te result looks as follows:

 

sample1

 

As you can read the first WritleLine method printed object type which in this case is Func<int, int>. By this, C# compiler says something like „Well, all I know is that you need some integer, and after execution, I’ll get back an integer.” That’s it. The compiler treats pow as a „black box” with some input and output. But it cannot look inside that until it needs that during runtime. It’s also worthwhile to mention that Func<T> is equivalent to the delegate. Here’s the example:

 

Func<int, int> powDelgate = delegate (int arg)
{
    return arg * arg;
};

 

Things look completely different when we come to the second line of the printed result. Surprisingly we’ve just got the expression – an observable recipe which describes the way to intensify a variable. That’s because by using the Expression<T> all code is stored in a tree-like data structure called an expression tree which allows the C# compiler to „play” with the that like if it were data. What’s even cooler is that Expression<T> has a Compile method that compiles the expression at runtime and generates Func<T>:

 

Expression<Func<int, int>> powExpression = arg => arg * arg;

Func<int, int> pow = powExpression.Compile();

 

That seems logical. Having a „recipe,” it’s easy to put it in a black box. But as you’ve probably guessed there’s no way back. Once you compile an expression, you won’t be able to reconstruct it using a func. Bearing in mind the fact that Func<T> doesn’t have information how to perform certain logic, we can guess why Entity Framework threw mentioned exception.

 

Playing with IQueryable<T>

The first thing we need to understand is the way that EF works. Like any other ORM (which stands for object-relational mapping) its primary task is to translate the object-specific code into the SQL. For this purpose, it uses the generic interface delivered by System.Linq namespace called IQueryable. Because LINQ is lazy, as long as we operate on this interface no round-trip to the database is performed, so we can easily create a particular query. All translation happens when we materialize IQueryable. The example is given below:

 

public void Test()
{
    var context = new Context();

    var qUsers = context.Users; //IQueryable

    var qFilteredUsers = qUsers.Where(u => u.Age > 30); //IQueryable

    var qOrderedFilteredUsers = qFilteredUsers.OrderBy(u => u.FirstName); //IQueryable

    var result = qOrderedFilteredUsers.ToList(); // Execute the SQL on the DB. I'm so lazy...
}

 

Now let’s take a look at the Where method:

 

sample2

 

There are four overloads available but focus on just two of them. We can choose between Func<User, bool> and Expression<Func<User, bool>>. Does it mean that I lied to you and we can use them alternately? Let’s execute the following code:

 

public void Test()
{
    var context = new Context();

    Func<User, bool> funcPredicate = user => user.Age > 30;

    Expression<Func<User, bool>> expressionPredicate = user => user.Age > 30;

    var funcResult = context.Users.Where(funcPredicate).Count();

    var expressionResult = context.Users.Where(expressionPredicate).Count();

    Console.WriteLine($"FUNC: {funcResult} | EXPRESSION: {expressionResult}");
}

 

Here’s the result:

 

sample3

 

Both queries returned the same result! But before you call me a moron let me present to you both SQL queries caught by the SQL Server Profiler:

 


//Expression<Func<User,bool>> predicate

SELECT
[GroupBy1].[A1] AS [C1]
FROM ( SELECT
COUNT(1) AS [A1]
FROM [dbo].[Users] AS [Extent1]
WHERE [Extent1].[Age] > 30
) AS [GroupBy1]

&nbsp;

//Func<User, bool> predicate
SELECT
[Extent1].[Id] AS [Id],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[LastName] AS [LastName],
[Extent1].[Age] AS [Age]
FROM [dbo].[Users] AS [Extent1]

 

What happened? Everything is clear when we navigate to the both Where overloads. The Expression<Func<T>> version operates on the IQueryable interface. And that seems natural. Linq to Entities could include the WHERE statement to our query since it had access to the expression tree. Using other words, it could read the recipe that said: „Hey you, give me only users older than 30!”. On the other hand, Func<T> version had to be implemented on top of the IEnumerable because all we can do with Func is to execute that. Since it’s impossible to translate it back to the expression, so Linq to Entities had to fetch all the data and then run the function for each element to count the number of the users. But that example didn’t throw an exception, right? Let’s move to the last one:

 

public class FuncTest
{
    public void Test()
    {
        var context = new Context();

        Expression<Func<User, ReadModel>> selector = user => new ReadModel
        {
            FullName = user.GetFullName()
        };

        var result = context.Users.Select(selector).ToList();
    }
}

public static class UserExtensions
{
    public static string GetFullName(this User user) => user.FirstName + " " + user.LastName;
}

public class ReadModel
{
    public string FullName { get; set; }
}

 

In this case, we’ll get the exception from the introduction. To be clear, the above code compiles but fails at runtime. Expression served here is correct, it takes an Entity and returns some ReadModel, but the problem lies in the fact that Linq to Entities doesn’t know how to translate the GetFullName method into SQL query since the expression tree does not represent it. What’s the solution? We need to move all method code inside the expression:

 


public class FuncTest
{
    public void Test()
    {
        var context = new Context();

        Expression<Func<User, ReadModel>> selector = user => new ReadModel
        {
            FullName = user.FirstName + " " + user.LastName
        };

        var result = context.Users.Select(selector).ToList();
    }
}

public class ReadModel
{
    public string FullName { get; set; }
}

 

That looks more complicated (especially when writing complicated query), but it’s way more efficient than fetching all the data from the DB.

 

Summary

I did my best to explain that topic, and hopefully, I’ll help you during your work. If you’ve got any questions related to that topic feel free to ask me in the comments section 🙂 As always I encourage you to follow me on Twitter and  Facebook to be up to date with fresh posts!