How to suppress SQL Server's 5000 row query limit globally for OrmLite and AutoQuery?

ServiceStack.OrmLite
1

I have over 5200 rows in a SQL Server 2019 database, but when I query using OrmLite only 5000 rows are returned.

private async Task<List<Position>> GetCombinedPositionsAsync(QueryPosition request)
    {
        using var db = AutoQuery!.GetDb<Position>(Request);

        var find = new QueryPosition()
        var q = AutoQuery.CreateQuery(find, Request, db);
        var result = await db.SelectAsync(q);
        return result;  //should be 5200 results, but only 5000 returned from query
    }
    //
ServiceModel


[Route("/api/report/position/data", "GET")]
public class QueryPosition : QueryDb<Position>, IGet
{
}

Is there any limit being applied here? (I am using ServiceStack 8.0.0 on Windows.)

I get the same 5000 limit when using AutoQuery instead of OrmLite

var result = await AutoQuery.ExecuteAsync(find, q, Request, db);

2

My understanding is that 5000 is a SQL Server query limit.

I can suppress the 5000 limit with the following:

q.Rows = 0; // needed to stop 5000 row limit

Is there a way to suppress the 5000 limit globally for all queries?

3

The AutoQuery Max Limit is set when you register the AutoQueryFeature plugin, otherwise OrmLite doesn’t impose any limits itself.

4

Great, that is perfect. Thank you.

1 Like