2

I am carrying out a pretty normal select data from table using Entity Framework Core and IQueryable. I am using paging in my search so want to fetch rows x to y, depending on the page size and current page.

Here's my code:

_dbContext.Orders.Where(o => o.UserId == userId)
   .Skip((pageNo - 1) * pageSize).Take(pageSize).ToListAsync();

And resulting SQL:

SELECT [o].[UserId], [o].[OrderId], [o].[OrderDate], [o].[OrderType], [o].[FromNameAddressId], [o].[ToNameAddressId], [o].[Status]
FROM [Orders] AS [o]
WHERE [o].[UserId] = 12
ORDER BY (SELECT 1) OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY;

The problem I'm facing is that I also want to return the total record count as part of the query results and end up (currently) having to do that as a separate call to the db.

Here is my code:

_dbContext.Orders.Where(o => o.UserId == userId).CountAsync();

And resulting SQL:

SELECT COUNT(*) FROM [Orders] AS [o] WHERE [o].[UserId] = 12;

I am looking to make this more efficient, so was looking to either return the total record count as part of the first query OR to run the two selects with one db call rather than two. Has anyone achieved this before? I'm fairly new to Entity Framework, so this is probably fairly straight forward to achieve.

Thanks in advance for any pointers!

Rob
  • 6,819
  • 17
  • 71
  • 131
  • Well, there would be the option to load your entities from a view or stored procedure. – marsze Feb 12 '18 at 13:47
  • 1
    You might want to check this out, https://stackoverflow.com/questions/7767409/better-way-to-query-a-page-of-data-and-get-total-count-in-entity-framework-4-1 – Jaliya Udagedara Feb 12 '18 at 13:54
  • 2
    Possible duplicate of [Better way to query a page of data and get total count in entity framework 4.1?](https://stackoverflow.com/questions/7767409/better-way-to-query-a-page-of-data-and-get-total-count-in-entity-framework-4-1) – marsze Feb 12 '18 at 13:55

0 Answers0