Grouping Lambda Expressions by Operators and Using Them With DapperExtensions' PredicateGroups

c# dapper extension-methods

Question

Pursuant to my previous question: Pulling Apart Expression<Func<T, object>> - I am trying to make it a bit more advanced. Currently, I can do this:

var matchingPeople = Connection.Get<Person>(p => p.MarketId == marketId);

which will be converted into a DapperExtensions FieldPredicate:

// Assume I've successfully parsed p => p.MarketId == marketId into its constituent parts:
// left = p => p.MarketId, theOperator = Operator.Eq, right = marketId
Predicates.Field(left, theOperator, right);

I now want to be able to do this:

var matchingPeople = Connection.Get<Person>(p => p.MarketId == marketId && p.FirstName == "John" || p.FirstName == "Jack");

and generate SQL that looks something like this:

DECLARE @MarketId INT = 3
DECLARE @FirstName01 VARCHAR(MAX) = 'John'
DECLARE @FirstName02 VARCHAR(MAX) = 'Jack'

SELECT *
FROM Person
WHERE MarketId = @MarketId AND (FirstName = @FirstName01 OR FirstName = @FirstName02)

by using DapperExtensions Compound Predicate Groups:

// ** This is the code I am trying to dynamically create based on the lambda that is passed in **

var predicateGroupAnd = new PredicateGroup {Operator = GroupOperator.And, Predicates = new List<IPredicate>()};
// I already have the code to determine: left = p => p.MarketId, theOperator = Operator.Eq, right = marketId
predicateGroupAnd.Predicates.Add(Predicates.Field(left, Operator.Eq, right));

var predicateGroupOr = new PredicateGroup {Operator = GroupOperator.Or, Predicates = new List<IPredicate>()};
// I already have the code to determine: left = p => p.FirstName, theOperator = Operator.Eq, right = "John"
predicateGroupAnd.Predicates.Add(Predicates.Field(left, Operator.Eq, right));
// I already have the code to determine: left = p => p.FirstName, theOperator = Operator.Eq, right = "Jack"
predicateGroupOr.Predicates.Add(Predicates.Field(left, Operator.Eq, right));

var predicateGroupAll = new PredicateGroup // This is what will be passed to DapperExtensions' GetList<T> method
    {
        Operator = GroupOperator.And, // How do I set this correctly?
        Predicates = new List<IPredicate> {predicateGroupAnd, predicateGroupOr}
    };

My problem seems to be around the way expression trees are parsed. Assume we have the lambda expression:

p => p.MarketId == marketId && p.FirstName == "John" || p.FirstName == "Jack"

I can cast this to a BinaryExpression. If I use BinaryExpression.Left, I get

p.MarketId == marketId && p.FirstName == "John"

and BinaryExpression.Right yields:

p.FirstName == "Jack"

Also, the NodeType of the overall BinaryExpression seems to be set to the last conditional operator of the lambda, i.e. ExpressionType.OrElse

I feel like I need to use recursion and traverse the lambda expression from right to left, but I haven't been able to create the compound group predicates that I want. Specifically, how do I group the AND lambdas together, and the OR lambdas together? Thanks!

Popular Answer

Your example lambda,

p => p.MarketId == marketId && p.FirstName == "John" || p.FirstName == "Jack"

is the equivilent of

p => (p.MarketId == marketId && p.FirstName == "John") || p.FirstName == "Jack"

because && has a higher precendence than ||.

Because of this you'll get a tree with the && at the "bottom", (as it needs to be calculated first) then the || on top:

                         ||
                        /  \
                       &&  Firstname == "Jack"
                      /  \
p.MarketId == marketId    p.FirstName == "John"

Once you understand operator precedence the above makes sense. If you want an alternative you can just use brackets to force the || to be evaluated first (making it end up at the bottom of the expression tree).

p => p.MarketId == marketId && (p.FirstName == "John" || p.FirstName == "Jack")

Your general problem is that you're approaching this slightly wrong. Currently you are trying to create 2 groups, one for ORs and one for ANDs. This might work in this case, but not in the general one, for example, what would you do for this: (a && b) || (c && d)?

I think what should happen is that each and and each or should translate into its own predicate group. See the "Multiple Compound Predicates (Predicate Group)" section of your linked article. You'd simply replace BinaryExpression with a predicate group.

In your example you have (a && b) || c with the || at the top. What you want to end up with is each time you have an operator you want to create a predicate group, with the left and right as the list of expressions. In your logic to convert a binaryexpression to a predicate group you would first use that same function to convert the left and the right into a predicate group

i.e. your code sees the ||.
it creates a predicate group ready to add the expressions to its list
it chooses left first (doesn't matter).
ok, left is another binary expression, so it calls itself to get a predicategroup that your target library understands
so that recursion is working on only a && b. It chooses left first, sees a simple predicate, a which means it can simply add it to the predicate group, it does the same for the right
then we're back up to the original call to the function which now has a predicate group with the lower expression on the left converted to a different predicate group and added. it goes down the right now, which is simple an individual predicate and so it can add it to its list.

Okay, so if I had something crazy like: a && b || c || d && e

Ok, taking into account higher precedence we get the following: ((a && b) || c) || (d && e) note that i'm not 100% sure about either the bracket would put c with the first && or the last, but it doesn't matter, the logic is the same When visualising the tree, start with the innermost brackets. They are the "leaves" then work outwards, using the brackets to work up the tree, finally arriving at the root node, which in our case is the || to the right of c:

       ||
     /    \
    ||     &&
   /  \   /  \
  &&   c d   e
 /  \
a    b



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why