Expression Trees: Filtered count on navigation property

Question

I'm making a dynamic report builder that allows the user to pick fields from predefined classes (that map to database tables through Entity Framework) as filters for their data. To build my LINQ to Entities query, I'm using Expression Trees due to the dynamic nature of the queries. I have it working for pretty much all non-custom scenarios but am really struggling to make it work for a few custom scenarios.

An abbreviated version of my models for one of my custom queries looks like this:

public class Attendee {
    public int ID { get; set; }
    public DateTime? CancelledOn { get; set; }

    [ForeignKey("Event")]
    public int Event_ID { get; set; }
    public virtual Event Event { get; set; }        
}

public class Event {
    public int ID { get; set; }
    public virtual ICollection<Attendee> Attendees { get; set; }        
}

An example query that a user wants to run is to filter to only show Events that have more than 10 Attendees that haven't cancelled. If I were writing this in a normal IQueryable query, I would write this as

db.Event.Where(s => s.Attendees.Count(a => a.CancelledOn == null) > 10);

With the Expression Tree framework I have setup, I can handle the "> 10" part already, but I cannot figure out how to dynamically generate the "s.Attendees.Count(a => a.CancelledOn == null)" part. I've read SO posts about doing the Count or a Sum on a top level property, but I've not been able to hack any of those solutions to work for a filtered navigation property. Example post: Dynamic LINQ, Select function, works on Enumerable, but not Queryable

The screenshot below is an example of a different filter built out with Expression Trees so you can see an example of what I have working. "pe" is the ParameterExpression for the Type that is being passed in, "Event". "expression" is what I'm trying to create and evaluate. http://grab.by/RoYm

The LINQ query the above is running is

db.Event.Where(s=> s.StartDate >= '1/1/2016 12:00 am')

Any help or direction on this would be greatly appreciated and please let me know if I need to include any other code snippets.

Answer 1

Not sure what are the input parameters for the method you are seeking, but the following should give you a starting point. The essential part is to build a method call to Enumerable.Count(predicate) method.

static Expression<Func<TSource, bool>> MakeCountPredicate<TSource>(string collectionName, string itemName, ExpressionType itemComparison, string itemValue, ExpressionType countComparison, int countValue)
{
    var source = Expression.Parameter(typeof(TSource), "s");
    var collection = Expression.Property(source, collectionName);
    var itemType = collection.Type.GetInterfaces()
        .Single(i => i.IsGenericType && i.GetGenericTypeDefinition() == typeof(IEnumerable<>))
        .GetGenericArguments()[0];
    var item = Expression.Parameter(itemType, "e");
    var itemProperty = Expression.Property(item, itemName);
    var itemPredicate = Expression.Lambda(
        Expression.MakeBinary(itemComparison, itemProperty, Expression.Constant(
            string.IsNullOrEmpty(itemValue) || itemValue.Equals("null", StringComparison.OrdinalIgnoreCase) ? null :
            Convert.ChangeType(itemValue, itemProperty.Type))),
        item);
    var itemCount = Expression.Call(
        typeof(Enumerable), "Count", new[] { itemType },
        collection, itemPredicate);
    var predicate = Expression.Lambda<Func<TSource, bool>>(
        Expression.MakeBinary(countComparison, itemCount, Expression.Constant(countValue)),
        source);
    return predicate;
}

so the sample predicate expression

Expression<Func<Event, bool>> predicate =
    s => s.Attendees.Count(a => a.CancelledOn == null) > 10

can be build dynamically like this

var predicate = MakeCountPredicate<Event>("Attendees", 
    "CancelledOn", ExpressionType.Equal, "null", ExpressionType.GreaterThan, 10);