Linq Expression with multiple nested properties

Question

I have read through the question, and answer, Dynamic linq expression tree with nested properties. It does seem to be very similar, though a lack of understanding with Expressions is resulting in me not being able to translate the answer to my own scenario.

Given a class structure that looks a little like this:

public class Parent
{
    public Parent()
    {
        ParentField = new HashSet<ParentField>();
    }
    public int ParentId { get; set; }
    public ICollection<ParentField> ParentField { get; set; }
}

public class ParentField
{
    public int ParentField { get; set; }
    public Field Field { get; set; }
    public string Value {get;set;}
}

public class Field
{
    public int FieldId { get; set; }
    public string Name { get; set; }
}

I am trying to build up a query that would be represented by this:

var query = _unitOfWork.ParentRepository.Queryable().Include("ParentField.Field");
query = query.Where(i =>
    (
        i.ParentField.Any(pField => pField.Field.Name.Equals("anId", StringComparison.OrdinalIgnoreCase)) &&
                i.ParentField.Any(pField => 
                // There may be multiple values to search for, so require the OR between each value
                pField.Value.Equals("10", StringComparison.OrdinalIgnoreCase) || pField.Value.Equals("20", StringComparison.OrdinalIgnoreCase))
    )
    // There may be multiple Names to search for, so require the AND between each Any
    &&
    (
        i.ParentField.Any(pField => pField.Field.Name.Equals("anotherId", StringComparison.OrdinalIgnoreCase)) &&
                i.ParentField.Any(pField => 
                pField.Value.Equals("50", StringComparison.OrdinalIgnoreCase) || pField.Value.Equals("60", StringComparison.OrdinalIgnoreCase))
    ));

The important parts to note is that there can be many "Field.Name"'s to search for, as well as multiple "Values" within each of the groups.

I'm not able to give much of an example of what I have tried so far, given I am not sure where to actually start.

Any pointers would be fantastic.

Answer 1

In this particular case there is no need to build dynamic expression predicate. The && can be achieved by chaining multiple Where, and || by putting the values into IEnumerable<string> and using Enumerable.Contains.

For single name / values filter it would be something like this:

var name = "anId".ToLower();
var values = new List<string> { "10", "20" }.Select(v => v.ToLower());
query = query.Where(p => p.ParentField.Any(
    pf => pf.Field.Name == name && values.Contains(pf.Value.ToLower())));

And for multiple key / values pairs:

var filters = new Dictionary<string, List<string>>
{
    { "anId", new List<string> { "10", "20" } },
    { "anotherId", new List<string> { "50", "60" } },
};

foreach (var entry in filters)
{
    var name = entry.Key.ToLower();
    var values = entry.Value.Select(v => v.ToLower());
    query = query.Where(p => p.ParentField.Any(
        pf => pf.Field.Name == name && values.Contains(pf.Value.ToLower())));
}

@geraphl's answer presents the same idea, but w/o taking into account EF query provider specific requirements (no dictionary methods, only primitive value list Contains, no Equals with StringComparison.OrdinalIgnoreCase usage, but == and ToLower etc.)

Answer 2

I am not sure but i think you are searching for something like this.

First you have to put your searched values in a data-type that is capable of doing what you want, which is in this case a dictionary:

var nameValues = new Dictionary<string, string[]>(StringComparer.OrdinalIgnoreCase)
        {
            {  "anId" , new string[] {"10", "20"} },
            {  "anotherId" , new string[] {"50", "60"} },
        };

Then you first check if the name is in the dictionary and if so, you also check if one of the values also listed is in the list you search.

var query = _unitOfWork.ParentRepository.Queryable().Include("ParentField.Field");
query = query.Where(i =>
    (
        i.ParentField.Any(pFieldOuter => nameValues.ContainsKey(pFieldOuter.Field.Name) ?
        i.ParentField.Any(pFieldInner => nameValues[pFieldOuter.Field.Name].Contains(pFieldInner.Value, StringComparer.OrdinalIgnoreCase))
        : false 
    )
));

But if you want, that all your searched names and values are included you have to do this.

var names = new HashSet<string>(StringComparer.OrdinalIgnoreCase) { "anId", "anotherId" };
var values = new List<List<string>>() {
    new List<string> { "10", "20" },
    new List<string> { "50", "60" }
};

var query = _unitOfWork.ParentRepository.Queryable().Include("ParentField.Field");
query = query.Where(i =>
    (
        names.All(n => i.ParentField.Any(pField => pField.Name.Equals(n, StringComparison.OrdinalIgnoreCase))) &&
        values.All(l => l.Any(v => i.ParentField.Any(pField => pField.Value.Equals(v, StringComparison.OrdinalIgnoreCase))))
    ));

Even if i am sure this is not the most effective way to do is, it may be a good hint for you.