Func委托导致LINQ to Entity收回整个表
本文关键字:Entity to LINQ Func | 更新日期: 2023-09-27 18:11:49
传递函数<>因为Where/Count过滤器导致LINQ收回整个表。这里有一个简单的例子。
pdx.Database.Log = strWriter1.Write;
totalCount = pdx.Principals.Count(x => x.PrincipalNumber.ToLower().Contains("42"));
查看日志,我看到
SELECT [GroupBy1].[A1] AS [C1] FROM ( SELECT COUNT(1) AS [A1]
FROM [Dealer].[Principal] AS [Extent1]
WHERE LOWER([Extent1].[PrincipalNumber]) LIKE N'%42%'
) AS [GroupBy1]
没有把整张桌子拉回来。很简单。现在,让我们将lambda赋值给一个Func<>
pdx.Database.Log = strWriter2.Write;
Func<Principal, bool> filter = (x => x.PrincipalNumber.ToLower().Contains("42"));
totalCount = pdx.Principals.Count(filter);
日志显示它正在拉下整张桌子。
SELECT
[Extent1].[PrincipalNumber] AS [PrincipalNumber],
[Extent1].[Id] AS [Id],
[Extent1].[CompanyName] AS [CompanyName],
...
[Extent1].[DistrictSalesManagerId] AS [DistrictSalesManagerId]
FROM [Dealer].[Principal] AS [Extent1]
这对表现来说相当糟糕。我有一些函数可以执行LINQ查询。我想将lambda过滤器传递给这些函数,这样我就可以对各种内容进行过滤,但显然我不能将lambdas传递为Func<>s因为这会影响表演。我的备选方案是什么?
我想做什么…
public IEnumerable<DealerInfo> GetMyPage(Func<Principal, bool> filter, int pageNumber, int pageSize, out int totalCount)
{
List<DealerInfo> dealers;
using (MyContext pdx = new MyContext())
{
totalCount = pdx.Principals.Count(filter);
// More LINQ stuff here, but UGH the performance...
}
}
您实际上需要将Expression<Func<TSrource,T>>
、Linq传递给实体。无法将Func<T>
转换为sql,请将签名更改为:
public IEnumerable<DealerInfo> GetMyPage(Expression<Func<Principal, bool>> filter, int pageNumber, int pageSize, out int totalCount)
{
List<DealerInfo> dealers;
using (MyContext pdx = new MyContext())
{
totalCount = pdx.Principals.Count(filter);
// More LINQ stuff here, but UGH the performance...
}
}
当您将Count
方法中的Func<T,TResult>>
作为参数传递时,它会调用内存集合中IEnumerable<T>
的Count方法扩展方法,从而导致整个表数据首先加载到内存中,然后在加载所有数据和内存时执行计数委托,并在内存中执行所提供的委托调用,如果可能的话,传递Expression<Func<T>>
作为参数将使其将语句转换为正确的sql,然后调用IQueryable<T>
的Count扩展方法,这样您就可以执行正确的查询并返回结果。