Can I use an extension method in Entity Framework SubQuery?

Question

I'm trying to consolidate logic for accessing different tables using Entity Framework. I created an extension method for pulling all registrations from my registration entity where the person is attending:

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 works great for queries like this:

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

But it doesn't 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.IEnumerable1[Registration] Attending(System.Collections.Generic.IEnumerable1[Registration])' method, and this method cannot be translated into a store expression.

Is there any way re-use that code in a subquery?

Answer 1

The main thing you're trying to achieve is reusing the predicate that defines the meaning of Attending. You can do that by storing the expression in a readonly variable that is available to whoever needs it in your application, for example in a 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(),
})

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