How I can get record count matching predicate using Entity Framework

Question

How to get a count of records matching the predicate in below function? Required to generate pagination.

public async virtual Task<IEnumerable<T>> GetAll(Expression<Func<T, bool>> predicate, int pageNo, int pageSize)
{
    return (await dbContext.Set<T>().Where(predicate).Skip(pageSize * (pageNo - 1)).Take(pageSize).ToListAsync());
}

score 3 · Answer 1 · answered May 21 '18 at 12:51

3

To calculate the count of items needs to be done before pagination using Count() Method. Here example from Microsoft documentation :

 public static async Task<PaginatedList<T>> CreateAsync(IQueryable<T> source, int pageIndex, int pageSize)
        {
            var count = await source.CountAsync();
            var items = await source.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToListAsync();
            return new PaginatedList<T>(items, count, pageIndex, pageSize);
        }

For more details check link below : https://learn.microsoft.com/en-us/aspnet/core/data/ef-mvc/sort-filter-page?view=aspnetcore-2.0

answered May 21 '18 at 12:51

Hany Habib

1,377
1
10
19

It would be better that wrap both `CountAsync` and `ToListAsync` into concurrent Async calls using `await Task.WhenAll`, that would be much more performance oriented solution – Mrinal Kamboj May 21 '18 at 16:36
Your solution with usage of Async calls from EF extensions is a good idea +1 for the same – Mrinal Kamboj May 21 '18 at 16:59

itekin · Answer 2 · 2018-05-22T13:17:52.037

1

You should separate query before ToList() call and employ separate Count() call. Relevant part:

var query = dbContext.Set<T>().Where(predicate);
var cnt = query.Count();
var result = query.Skip(pageSize * (pageNo - 1)).Take(pageSize).ToList();

edited May 22 '18 at 13:17

answered May 21 '18 at 11:56

itekin

11
4

Only issue is OP needs `Count` on following part `dbContext.Set().Where(predicate)`, otherwise the value is fixed as `PageSize` except the last page, which may be less – Mrinal Kamboj May 21 '18 at 12:44
This query will return only the page size count which is not correct like @MrinalKamboj mentioned. – Hany Habib May 21 '18 at 12:46
Right. Updated. – itekin May 22 '18 at 13:24

undefined · Answer 3 · 2018-05-21T13:17:57.747

0

All of the answers so far do this in two queries. I think this is bad, and due to the wonders of SQL optimisation theres a little hack you can do to avoid needing to do a second query for the count in pagination.

Instead of doing the second query for count, append the count to each row and then separate it in the result set.

Below is a little helper I wrote to help do this:

public static IQueryable<EntityWithCount<T>> GetWithTotal<T>(this IQueryable<T> entities, int page, int pageSize) where T : class
{
    return entities
        .Select(e => new EntityWithCount<T> { Entity = e, Count = entities.Count() })
        .Skip((page-1) * pageSize)
        .Take(pageSize);
}

public class EntityWithCount<T> where T : class
{
    public T Entity { get; set; }
    public int Count { get; set; }
}

Full source on github

This method is faster for reasonable page sizes and avoids any transactional issues you might have doing multiple queries.

You you can chain this at the end of any other un-enumerated query (such as the one in the question, replacing the skip/take and before the .ToListAsync() call)

edited May 21 '18 at 13:17

answered May 21 '18 at 13:12

undefined

33,537
22
129
198

EF will translate the call into `Select * , count(*) from ... where ...`, how this any better in performance than making two Async calls one doing `Select *`, another `Select Count(*)`, for application that would give faster results and in fact in your solution it is leading to extra count(*) column per row, which is wastage of memory, it would get bulky with the increase in number of rows – Mrinal Kamboj May 21 '18 at 16:34
1

@MrinalKamboj there is only a single query plan and server round trip generated for this query. SQL will optimise the count(*) per row into a single call over the whole query. This means that you essentially trade the roundtrip+queryplan for an extra integer per row in data transfer. This is negligible for any reasonable page size. It also has the important benefit of transactional integrity. In your example you could potentially get 9 rows returned and a count of 8 (if for example someone deleted something at the same time as your query) – undefined May 22 '18 at 03:24
Query Plan would be certainly optimized for count(*), still fetching extra column / row, which is directly proportional to the number of rows cannot be avoided, though may not have a huge memory impact but would yield a superfluous result set. Regarding transaction there will not be any difference, in result provided a default like ReadCommitted mode is used, in case of Sql query it is implicit transaction, for Async requests transaction is tied to the IDbConnection, will have the exactly have impact, its all about enlisting in the same transaction context. (Contd :) – Mrinal Kamboj May 22 '18 at 05:52
(Contd :) Most of the current day systems would prefer a concurrent Async request, especially since databases are capable of handling million+ transactions / sec. it is much more efficient and flexible to execute the async requests using `Task.WhenAll`, even in this case the Select query would be using same query plan as its executing same query -"Select ... using same predicate" – Mrinal Kamboj May 22 '18 at 06:02
@MrinalKamboj read committed does not guarantee consistent results between queries, see https://stackoverflow.com/a/4036063/1070291, as for the dual queries being faster, you would need to test that, but my guess is that there would be indiscernable difference on low latency, multicore systems, in high latency/load systems the difference would be more pronounced. for example if they are sitting in a priority queue you will be waiting on the last query even though you could have had a result in the first – undefined May 23 '18 at 04:11

How I can get record count matching predicate using Entity Framework

3 Answers3