Using an IEnumerable as Parameter for a SELECT

ServiceStack.OrmLite Oracle
1

Is there a way to use Ormlite to perform a SELECT on a table (in Oracle, in this case) where the filter is a subset of the columns of an IEnumerable. I’d be interested if there were a way to execute a query that returns the same results as the following hand-rolled SQL:

SELECT * 
FROM TEST 
WHERE (COL_1, COL_2, COL_3, COL_4, COL_5)
IN 
(
    ('1', '0', '234', '3'), 
    ('2', '0', '098', '16.1166666666667', '3')
)

Where, however, the IEnumerable to be passed in contains more than those 5 fields.

2

No you’ll need to use Custom SQL for non-standard SQL like this, e.g:

var results = db.SqlList<Test>(@"SELECT * 
    FROM TEST 
    WHERE (COL_1, COL_2, COL_3, COL_4, COL_5)
    IN 
    (
        ('1', '0', '234', '3'), 
        ('2', '0', '098', '16.1166666666667', '3')
    )");

You can also use partial Custom SQL like:

var q = db.From<Test>()
    .Where(@"(COL_1, COL_2, COL_3, COL_4, COL_5)
    IN 
    (
        ('1', '0', '234', '3'), 
        ('2', '0', '098', '16.1166666666667', '3')
    )");

as well as:

var q = db.From<Test>()
    .Where(@"(COL_1, COL_2, COL_3, COL_4, COL_5)
    IN 
    (
        ({0}, {1}, {2}, {3}), 
        ({4}, {5}, {6}, {7}, {8})
    )", "1","0","234","3","2","0","098", "16.1166666666667","3");

Because the IN statement values are jagged this is about the best you can do: