c# - Dapper 和列表参数在 ',' 附近返回不正确的语法

Question

我有以下使用 Dapper 的代码:

public async Task GetClientsInvoices(List<long> clients, List<int> invoices)
{
    var parameters = new
    {
        ClientIds = clients,
        InvoiceIds = invoices
    };

    string sqlQuery = "SELECT ClientId, InvoiceId, IsPaid" +
                       "FROM ClientInvoice " +
                       "WHERE ClientId IN ( @ClientIds ) " +
                       "AND InvoiceId IN ( @InvoiceIds )";

    var dbResult = await dbConnection.QueryAsync<ClientInvoicesResult>(sqlQuery, parameters);
}

public class ClientInvoicesResult
{
    public long ClientId { get; set; }

    public int InvoiceId { get; set; }

    public bool IsPaid { get; set; }
}

它基于 sql server profiler 产生这个

exec sp_executesql N'SELECT ClientId, InvoiceId, IsPaid FROM ClientInvoice WHERE ClientId IN ( (@ClientIds1) ) AND InvoiceId IN ( (@InvoiceIds1,@InvoiceIds2) )',N'@InvoiceIds1 int,@InvoiceIds2 int,@ClientIds1 bigint',InvoiceIds1=35,InvoiceIds2=34,ClientIds1=4

执行时出现以下错误

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.

我有两个问题:

1. 我做错了什么，我得到了这个异常？

2. 为什么 dapper 翻译这个查询并使用 sp_executesql 执行它。我如何强制它使用像

   这样的普通选择查询

   选择 ClientId、InvoiceId、IsPaid来自客户发票其中 ClientId 在 (4)和 InvoiceId 在 (34,35)

Answer 1

查看您分析的 SQL:

exec sp_executesql
    N'SELECT ClientId, InvoiceId, IsPaid
        FROM ClientInvoice
        WHERE ClientId IN ( (@ClientIds1) )
        AND InvoiceId IN ( (@InvoiceIds1,@InvoiceIds2) )',
    N'@InvoiceIds1 int,@InvoiceIds2 int,@ClientIds1 bigint',InvoiceIds1=35,InvoiceIds2=34,ClientIds1=4

我们可以看到您在 @ClientIds1 周围有两组括号和 @InvoiceIds1,@InvoiceIds2 ，您编写的集合，以及 Dapper(或其下方的层)正在注入(inject)的集合。

因此，尝试删除您添加的括号:

string sqlQuery = "SELECT ClientId, InvoiceId, IsPaid " +
                   "FROM ClientInvoice " +
                   "WHERE ClientId IN @ClientIds " +
                   "AND InvoiceId IN @InvoiceIds";

这与 Dapper README 中的代码匹配:

Dapper allows you to pass in IEnumerable<int> and will automatically parameterize your query.

For example:

connection.Query<int>("select * from (select 1 as Id union all select 2 union all select 3) as X where Id in @Ids", new { Ids = new int[] { 1, 2, 3 } });

Will be translated to:

select * from (select 1 as Id union all select 2 union all select 3) as X where Id in (@Ids1, @Ids2, @Ids3)" // @Ids1 = 1 , @Ids2 = 2 , @Ids2 = 3