Search code examples
.netsql-serverormlite-servicestack

Faster equivalent of SQL Server IN clause for many values

I'm using OrmLite .NET with SQL Server 12.0. I want to select entities where a certain integer column (not the primary key) has one of many values, which I have in an array. An OrmLite expression like this:

q => query.Where(r => myIntegers.Contains(r.TheColumn))

gets translated to

WHERE "TheColumn" IN (1, 2, 3, ...) -- my integers

This works fine with ~100, but times out with 1000. How do I achieve the same effect with a larger list? Can I pass an array to SQL Server somehow or a table parameter?

Solution

  • Similar to what @JoeTaras commented, you can put the acceptable values into a subquery, something like;

    SELECT TheColumn from TheTable T
INNER JOIN (SELECT * from (VALUES(1),(2),(3),(4)) as V1(value)) V
ON T.TheColumn = V.value