Func<t, bool> 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?

Answer 1

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.

Answer 2

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.