CODEWITHSUNDEEP
sql-serverdapper
David Rubin
David Rubin

Reputation: 1648

What am I doing wrong with Dapper's IN-clause support?

I'm trying to use Dapper's (v1.42) support for automatic expansion of query parameters for IN clauses. My query looks like:

var records = db.Query(
    "SELECT [IdCol], [OtherCol], [TinyIntCol], [TextCol] FROM [Tbl] WHERE [FilterCol] = @filterVal AND [TinyIntCol] IN @byteVals",
    new { filterVal = ..., byteVals = flag ? new byte[] { Constants.Byte1, Constants.Byte2 } : new byte[] { Constants.Byte1 } }
);

But that results in System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near '@byteVals'.

I must be doing something wrong, but I'm not sure what. Please tell me what sort of idiocy I'm committing. Thank you!

Upvotes: 1

Views: 1223

Answers (1)

Dan Roberts
Dan Roberts

Reputation: 2329

The issue is with the use of the byte array, Dapper will always pass byte arrays as a single binary value - the fact that the query text has an "IN" clause does not change that (see the comment from @Damien_The_Unbeliever).

To illustrate, the following works (based on the NORTHWND database, since I had that to hand) because the parameter expansion is working with an int array -

var results = db.Query<Product>(
    "SELECT * FROM Products WHERE ProductId IN @ids",
    new
    {
        ids = new [] { 1, 2 } // This works (int array)
    }
);

.. but the following fails, where it tries to pass a byte array -

var results = db.Query<Product>(
    "SELECT * FROM Products WHERE ProductId IN @ids",
    new
    {
        ids = new byte[] { 1, 2 } // This fails (byte array)
    }
);

Using SQL profiler, the first int-array approach results in the following query:

exec sp_executesql
    N'SELECT * FROM Products WHERE ProductId IN (@ids1,@ids2)',
    N'@ids1 int,@ids2 int',
    @ids1=1,
    @ids2=2

You can clearly see the individual parameters being passed over.

The byte-array approach results in the following query:

exec sp_executesql
    N'SELECT * FROM Products WHERE ProductId IN @ids',
    N'@ids varbinary(2)',
    @ids=0x0102

Here, the "@ids" parameter is passed as a single value, rather than being expanded into individual ones - this is what is causing your problem.

You should be able to work around it by using your byte values to populate an int array.

Upvotes: 1

Related Questions