Use of DapperExtensions to generate predicates with flagged enums

Question

I wish to generate a list of three predicates using DapperExtensions.Predicates class. I'm looking to achieve the generation of the following SQL's statement WHERE clause:

SELECT Field1, Field2, Field3 
FROM A JOIN B ON A.Field1 = B.Field1 
WHERE ([B].[TypeProtectionInfoAdd] & 128 = 0) 
AND ([B].[TypeProtectionId] & 2049 = 2049) 
AND ([B].[TypeProtectionId] & 1024 = 0)

The fields TypeProtectionInfoAdd and TypeProtectionId are matched to values of flagged enums (ProtectionInfoAddEnum and ProtectionEnum) in the code.

I tried this: I use the following method in the code to generate the predicates:

public string GetPredicateSql(IEnumerable<PlanDetails> planDetails)
{
var predicateGroup = new PredicateGroup
            {
                Predicates = new List<IPredicate>
                {
                    Predicates.Field<VIT9010_Plans>(x => x.TypeProtectionInfoAdd, Operator.Eq, planDetails.FirstOrDefault()?.VIT9010_Plans.TypeProtectionInfoAdd.Value & 128),
                    Predicates.Field<VIT9010_Plans>(x => x.TypeProtectionId, Operator.Eq, planDetails.FirstOrDefault()?.VIT9010_Plans.TypeProtectionId.Value & 2049),
                    Predicates.Field<VIT9010_Plans>(x => x.TypeProtectionId, Operator.Eq, planDetails.FirstOrDefault()?.VIT9010_Plans.TypeProtectionId.Value & 1024)
                },
                Operator = GroupOperator.And
            };
return GetSqlFromPredicate(predicateGroup);
}

I am expecting this:

SELECT Field1, Field2, Field3 
FROM A JOIN B ON A.Field1 = B.Field1 
WHERE ([B].[TypeProtectionInfoAdd] & 128 = 0) 
AND ([B].[TypeProtectionId] & 2049 = 2049) 
AND ([B].[TypeProtectionId] & 1024 = 0)

When I run this query in SQL Server, I get the required result.

But I get this, which when run in SQL Server returns no result:

SELECT Field1, Field2, Field3 
FROM A JOIN B ON A.Field1 = B.Field1 
WHERE ([B].[TypeProtectionInfoAdd] = 0) 
AND ([B].[TypeProtectionId] = 2049) 
AND ([B].[TypeProtectionId] = 0)

This is what makes me think that I need the predicates to be formatted correctly so I can get the equivalent SQL query. With this actual code, the query generated computes the bitwise operation and includes the result in the query directly (which is somewhat logical from what I wrote in the code). But I need to generate the SQL query with the WHERE clauses exactly as is in the preceding SQL query. Any idea, suggestions on how to format the predicates to get the correct result, please ?

Answer 1

The code you were using was constructing the & on the client-side, not within the SQL.

It seems that DapperExtensions provides this built-in using the BitEq operator (ie you don't need to construct the & as a separate PredicateGroup).

var predicateGroup = new PredicateGroup
{
    Predicates = new List<IPredicate>
    {
        Predicates.Field<VIT9010_Plans>(x => x.TypeProtectionInfoAdd, Operator.BitEq, 128, not: true),
        Predicates.Field<VIT9010_Plans>(x => x.TypeProtectionId, Operator.BitEq, 2049),
        Predicates.Field<VIT9010_Plans>(x => x.TypeProtectionId, Operator.BitEq, 1024, not: true)
    },
    Operator = GroupOperator.And
};