Mark
Mark

Reputation: 31

ServiceStack.OrmLite Using Limit in SQL.In filter

I have a parent/child table setup - Items/ItemDetails. This part works:

    var q = db.From<Item>(); //various where clauses based on request
    items = db.Select<Item>(q);
    q = q.Select(a => a.ITEM_NO);
    itemDetails = db.Select<ItemDetail>(x => Sql.In(x.ITEM_NO, q));

Trying to add paging to improve the performance of this request for large data sets, I'm having trouble getting the .Limit(skip, rows) function to work in the SQL.In statement of the child table.

    var q = db.From<Item>().Limit(skip, rows);
    items = db.Select<Item>(q);
    q = q.Select(a => a.ITEM_NO);
    itemDetails = db.Select<ItemDetail>(x => Sql.In(x.ITEM_NO, q));

It works when limiting the results in the first select, but when used in the child data pull I get "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."

The SQL that comes out changes the where subquery to:

    WHERE "ITEM_NO" IN (SELECT * FROM (SELECT  "ITEM_NO", ROW_NUMBER() OVER 
    (ORDER BY "ITEM"."ITEM_NO") As RowNum  
    FROM "ITEM") AS RowConstrainedResult WHERE RowNum > 5 AND RowNum <= 15)

I understand the SQL error is because I am selecting more than one column in the IN clause. Is there a better way to write this to avoid the error?

Thanks

Upvotes: 3

Views: 469

Answers (1)

mythz
mythz

Reputation: 143374

If you're using SQL Server 2012 or later you should use SqlServer2012Dialect.Provider, e.g:

container.Register<IDbConnectionFactory>(c => 
    new OrmLiteConnectionFactory(connString, SqlServer2012Dialect.Provider)); 

Which lets OrmLite use the paging support added in SQL Server 2012 instead of resorting to use the windowing function hack required to implement paging for earlier versions of SQL Server.

Upvotes: 1

Related Questions