Dapper and list parameters return Incorrect syntax near ','

Question

I have the following code that uses 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; }
}

which produces this based on 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

When it is executed I am getting the following error

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

I have two questions:

1. What am I doing wrong and I am getting this exception?

2. Why dapper translates this query and executes it using sp_executesql . How do I force it to use a normal select query like

   SELECT ClientId, InvoiceId, IsPaid FROM ClientInvoice WHERE ClientId IN (4) AND InvoiceId IN (34,35)

Answer 1

Looking at your profiled 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

We can see that you've got two sets of brackets around @ClientIds1 and @InvoiceIds1,@InvoiceIds2, the set which you wrote, and the set which Dapper (or a layer below it) is injecting.

Therefore, try removing the brackets which you added:

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

This matches the code in the 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

Answer 2

Remove the parentheses would fix the problem.

Wrong: "WHERE ClientId IN ( @ClientIds ) "

Correc: "WHERE ClientId IN @ClientIds "