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?

Answer 1

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;
}