How to implement "IN" clause with Dapper Extensions Predicate?

Question

I want to replace this query using Dapper Extensions Predicate?

SELECT * 
FROM SomeTable 
WHERE id IN (commaSeparatedListOfIDs)

The commaSeparatedListOfIDs is an IEnumerable of Integer.

What I have tried so far:

using (SqlConnection cn = new SqlConnection(_connectionString))
{
    cn.Open();
    var predicate = Predicates.Field<SomeTable>(f => f.Id, Operator.???, commaSeparatedListOfIDs);
    IEnumerable<SomeTable> list = cn.GetList<SomeTable>(predicate);
    cn.Close();
}

I need an operator Operator.In, but it does not exists in Dapper Extensions.

How should I implement "IN" clause with Dapper Extensions Predicate system?

I see. I don't think it is supported. File an issue https://github.com/tmsmith/Dapper-Extensions or, as I would recommend, use a better tool. Dapper Extensions is awkward at best. — Aluan Haddad, Apr 11 '18 at 14:12
@mr5 the operators available are like, equal, greater than, greater or equal, less than, less or equal. I do not know which use for accomplish the in clause. — DanielGatti, Apr 11 '18 at 14:25

Amit Joshi · Answer 1 · 2020-09-10T14:24:24.687

Solution 1:

Using PredicateGroup with GroupOperator.Or is one solution.

var predicateGroup = new PredicateGroup { Operator = GroupOperator.Or, Predicates = new List<IPredicate>() };
foreach(int thisID in commaSeparatedListOfIDs)
{
    var predicate = Predicates.Field<SomeTable>(f => f.Id, Operator.Eq, thisID);
    predicateGroup.Predicates.Add(predicate);
}
IEnumerable<SomeTable> list = cn.GetList<SomeTable>(predicateGroup);

Please refer this and this link.

Solution 2:

As you mentioned in your answer and this link, using FieldPredicate (Predicates.Field) with Operator.Eq, and passing IEnumerable parameter should do the same.

var predicate = Predicates.Field<SomeTable>(f => f.Id, Operator.Eq, commaSeparatedListOfIDs);

Here Eq should be internally translated to IN clause as per this source code of Dapper Extensions on GitHub.

if(Value is IEnumerable && !(Value is string))
{
    if(Operator != Operator.Eq)
    {
        throw new ArgumentException("Operator must be set to Eq for Enumerable types");
    }

    List<string> @params = new List<string>();
    foreach(var value in (IEnumerable)Value)
    {
        string valueParameterName = parameters.SetParameterName(this.PropertyName, value, sqlGenerator.Configuration.Dialect.ParameterPrefix);
        @params.Add(valueParameterName);
    }

    string paramStrings = @params.Aggregate(new StringBuilder(), (sb, s) => sb.Append((sb.Length != 0 ? ", " : string.Empty) + s), sb => sb.ToString());
    return string.Format("({0} {1}IN ({2}))", columnName, Not ? "NOT " : string.Empty, paramStrings);
}

To turn the IN clause as mentioned above to NOT IN clause, use the last bool not parameter. Please refer to this answer for more details.
Sample code is as below:

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

score 0 · Answer 2 · answered Apr 11 '18 at 14:27

Based on this link Github Issue

if you use a FieldPredicate, set to Equal and pass in an array it will translate to IN.

So, the correct way would be this.

using (SqlConnection cn = new SqlConnection(_connectionString))
{
    cn.Open();
    var predicate = Predicates.Field<SomeTable>(f => f.Id, Operator.In, commaSeparatedListOfIDs);
    IEnumerable<SomeTable> list = cn.GetList<SomeTable>(predicate);
    cn.Close();
}

How to implement "IN" clause with Dapper Extensions Predicate?

2 Answers2

Linked