mhenry1384 mhenry1384 - 2 months ago 10
C# Question

Func Delegates cause LINQ-to-Entities to pull back the entire table

Passing a Func<> as a Where/Count filter causes LINQ to pull back the entire table. Here's a simple example.

pdx.Database.Log = strWriter1.Write;
totalCount = pdx.Principals.Count(x => x.PrincipalNumber.ToLower().Contains("42"));


Looking at the log I see

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]


Did not pull back the full table. Simple enough. Now let's assign that lambda to a Func<>

pdx.Database.Log = strWriter2.Write;
Func<Principal, bool> filter = (x => x.PrincipalNumber.ToLower().Contains("42"));
totalCount = pdx.Principals.Count(filter);


The log shows it's pulling down the entire table.

SELECT
[Extent1].[PrincipalNumber] AS [PrincipalNumber],
[Extent1].[Id] AS [Id],
[Extent1].[CompanyName] AS [CompanyName],
...
[Extent1].[DistrictSalesManagerId] AS [DistrictSalesManagerId]
FROM [Dealer].[Principal] AS [Extent1]


That's pretty bad for performance. I have functions that do LINQ queries. I want to pass lambda filters to these functions so I can filter on various things, but apparently I can't pass lambdas as Func<>s because it will kill the performance. What are my alternatives?

What I want to do...

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...
}
}

Answer

You actually need to pass Expression<Func<TSrource,T>> , Linq to Entities cannot translate Func<T> to sql, change the signatures to be like:

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...
    }
}

When you pass Func<T,TResult>> in the Count method as argument, it would call Count method extension method of IEnumerable<T> which is in memory collection, so that is causing the whole table data to be loaded in to memory first and the count delegate then gets executed when all data is loaded and memory and executes the provided delegate call in memory , while passing Expression<Func<T>> as argument will make it translate the statement to proper sql if possible and then will make call to Count extension method of IQueryable<T> so you will have the correct query executing and result back.