Using Where( Expression<Func<T, bool>> ) in IGrouping

Question

Consider the following Linq to Entities query:

return (from lead in db.Leads
    join postcodeEnProvincie in postcodeEnProvincies
    on lead.Postcode equals postcodeEnProvincie.Postcode
    where (lead.CreationDate >= range.StartDate) && (lead.CreationDate <= range.EndDate)
    group lead by postcodeEnProvincie.Provincie into g
    select new Web.Models.GroupedLeads() {
        GroupName = g.Key,
        HotLeads = g.Count(l => l.Type == Data.LeadType.Hot),
        Leads = g.Count(),
        PriorityLeads = g.Count(l => l.Type == Data.LeadType.Priority),
        Sales = g.Count(l => l.Sold),
        ProductA = g.Count(l => l.Producten.Any(a => ((a.Name.Equals("productA", StringComparison.CurrentCultureIgnoreCase)) || (a.Parent.Name.Equals("productA", StringComparison.CurrentCultureIgnoreCase))))),
        ProductB = g.Count(l => l.Producten.Any(a => ((a.Name.Equals("productB", StringComparison.CurrentCultureIgnoreCase)) || (a.Parent.Name.Equals("productB", StringComparison.CurrentCultureIgnoreCase))))),
        ProductC = g.Count(l => l.Producten.Any(a => ((a.Name.Equals("productC", StringComparison.CurrentCultureIgnoreCase)) || (a.Parent.Name.Equals("productC", StringComparison.CurrentCultureIgnoreCase))))),
        ProductC = g.Count(l => l.Producten.Any(a => ((a.Name.Equals("productD", StringComparison.CurrentCultureIgnoreCase)) || (a.Parent.Name.Equals("productD", StringComparison.CurrentCultureIgnoreCase)))))
}).ToList();

If you're anything like me, your toes curl at the repetition of the product selection logic. This pattern is repeated in another place as well. I first attempted to replace it by an extension method on IEnumerable, which of course does not work: Linq to Entities needs an Expression to parse and translate.
So I created this method:

    public static System.Linq.Expressions.Expression<Func<Data.Lead, bool>> ContainingProductEx(string productName)
    {
        var ignoreCase = StringComparison.CurrentCultureIgnoreCase;

        return (Data.Lead lead) =>
            lead.Producten.Any(
                (product =>
                    product.Name.Equals(productName, ignoreCase) ||
                    product.Parent.Name.Equals(productName, ignoreCase)
                ));
    }

The following selection now works perfectly fine:

var test = db.Leads.Where(Extensions.ContainingProductEx("productA")).ToList();

However, this won't compile, because IGrouping does not contain an override of Where that accepts an Expression:

return (from lead in db.Leads
        join postcodeEnProvincie in postcodeEnProvincies
        on lead.Postcode equals postcodeEnProvincie.Postcode
        where (lead.CreationDate >= range.StartDate) && (lead.CreationDate <= range.EndDate)
        group lead by postcodeEnProvincie.Provincie into g
        select new Web.Models.GroupedLeads()
        {
            GroupName = g.Key,
            HotLeads = g
                .Where(l => l.Type == Data.LeadType.Hot)
                .Count(),
            Leads = g.Count(),
            PriorityLeads = g
                .Where(l => l.Type == Data.LeadType.Priority)
                .Count(),
            Sales = g
                .Where(l => l.Sold)
                .Count(),
            ProductA = g
                .Where(Extensions.ContainingProductEx("productA"))
                .Count(),
            ProductB = g
                .Where(Extensions.ContainingProductEx("productB"))
                .Count(),
            ProductC = g
                .Where(Extensions.ContainingProductEx("productC"))
                .Count(),
            ProductD = g
                .Where(Extensions.ContainingProductEx("productD"))
                .Count()
        }).ToList();

Casting g to IQueryable compiles, but then yields a "Internal .NET Framework Data Provider error 1025.".

Is there any way to wrap this logic in its own method?

Answer 1

This is a problem that can be solved using LINQKit. It allows expressions to be invoked from within other expressions, and it will inline the invoked expression within its caller. Sadly, it only supports a handful of very specific situations, so we'll need to adapt your expression generating method a bit.

Rather than passing the product name to the expression generating method, we'll have it be a parameter of the returned expression:

public static Expression<Func<Data.Lead, string, bool>> ContainingProductEx()
{
    var ignoreCase = StringComparison.CurrentCultureIgnoreCase;

    return (lead, productName) =>
        lead.Producten.Any(
            (product =>
                product.Name.Equals(productName, ignoreCase) ||
                product.Parent.Name.Equals(productName, ignoreCase)
            ));
}

Next we'll need to call the method before declaring the query:

var predicate = Extensions.ContainingProductEx();

Your query can can now be written as:

from lead in db.Leads.AsExpandable()
//...
    ProductA = g
        .Where(lead => predicate.Invoke(lead, "productA"))
        .Count(),
    ProductB = g
        .Where(lead => predicate.Invoke(lead, "productB"))
        .Count(),
    ProductC = g
        .Where(lead => predicate.Invoke(lead, "productC"))
        .Count(),
    ProductD = g
        .Where(lead => predicate.Invoke(lead, "productD"))
        .Count()