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.
This is the LINQ query that we are interested in.
public static void Main(string args)
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.
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.
Now, let’s understand why it took more time to return the result.
.Where extension method has two overloads. One that implements
IQueryable, which takes
Expression parameter and the other that implements
IEnumerable, which takes a
- The IQueryable overload of Where
public static IQueryable<TSource> Where<TSource> (this IQueryable<TSource> source, Expression<Func<TSource,int,bool>> predicate);
- The IEnumerable overload of Where
public static IEnumerable<TSource> Where<TSource> (this IEnumerable<TSource> 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";
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”.
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";
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]
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.
Let’s wrap Expression around Func and see the results.
Expression<Func<Employee, bool>> funcPred = p => p.JobTitle == "Design Engineer";
The sql generated for the above code is
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.
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.