Where comparison from array

Question

I have an sql table with the following structure:

Id-------|-------Attr1-------|-------Attr2------|------Attr3------|

1--------|--------true-------|------false-------|------false------|

2--------|--------true-------|------true--------|-------true-------|

And so on with different combinations of true/false.

I am trying to construct a Linq query that can pull the correct Id given a known combination of true/false.

For example, I could use the following:

public int GetTypeId(string[] arr)
    {
        //arr[0] is true, arr[1] is false
        return _context.Types
            .FirstOrDefault(x => x.Attr1 == arr[0] && x.Attr2 ==arr[1]).Id;
    }

And that would yield an Id of 1.

Now my question is how to properly make this a dynamic function where there are 10 attributes columns, and I can input an array with an arbitrary number of elements to make the where comparison (say Attr1 and Attr2 the first time, then Attr3 and Attr4 and Attr5 the next).

Is there an efficient way to do this? My biggest issue is that the .FirstOrDefault() function does not accept a concatenated string.

you probably need to use reflection here, something like https://stackoverflow.com/questions/737151/how-to-get-the-list-of-properties-of-a-class and then compare it with array values. However, I would rather refactor out compare code inside affected classes, this is more robust and maintainable way in my opinion. — Traveler, Feb 05 '18 at 01:38
You'd also need a step in the middle to parse string to boolean, otherwise you cannot compare string 'true' and 'false' to bool values. — Keyur PATEL, Feb 05 '18 at 02:03

Slava Utesinov · Answer 1 · 2018-02-05T12:28:49.430

You should dynamically create Expression predicate:

public int GetTypeId(string[] arr, int start = 1)
{
    var arg = Expression.Parameter(typeof(TypeClass), "x");            
    Expression andExpr = null;
    for(var i = 0; i < arr.Length; i++)
    {
        var exprProp = Expression.Property(arg, "Attr" + (i + start));
        var exprVal = Expression.Constant(arr[i]);
        var exprEq = Expression.Equal(exprProp, exprVal);
        andExpr = andExpr == null ? exprEq : Expression.AndAlso(andExpr, exprEq);
    }                        
    var predicate = Expression.Lambda<Func<TypeClass, bool>>(andExpr, arg);

    return _context.Types.FirstOrDefault(predicate).Id;
}

If you want to start comparison not from Attr1(default behavior), but from Attr3 you should pass 3 as start argument.

Where comparison from array

1 Answers1