Func vs Manually expression performance in C# lambda

Question

Please take a look at these lines:

1. in this case I type where statement directly in method

public List<User> GetUsers()
{
    return _entity.Where(x => x.Id == 1).ToList();
}

Executed sql query is:

SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Username] AS [Username], 
    [Extent1].[Password] AS [Password], 
    [Extent1].[Email] AS [Email],
    [Extent2].[Id] AS [Id1]
    FROM  [dbo].[Account_Users] AS [Extent1]
    LEFT OUTER JOIN [dbo].[Account_Profiles] AS [Extent2] ON [Extent1].[Id] = [Extent2].[UserId]
    WHERE 1 = [Extent1].[Id]

2. in this case I used Func for generic where clause

public List<User> GetUsers(Func<User, bool> where)
{
    return _entity.Where(where).ToList();
}
var users = _acc.GetUsers(x => x.Id == 1);

Executed sql query is:

SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Username] AS [Username], 
    [Extent1].[Password] AS [Password], 
    [Extent1].[Email] AS [Email], 
    [Extent2].[Id] AS [Id1]
    FROM  [dbo].[Account_Users] AS [Extent1]
    LEFT OUTER JOIN [dbo].[Account_Profiles] AS [Extent2] ON [Extent1].[Id] = [Extent2].[UserId]

as you can see, in case 2 where clause which is WHERE 1 = [Extent1].[Id] is missing and therefore whole records are stored in memory. do you have any idea why where clause is not translated in sql query?
I want to use Func<t, bool> in .Where() so it would be generic and no need to create functions for each query.
is there any way to use .Where(Func<t, bool>) and also see the translated where clause in sql query?

score 10 · Accepted Answer · edited May 23 '17 at 12:25

10

If you want your lambda to be executed in SQL, you need to pass it as an Expression, not a Function:

public List<User> GetUsers(Expression<Func<User, bool>> where)
{
    return _entity.Where(where).ToList();
}
var users = _acc.GetUsers(x => x.Id == 1);

If you wonder what the difference is (after all, the lambda itself looks the same), take a look at this question.

edited May 23 '17 at 12:25

Community

1
1

answered Jul 12 '15 at 14:24

Glorfindel

21,988
13
81
109

that simple and I was wondering why my app is so slow... thanks – mhesabi Jul 12 '15 at 18:00

score 3 · Answer 2 · answered Jul 12 '15 at 14:24

Instead of

public List<User> GetUsers(Func<User, bool> where)

you should be using

public List<User> GetUsers(Expression<Func<User, bool>> where)

When you are using Expression Entity Framework is able to translate it to SQL correctly. On the other hand when you are using Func Entity framework doesn't know how to translate that to SQL so it is using in-memory processing.

Leonid Vasilev · Answer 3 · 2015-07-12T15:45:12.117

That is because in this two cases compiled code contains call to two different extension methods: Queryable.Where and Enumerable.Where respectively. Queryable class contains extension methods for processing data from different data sources, including external sources whereas Enumerable contains extension methods for in-memory objects processing. Queryable version of Where accept Expression rather than Func.

Lamda expression can be implicitly converted to both Expression and Func, so as other answers pointed out you just need to accept an Expression instance as your function argument.

Func vs Manually expression performance in C# lambda

3 Answers3