如何实现“ IN”？ Dapper Extensions Predicate的子句？ [英] How to implement "IN" clause with Dapper Extensions Predicate?

问题描述

我要使用Dapper Extensions谓词替换此查询吗？

I want to replace this query using Dapper Extensions Predicate?

SELECT * 
FROM SomeTable 
WHERE id IN (commaSeparatedListOfIDs)

commaSeparatedListOfIDs 是整数的 IEnumerable 。

我有什么到目前为止尝试过的

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();
}

我需要一个运算符 Operator.In ，但Dapper Extensions中不存在。

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

我应该如何在Dapper Extensions谓词系统中实现 IN子句？

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

推荐答案

解决方案1：

使用 PredicateGroup 与 GroupOperator.Or 是一个解决方案。

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);

请参考此和此链接。

解决方案2：

正如您在答案中提到的那样，< a href = https://github.com/tmsmith/Dapper-Extensions/issues/164 rel = nofollow noreferrer>此链接，使用FieldPredicate（ Predicates.Field ）和 Operator.Eq ，并传递 IEnumerable 参数也应这样做。

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);

此处 Eq 应该在内部翻译为 IN 子句，根据此源

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);
}

---

打开 IN 子句中的 NOT IN 子句，使用最后一个 bool not 参数。有关更多详细信息，请参考此答案。

示例代码如下：

---

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);

这篇关于如何实现“ IN”？ Dapper Extensions Predicate的子句？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！