Home EF - IQueryable - two selects with one db call
Reply: 0

EF - IQueryable - two selects with one db call

user1461
1#
user1461 Published in June 19, 2018, 4:17 pm

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!

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.372197 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO