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 look like me, your toes curl as you repeat the logic of product selection. This picture is repeated elsewhere. At first I tried to replace it with the IEnumerable extension method, which, of course, does not work: Linq to Entities needs an expression for parsing and translation. 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 fine:

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

However, this will not compile because IGrouping does not contain a Where redefinition that takes 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(); 

Passing g in an IQueryable compilation, but then gives an "Internal error of the .NET Framework 1025 data provider."

Is there a way to wrap this logic in my own method?

Answer 1

This is a problem that can be solved with LINQKit. It allows you to call expressions from other expressions and embeds the called expression inside its caller. Unfortunately, it only supports a few very specific situations, so we need to adapt the expression generation method a bit.

Instead of passing the product name to the expression generation method, we will have it as 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 need to call the method before declaring the request:

 var predicate = Extensions.ContainingProductEx(); 

Now your request can 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() 

Answer 2

Instead of worrying about creating a function pointer / expression inside your request that you can reference (it may not be possible), why not just create a separate private method that takes an IEnumerable<Lead> string, returns a string and returns an int and specify a group of methods in your request? I think your confusion is related to trying to create an extension method in the collection instead of creating the method that is in the collection and the value you are looking for.

Sort of:

ProductA = GetLeadsForProduct(g, "productA")

 private int GetLeadsForProduct(IEnumerable<Lead> leads, string productType) { return leads.Count(l => l.Producten.Any(a => ((a.Name.Equals(productType, StringComparison.CurrentCultureIgnoreCase)) || (a.Parent.Name.Equals(productType, StringComparison.CurrentCultureIgnoreCase))))) }