Can I use the extension method in the Entity Framework SubQuery?

Question

I am trying to consolidate the logic for accessing various tables using the Entity Framework. I created an extension method to pull all registrations from my registration object where the person is located:

public static IEnumerable<Registration> Attending(this IEnumerable<Registration> registrations) { return registrations.Where(r => r.Status == RegistrationStatus.Paid || r.Status == RegistrationStatus.Assigned || r.Status == RegistrationStatus.Completed); } 

This is great for queries like this:

 var attendees = db.Registrations.Attending().ToList(); 

But it does not work when used in a subquery:

 ProductTotals = db.Products.Where(p => p.EventID == ev.Id).Select(p => new ProductSummaryViewModel { ProductID = p.ProductID, ProductName = p.Name, Registrations = p.Registrations.Attending().Count(), }).ToList(); 

I get the following error:

LINQ to Entities does not recognize the method 'System.Collections.Generic.IEnumerable 1[Registration] Attending(System.Collections.Generic.IEnumerable 1 [Registration])' method, and this method cannot be translated into a storage expression.

Is there a way to reuse this code in a subquery?

Answer 1

The main thing you are trying to achieve is reusing the predicate that defines the value of Attending . This can be done by storing the expression in the readonly variable, accessible to those who need it, for example, in the static class ExpressionConstants .

 public static readonly Expression<Func<Registration, bool>> IsAttending = r => r.Status == RegistrationStatus.Paid || r.Status == RegistrationStatus.Assigned || r.Status == RegistrationStatus.Completed; 

Then you can do

 var attendees = db.Registrations.Where(ExpressionConstants.IsAttending).ToList(); 

And used in the subquery:

 ProductTotals = db.Products.Where(p => p.EventID == ev.Id).Select(p => new ProductSummaryViewModel { ProductID = p.ProductID, ProductName = p.Name, Registrations = p.Registrations.AsQueryable() // AsQueryable() .Where(ExpressionConstants.IsAttending).Count(), }) 

AsQueryable() necessary because p.Registrations is probably an ICollection .