将谓词作为参数传递给Where子句时，EF SQL发生了更改 [英] EF SQL changed when passing predicate as Parameter to Where Clause

问题描述

EF为下面列出的两个类似语句生成了不同的SQL

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();`

生成的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"

我在多个具有不同Where条件的地方使用上述语句；将逻辑整合到一个地方，我将条件作为参数传递

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();
}

我将函数称为

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

生成的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]

如果您查看第二个SQL，这将非常令人震惊:它正在提取所有信息(列和行).它没有where子句，也没有选择所有列.

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.

从数据库返回结果后将应用where条件.

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

第二条语句的唯一区别是我将条件作为参数传递，而不是在where子句中具有条件.

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?

推荐答案

由于ReqFunc类型为Func<ApplicationRequest, bool>，因此您使用的是Enumerable扩展名，因此您的代码(Where，Select，FirstOrDefault )将在内存中执行.

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

要解决此问题，只需将ReqFunc更改为Expression<Func<ApplicationRequest, bool>>以使用Queryable扩展名:

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();
}

这篇关于将谓词作为参数传递给Where子句时，EF SQL发生了更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！