Use Table Valued Parameter (TVP) instead of 'where in'

Question

Code like this:

connection.Execute("delete from Table where ID in @ids", new { ids=listOfIds });

fails when listOfIds is too long. You will get something along those lines:

The incoming request has too many parameters. The server supports a maximum of 2100

(depending on your rdbms)

Ideally, I would like to use a Table Valued Parameter. I am yet to find a decent dapper example. Could someone please point me in the right direction?

I am currently looking at Dapper.Tvp: http://www.nuget.org/packages/Dapper.Tvp for this. — cs0815, Oct 20 '14 at 15:47
Does this show usage? http://stackoverflow.com/questions/6232978/does-dapper-support-sql-2008-table-valued-parameters — Marc Gravell, Oct 20 '14 at 16:55
Thanks I am aware of this but is does not make sense. I want to inner join on a TVP called @Ids insted of using 'WHERE Id IN (@Ids) - there is no sproc ... — cs0815, Oct 20 '14 at 16:59

score 2 · Accepted Answer · answered Oct 20 '14 at 17:19

This should help:

// 1. declare the custom data type
// this is just to make it re-runnable; normally you only do this once
try { connection.Execute("drop type MyIdList"); } catch { }
connection.Execute("create type MyIdList as table(id int);");

// 2. prepare the data; if this isn't a sproc, also set the type name
DataTable ids = new DataTable {
    Columns = {{"id", typeof(int)}},
    Rows = {{1},{3},{5}}
};
ids.SetTypeName("MyIdList");

// 3. run the query, referencing the TVP (note @tmp represents the db data)
int sum = connection.Query<int>(@"
-- spoof some data
declare @tmp table(id int not null);
insert @tmp (id) values(1), (2), (3), (4), (5), (6), (7);
-- the actual query
select * from @tmp t inner join @ids i on i.id = t.id", new { ids }).Sum();
sum.IsEqualTo(9); // just checks the result

Use Table Valued Parameter (TVP) instead of 'where in'

1 Answers1