How to implement “NOT IN” clause with Dapper Extensions Predicate?

Question

I found how to implement IN clause using Dapper Extensions here.

Now, I want to implement NOT IN clause.
So, I am expecting SQL query something like below:

SELECT * FROM MyTable
WHERE MyField NOT IN (param1, param2)

But I could not find anything about NOT IN or NOT clause in Dapper Extensions.

How can I implement NOT IN clause with Dapper Extensions Predicate?

Answer 1

Please refer to this answer (linked in question as well) to understand how to implement IN clause.

To turn the IN clause as mentioned above to NOT IN clause, use the last bool not parameter.
This is optional parameter and default value for it is false.
That is why; even though so obvious, it is bit hidden and hence undiscovered.
Documentation does not mention it explicitly either.

Below are the definitions of each predicate defined in Dapper Extensions source code:

public static class Predicates
{
    public static IBetweenPredicate Between<T>(Expression<Func<T, object>> expression, BetweenValues values, bool not = false) where T : class;
    public static IExistsPredicate Exists<TSub>(IPredicate predicate, bool not = false) where TSub : class;
    public static IFieldPredicate Field<T>(Expression<Func<T, object>> expression, Operator op, object value, bool not = false) where T : class;
    public static IPropertyPredicate Property<T, T2>(Expression<Func<T, object>> expression, Operator op, Expression<Func<T2, object>> expression2, bool not = false)
        where T : class
        where T2 : class;
}

Sample code is as below:

var predicate = Predicates.Field<Customer>
                (f => f.CustomerID, Operator.Eq, listOfIDs, true);

Observe the value true for last parameter in above code. The listOfIDs is an IEnumerable of your data type.

Please refer to this for more source code.