EF SQL changed when passing predicate as Parameter to Where Clause

Question

EF is generating different SQL for the for two similar statements listed below

var test = dbcontext.Persons.GetAll()
                            .Where(c => c.PersonID == 2)
                            .Select(c => c.PersonName)
                            .FirstOrDefault();`

Generated SQL:

SELECT 
    [Limit1].[PersonName ] AS [PersonName ]
FROM 
    (SELECT TOP (1)
         [Extent1].[PersonName ] AS [PersonName ]
     FROM 
         [dbo].[ApplicationRequest] AS [Extent1]
     WHERE 
         [Extent1].[PersonID ] = @p__linq__0) AS [Limit1]',N'@p__linq__0 uniqueidentifier',@p__linq__0= "2"

I am using the above statements at multiple places with different Where condition; to consolidate logic in one place I am passing the condition as a parameter

Public Void PassPredicate(Func<ApplicationRequest, bool> ReqFunc)
{
    var test = dbcontext.Persons.GetAll()
                                .Where(ReqFunc)
                                .Select(c => c.PersonName)
                                .FirstOrDefault();
}

I am calling the function as

PassPredicate(c => c.PersonID == 2);

Generated SQL:

SELECT 
    [Extent1].[PersonID] AS [PersonID], 
    [Extent1].[PersonName ] AS [PersonName ], 
    [Extent1].[DOB] AS [Dob], 
    [Extent1].[Height] AS [Height],
    [Extent1].[BirthCity] AS [BirthCity], 
    [Extent1].[Country] AS [Country],
FROM 
    [dbo].[Person] AS [Extent1]

If you look at the second SQL, it is quite alarming: it is pulling all info (columns and rows). It doesn't have where clause and selecting all columns.

The where condition is being applied after the results were returned from DB.

The only difference in the second statement is I am passing condition as parameter instead of having condition inside where clause.

Can anyone explain why the difference?

Answer 1

Since ReqFunc type is Func<ApplicationRequest, bool> you're using Enumerable extensions, so your code (Where, Select, FirstOrDefault) will be executed in-memory.

To fix this, just change ReqFunc to Expression<Func<ApplicationRequest, bool>> to use Queryable extensions:

Public Void PassPredicate(Expression<Func<ApplicationRequest, bool>> ReqFunc)
{
    var test = dbcontext.Persons.GetAll().Where(ReqFunc).Select(c => c.PersonName).FirstOrDefault();
}