How can I build a LINQ predicate/dynamic.LINQ query based off grid filtering when the grid properties don't have the entity properties?

Question

I have a grid passing me filters. So I may have an object like:

var filter = new Filter(){
   Member = "Titles",
   Operator = Filter.Operators.IsEqualTo,
   Value = "Developer"
};

Then I need to take this and extend an IQueryable so to do that, I use dynamic.LINQ and have a method to apply these filters:

private IQueryable<TReportClass> ApplyFilter(ReportFilter filter, IQueryable<TReportClass> baseQuery)
    {
        switch (filter.Operator)
        {
            case ReportFilter.Operators.Contains:
                baseQuery = baseQuery.Where(string.Format("{0}.Contains(@0)", filter.Member), filter.Value);
                break;
            case ReportFilter.Operators.DoesNotContain:
                baseQuery = baseQuery.Where(string.Format("!{0}.Contains(@0)", filter.Member), filter.Value);
                break;
            case ReportFilter.Operators.IsEqualTo:
                baseQuery = baseQuery.Where(string.Format("{0} = @0", filter.Member), filter.Value);
                break;
            case ReportFilter.Operators.IsNotEqualTo:
                baseQuery = baseQuery.Where(string.Format("{0} != @0", filter.Member), filter.Value);
                break;
            case ReportFilter.Operators.StartsWith:
                baseQuery = baseQuery.Where(string.Format("{0}.StartsWith(@0)", filter.Member), filter.Value);
                break;
            case ReportFilter.Operators.EndsWith:
                baseQuery = baseQuery.Where(string.Format("{0}.EndsWith(@0)", filter.Member), filter.Value);
                break;
            case ReportFilter.Operators.IsNull:
                baseQuery = baseQuery.Where(string.Format("{0} = NULL", filter.Member));
                break;
            case ReportFilter.Operators.IsNotNull:
                baseQuery = baseQuery.Where(string.Format("{0} != NULL", filter.Member));
                break;
            case ReportFilter.Operators.IsEmpty:
                baseQuery = baseQuery.Where(string.Format("string.IsNullOrEmpty({0})", filter.Member));
                break;
            case ReportFilter.Operators.IsNotEmpty:
                baseQuery = baseQuery.Where(string.Format("!string.IsNullOrEmpty({0})", filter.Member));
                break;
        }

        return baseQuery;
    }

However this works only for non-collections. How can I get it to work with collections? If I have this model:

public class UserReport : Entity
{
    public string Name { get; set; }
    public string Email { get; set; }
    public List<string> Titles { get; set; }
}

And this query as a base:

IQueryable<UserReport> baseQuery = MyDbContext.DbSet<User>.Select(user => new UserReport
        {
            Id = user.Id,
            Name = user.FirstName + " " + user.LastName,
            Email = user.Email,
            Titles = user.Positions.Select(apptment => apptment.Title).ToList()
        })

So I can call like:

IQueryable<UserReport> filteredQuery = ApplyFilters(filters, baseQuery);

How do I transform the above filter to translate into a LINQ like:

baseQuery.Where(userReport => userReport.Titles.Any(title => title == "Developer")

Can that be done with dynamic LINQ? Or do I need to build my own predicate? If so, how do I do that?

score 2 · Accepted Answer · answered Oct 07 '16 at 08:20

It's possible with both System.Linq.Dynamic and System.Linq.Expressions.

Here is the solution with System.Linq.Dynamic to keep it close to your current code. All you need is to identify if the member is collection and use the following pattern for the dynamic criteria:

Collection: {PropertyName}.Any(it{condition})
Object: {PropertyName}{condition}

And the implementation could be like this (basically replacing string.Format with Func<string, string, string>):

private IQueryable<TReportClass> ApplyFilter(ReportFilter filter, IQueryable<TReportClass> baseQuery)
{
    var property = typeof(TReportClass).GetProperty(filterMember);
    bool isCollection = property.Type != typeof(string) &&
        && typeof(IEnumerable).IsAssignableFrom(property.Type);
    Func<string, string, string> condtion;
    if (isCollection)
        condition = (format, member) => string.Format("{0}.Any({1})", member, string.Format(format, "it"));
    else
        condition = (format, member) => string.Format(format, member);
    switch (filter.Operator)
    {
        case ReportFilter.Operators.Contains:
            baseQuery = baseQuery.Where(condition("{0}.Contains(@0)", filter.Member), filter.Value);
            break;
        case ReportFilter.Operators.DoesNotContain:
            baseQuery = baseQuery.Where(condition("!{0}.Contains(@0)", filter.Member), filter.Value);
            break;
        case ReportFilter.Operators.IsEqualTo:
            baseQuery = baseQuery.Where(condition("{0} = @0", filter.Member), filter.Value);
            break;
        case ReportFilter.Operators.IsNotEqualTo:
            baseQuery = baseQuery.Where(condition("{0} != @0", filter.Member), filter.Value);
            break;
        case ReportFilter.Operators.StartsWith:
            baseQuery = baseQuery.Where(condition("{0}.StartsWith(@0)", filter.Member), filter.Value);
            break;
        case ReportFilter.Operators.EndsWith:
            baseQuery = baseQuery.Where(condition("{0}.EndsWith(@0)", filter.Member), filter.Value);
            break;
        case ReportFilter.Operators.IsNull:
            baseQuery = baseQuery.Where(condition("{0} = NULL", filter.Member));
            break;
        case ReportFilter.Operators.IsNotNull:
            baseQuery = baseQuery.Where(condition("{0} != NULL", filter.Member));
            break;
        case ReportFilter.Operators.IsEmpty:
            baseQuery = baseQuery.Where(condition("string.IsNullOrEmpty({0})", filter.Member));
            break;
        case ReportFilter.Operators.IsNotEmpty:
            baseQuery = baseQuery.Where(condition("!string.IsNullOrEmpty({0})", filter.Member));
            break;
    }

    return baseQuery;
}

Thanks, would it be more performant to only use reflection once outside of ApplyFilter and pass ApplyFilter all the collection property names instead of using reflection on each ApplyFilter call? Also I am curious as to why the reflection is first checking if property is not of `typeof(string)` and it's assignable from `typeof(IEnumerable)`? Couldn't I just have one statement that checks if `typeof(IEnumerable).IsAssignableFrom(property.Type)` ? — SventoryMang, Oct 07 '16 at 17:57
(1) In this case the affect of reflection is negligible. It's not something that is executed per each element of the result set. Also building expressions trees with `System.Linq.Expressions` classes does a lot of reflection anyway - how do you think Dynamic LINQ processes the passed string. (2) If you support only `string` lists, it will be sufficient. The above is more general and works for any list with values. Of course some of your filter operators are defined only for strings, but others like `Is(Not)EqualTo`, `Is(Not)Null` can be used for `int`s, `DateTime`s etc. — Ivan Stoev, Oct 07 '16 at 18:20
Anyway, the above is just a sample implementation, you could adjust it for your needs :) — Ivan Stoev, Oct 07 '16 at 18:23
Oh I didn't think about that, interesting. Good point about other types, I hadn't even considered that. All these filters logic only work if the column is of type string then right? I do use some ints, bools, and Datetimes in my grids as well. I couldn't apply something like Contains to a bool. However, I think my grid filters automatically know that, It would never try and pass a filter for a bool where the operator was set to contains. — SventoryMang, Oct 07 '16 at 18:23
It depends what the `filter.Value` contains. If it's a string, then yes, the filters would work only for string properties. But they can be made to work with other types by using `var value = Convert.ChangeType(filter.Value, property.Type)` for instance. — Ivan Stoev, Oct 07 '16 at 18:29

How can I build a LINQ predicate/dynamic.LINQ query based off grid filtering when the grid properties don't have the entity properties?

1 Answers1