Don’t use Func delegate on the Entity Framework entities

In this post, I’ll show you what happens when you use a func delegate on the Entity Framework entities.

I have seen a case where a developer wrote a LINQ query with Func delegate which will get the data from the database and returns back to the UI.

The query was working fine but it needs performance tuning as it is taking more time for a simple query.

Example: LINQ Query

This is the LINQ query that we are interested in.

public static void Main(string[] args)
{
    Func<Employee, bool> dynamicFilter = GetDynamicQuery();
    var results = EmployeeContext.Employee.Where(dynamicFilter).Count();
}

public static Func<Employee, bool> GetDynamicQuery()
{
    Func<Employee, bool> e;
    //the lambda expressions set to the result "e" are dynamically obtained.
    //For the purpose here, let's assume we are getting the following
    //lambda expression
    e = p => p.JobTitle == "Design Engineer";
    return e;
}

The above code will execute and will return the employees whose have the designation as “Design Engineer” (it is actually dynamic based on few other conditions).

Check out this article if you are interested in writing dynamic queries with Expression Trees

If you don’t know what will happen under the hood then you will fail to refactor this and will certainly write a stored procedure to improve the performance.

Background on LINQ query execution

When a LINQ query is executed, it finds the Expressions in the query and will translate into appropriate SQL queries and will be submitted to the server. This gives us the result.

Ok, why it took more time

Now, let’s understand why it took more time to return the result.

The .Where extension method has two overloads. One that implements IQueryable, which takes Expression parameter and the other that implements IEnumerable, which takes a Func.

• The IQueryable overload of Where

public static IQueryable Where (this IQueryable source, Expression<Func<TSource,int,bool>> predicate);

• The IEnumerable overload of Where

public static IEnumerable Where (this IEnumerable source, Func<TSource,int,bool> predicate);

Now, in our case, the predicate here is a Func. So, the second version of the .Where that implements IEnumerable will be used.

One important thing to note here is that Entity Framework will consider only IQueryable’s to translate into SQL. So, if we use the IEnumerable extension for the entities that will not be translated.

With that in mind, let’s examine our Query on the Employee entity.

Func<Employee, bool> dynamicFilter = p => p.JobTitle == "Design Engineer";
var results = EmployeeContext.Employee.Where(dynamicFilter);

If you can check the output of the operation you can see that we are getting correct results.

But, the fact is that our Func delegate is not translated into SQL. So, only the EmployeeContext.Employee is translated into appropriate SQL query and will be sent to SQL server.

That will fetch all the records of the employee table and load into memory and will perform an IEnumerable where filter on the whole collection to find the appropriate employee whose designation is “Design Engineer”.

If the Employee table is huge then the query will take more time to fetch the records.

Let’s see this in action with LINQPad.

For the purpose of this article, I’ve restored AdventureWorks2014 database on my machine.

Func<Employee, bool> funcPred = p => p.JobTitle == "Design Engineer";
var total = Employees.Where(funcPred).Count();

SELECT [t0].[BusinessEntityID], [t0].[NationalIDNumber], [t0].[LoginID], [t0].[OrganizationNode], [t0].[OrganizationLevel], [t0].[JobTitle], [t0].[BirthDate], [t0].[MaritalStatus], [t0].[Gender], [t0].[HireDate], [t0].[SalariedFlag], [t0].[VacationHours], [t0].[SickLeaveHours], [t0].[CurrentFlag], [t0].[rowguid] AS [Rowguid], [t0].[ModifiedDate]
FROM [HumanResources].[Employee] AS [t0]

Look at the SQL generated for the above code, it just pulled everything from the Employee table. And, once we get the entire table to in-memory, LINQ will perform the filter on the entire list of employees and returns the result.

The solution to this problem is to wrap the Func with Expression.

Solution: Go for Expression<Func>

Let’s wrap Expression around Func and see the results.

// With expression wrapped around func
Expression<Func<Employee, bool>> funcPred  = p => p.JobTitle == "Design Engineer";
var total = Employees.Where(funcPred).Count();

The sql generated for the above code is

-- SQL query generated for Expression in LINQPad
DECLARE @p0 NVarChar(1000) = 'Design Engineer'
SELECT COUNT(*) AS [value]
FROM [HumanResources].[Employee] AS [t0]
WHERE [t0].[JobTitle] = @p0

As you can see we have got the exact SQL query for the LINQ query we wrote. The above query doesn’t take much time as the query directly returns the result instead of returning everything in the tables.

Conclusion

If we mix these Func delegates in C# with the Entity framework entities then this would happen.

If you don’t understand the inner workings of the LINQ to SQL translation, you will end up querying the entire table in LINQ. More importantly, you will get the result but the query is slow.