在Dapper中如何使用'Where In'？

Question

我一直在尝试使用Dapper的WHERE IN子句来处理IEnumerable<string>，但一直未能成功。

文档中确实提到支持IEnumerable<int>用于WHERE IN，但我甚至不能让它正常工作。

Dapper允许您传递IEnumerable<int>，并自动参数化查询。

我一直收到的错误消息是Sql语法错误：Incorrect syntax near ','.

我编写了一些测试代码，希望能够演示我的目标。

---

string connString = "Server=*.*.*.*;Database=*;User Id=*;Password=*;";

string sqlStringIn = @"SELECT StringText FROM 
                (SELECT 1 ID, 'A' StringID, 'This is a test' StringText
                UNION SELECT 2 ID, 'B' StringID, 'Another test' StringText
                UNION SELECT 3 ID, 'C' StringID, 'And another' StringText
                UNION SELECT 4 ID, 'D' StringID, 'and again' StringText
                UNION SELECT 5 ID, 'E' StringID, 'yet again' StringText) data
                WHERE StringId IN (@str)";

string sqlIntegerIn = @"SELECT StringText FROM 
                (SELECT 1 ID, 'A' StringID, 'This is a test' StringText
                UNION SELECT 2 ID, 'B' StringID, 'Another test' StringText
                UNION SELECT 3 ID, 'C' StringID, 'And another' StringText
                UNION SELECT 4 ID, 'D' StringID, 'and again' StringText
                UNION SELECT 5 ID, 'E' StringID, 'yet again' StringText) data
                WHERE ID IN (@integer)";


using (SqlConnection conn = new SqlConnection(connString))
{
    conn.Open();

    List<int> integers = new List<int>{ 1, 2, 3 };
    List<string> strings = new List<string> { "A", "B", "C" };

    var parameters = new {str = strings, integer = integers };

    //fails here
    IEnumerable<string> intTest = conn.Query<string>(sqlIntegerIn, parameters, commandType: System.Data.CommandType.Text);

    //and here
    IEnumerable<string> stringTest = conn.Query<string>(sqlStringIn, parameters, commandType: System.Data.CommandType.Text);

}

- Sam

---

为什么不使用Linq？有一个Dapper的帮助库可以为您完成这项工作：http://sqlinq.codeplex.com/releases/view/88056 - aybe

没必要这样做，dapper会明确处理这种情况。 - Marc Gravell

Answer 1

为了在这里执行所需的操作，dapper需要动态更改SQL语句 - 因此必须确保它确实在做正确的事情。常规有效的SQL语法包括括号：

为了在这里进行所需的操作，Dapper需要即时更改SQL查询语句 - 因此必须非常确信它正在执行正确的操作。正常有效的SQL语法包括括号：

WHERE StringId IN (@str)

为了消除歧义，voodoo dapper syntax在省略括号的情况下进行操作。

WHERE StringId IN @str

如果检测到这种情况，它会查找名为str的参数并将其扩展为以下之一：

WHERE 1=0 -- if no values
WHERE StringId = @str -- if exactly one value
WHERE StringId IN (@str0, @str1, ...) -- if more than one value

简洁版：去掉括号。

- Marc Gravell

---

@csetzkorn 如果你正在使用非常大的集合，那么使用 TVP 和 INNER JOIN 比 IN 更好；dapper 对 TVP 有完全支持。我不想在库中硬编码每个不同供应商的不同技术限制。 - Marc Gravell

@MarcGravell，如果传入的对象上只有一个IEnumerable参数，那么这个方法只能用于它吗？还是说它也可以处理其他属性呢？var parameters = new { assemblyName, entityIds = listOfIds.ToArray() }; - jcolebrand

@Ben 如果数据库是varchar而不是nvarchar，那么是的：DbString。如果您广泛使用varchar，则我记得我们公开了一个默认设置。 - Marc Gravell

@MarcGravell 非常有帮助。默认设置是什么？我知道如何使用DbString，但如何使用数组完成它？ - Ben Hoffman

它在ExecuteScalar扩展中也能工作吗？使用建议的语法会导致“不正确的语法”运行时异常。 - Simone

Answer 2

如果您想处理空列表（即使IN从句变成可选），那么我希望添加一个重要说明。我通过添加一个包含计数的属性来实现这一点，例如： public int InClauseCount => InClauseList?.Length ?? 0;

然后像这样在SQL中使用计数...

Select field1, field2
from Table1
where (some condition)
AND (@InClauseCount = 0 OR field1 IN @InClauseList)

我希望这篇文章能够帮助到一些人。我花费了一些时间来解决这个问题，部分原因是我对Dapper不熟悉。

- user1856792