CODEWITHSUNDEEP
c#linqentity-framework-6
ssharma
ssharma

Reputation: 541

How I can get record count matching predicate using Entity Framework

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());
}

Upvotes: 1

Views: 2966

Answers (3)

itekin
itekin

Reputation: 11

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();

Upvotes: 1

undefined
undefined

Reputation: 34309

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)

Upvotes: 0

Hany Habib
Hany Habib

Reputation: 1405

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

Upvotes: 3

Related Questions