Query with limit, but get count of the total

Hi there,

      count = unitOfWork.Query<MyEntity>()
        .Where(e => e.Lang = "en")
        .Count();


      var rows = unitOfWork.Query<MyEntity>()
        .Where(e => e.Lang = "en")
        .OrderByDescending(dbp => dbp.UpdatedOn)
        .OrderByDescending(dbp => dbp.CreatedOn)
        .Skip(page * pageSize)
        .Take(pageSize);

We have a lot of this happening, and although it is quite fast, you'd want this a bit faster. Since there is ordering going on the select query is probably looking at all the rows any way, so why shouldn't it be able to count them directly.

In MySQL they have SQLCALCFOUND_ROWS just for this. In MSSQL there is no "native" support for it but it can be done by:

SELECT totalrows, FirstName, LastName FROM ( 
    SELECT COUNT(*) OVER () totalrows, FirstName, LastName, 
            ROW_NUMBER() OVER (ORDER BY FirstName, LastName) rn 
    FROM Person.Contact WHERE Title='Mr.' 
) x WHERE rn BETWEEN 1 AND 10 
ORDER BY FirstName, LastName;

Or something similar.

How would I do this with Lightspeed in play?

+1 for this as I'm doing the same thing.

It's sort of the classic place ORMs rob you of performance in real-world apps. You can't use stored procedures, because then you're basically stuck writing an ORM inside your ORM to handle all the paging and mapping logic.

If you look at enhancing paging functionality, please consider the ".After(Id)" case in addition to the existing ".Offset/.Skip(Count)" one.

Hi chadw,

I'm unfamiliar with .Offset or .After(Id), can you explain where they are defined and how to use them? I haven't found them in IQueryable on MSDN. I've also looked for it in the mindscape docs for lightspeed and I only found .Offset.

Thank you!

The LightSpeed Native API has .Offset and .Limit which is equivalent to .Skip and .Take in LINQ.

Some scenarios make it hard to compute the Offset/Skip. In that case you want a way to request all results ".After" (new API call) the last row you displayed. Sometimes you use cursors, sometimes you have a good identifier for the rows. Our friends in New Zealand mention that LightSpeed 5 is on the horizon, so if they were thinking about paging improvements I just wanted to toss out the concept.

Sorry if my brevity sent you on a wild run through MSDN!

FYI, the big news with paging is this functionality in SQL Server 2012:

SELECT ... 
ORDER BY Id
OFFSET 50 ROWS
FETCH NEXT 100 ROWS ONLY

.After(Id) is a nice idea, Ill pop that on my list for 5.0 to investigate. We will be adding a SQL Server 2012 provider as part of the update - the paging is definitely a big win :)

Jeremy