Get Total Count and Page Rows in same database trip when using Entity Framework

Question

I am currently using the following method to get a page of customers as well as the total count. The only problem is that I am making 2 database trips - one for getting the total count and the other for getting the actual rows for the page.

My question is: Can I combine the totalcount query with the actual rows query so Entity Framework sends both the queries in a single database trip?

public IList GetPageOfCustomers(string name, int skipCount, 
                     int pageSize, out int totalCount) {

using(CustomerEntities e = new CustomerEntities()) {

    //FIRST QUERY
    var query = (from c in e.Customers
    where c.NAME.Contains(name)
    select new {
        c.CustomerID, c.NAME, c.CITY, c.STATE, c.COUNTRY
    })
        .Distinct()
        .OrderBy(s = > s.NAME)
        .ThenBy(s = > s.CITY)
        .ThenBy(s = > s.CustomerID);


    //SECOND QUERY ( executed in a separate database trip)
    int totalCount = (from c in e.Customers
    where c.NAME.Contains(name)
    select new {
        c.CustomerID, c.NAME, c.CITY, c.STATE, c.COUNTRY
    })
        .Distinct()
        .Count();

    return query.Skip(skipCount).Take(pageSize).ToList();
     }//END of  USING
   }//END of  METHOD

I would like to get the distinct count of the same result set that is being fetched for page rows, else I might get an inaccurate total count. — Sunil, Jun 14 '14 at 16:22
`where m.NAME.Contains(name)` What is `m`?, shouldn't that be `c`? — The One, Jun 14 '14 at 16:36
@ATM, You mean create a stored procedure which will execute both the queries? — Sunil, Jun 14 '14 at 16:45
With [Entity Framework Extended](https://github.com/loresoft/EntityFramework.Extended) you can send multiple queries in one package. (See "Future Queries"). — Gert Arnold, Jun 14 '14 at 19:16
@GetArnold, Can I combine Entity Framework Extended with my current Entity Framework 4.x code base OR I will have to replace Entity Framework 4.x in my code base? — Sunil, Jun 15 '14 at 15:53
I'm pretty sure you'll have to upgrade EF. I don't know when this futures feature was introduced, but I think in one of the 5 versions (requiring EF 5). — Gert Arnold, Jun 15 '14 at 16:06
The clever trick how to do it is in http://stackoverflow.com/questions/7767409/better-way-to-query-a-page-of-data-and-get-total-count-in-entity-framework-4-1, but it's better to have simple design with 2 calls — Michael Freidgeim, Mar 10 '17 at 21:47

score 1 · Answer 1 · answered Apr 06 '18 at 07:36

1

I thought and research a lot on this issue. Right now and with EF 6, there are 2 good practices:

(1) The first solution is to have a Stored Procedure (I know, I Know, you usually want to avoid Stored Procedures when you work with EF, go to solution 2 then!), which returns multiple results. This article explained it:

Entity Framework Sprocs with Multiple Result Sets

(2) The second best practice is to use "Query Future" feature of Entity Framework Plus package. This is a very cool extension to Entity Framework and can run multiple queries in one database trip.

answered Apr 06 '18 at 07:36

Tohid

6,175
7
51
80

I looked into the Future feature of EF+, but it lacks async for EF Core. Therefor I prefer to have 2 roundtrips to the database with async support. – kipusoep Jan 29 '20 at 08:35
@kipusoep - did u check this link? https://entityframework-plus.net/ef-core-query-future – Tohid Jan 29 '20 at 12:34
Yes I did, why? – kipusoep Jan 29 '20 at 16:58
@kipusoep- huh, I thought it has async method. – Tohid Jan 29 '20 at 19:24

score -3 · Accepted Answer · edited May 23 '17 at 12:17

-3

Depending on the cost of the database roundtrip and number of items coming back, it might be faster/easier to perform the base query once and do the paging/count operations on the c# server. i.e.

var results = (from c in e.Customers
               where m.Name.Contains(name)
               select new { c.CustomerId, c.NAME, c.CITY, c.STATE, c.COUNTRY })
              .Distinct()
              .OrderBy(s => s.NAME)
              .ThenBy(s => s.CITY)
              .ThenBy(s => s.CustomerId)
              .ToList();
totalCount = results.Count;
return results.Skip(skipCount).Take(pageSize).ToList();

This will only perform one database call, but won't perform the paging operations on the sql server.

Edit:
Also take a look at this Better way to query a page of data and get total count in entity framework 4.1?

edited May 23 '17 at 12:17

Community

1
1

answered Jun 14 '14 at 16:30

Jon R

94
3

1

This will work but I was looking for executing 2 queries in a single database trip, else I could end up with huge number of rows being returned by database, which I am avoiding. – Sunil Jun 14 '14 at 16:42
2

ah, how about this: http://stackoverflow.com/questions/7767409/better-way-to-query-a-page-of-data-and-get-total-count-in-entity-framework-4-1 – Jon R Jun 14 '14 at 17:02
Can you put this in your post, so I can mark this as an answer? – Sunil Jun 15 '14 at 15:54
Sure, sorry about that. I'm just getting into StackOverflow – Jon R Jun 18 '14 at 20:43
The solution is not scalable. If you need 1page, don't load all table to the server – Michael Freidgeim Mar 10 '17 at 13:56
Not scalable at all and could give you performance issues for a long dataset, this is basically retrieving the entire dataset into memory and then applying the pagination and count operation. – Gabriel Cerutti Jul 19 '17 at 13:17

Get Total Count and Page Rows in same database trip when using Entity Framework

2 Answers2

Linked