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 query for large datasets, I'm having a hard time getting the .Limit (skip, rows) function to work in the SQL.In child table statement.
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 selection, but when used in child data, I get "Only one expression can be specified in the select list when no subquery is entered with EXISTS."
The SQL that comes out changes the 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 because I am selecting more than one column in the IN clause. Is there a better way to write this to avoid the error?
thank
source to share
If you are using SQL Server 2012 or later you should use SqlServer2012Dialect.Provider
for example:
container.Register<IDbConnectionFactory>(c =>
new OrmLiteConnectionFactory(connString, SqlServer2012Dialect.Provider));
This allows OrmLite to take advantage of the paging support added in SQL Server 2012, rather than using the window-breaking functionality required to implement paging for earlier versions of SQL Server.
source to share