How to get total available rows from paginated ef core query

Question

Thanks in advance for taking time to read this question.

I have a view in my database, lets call it Members_VW

In my .net 5 API, I'm trying to get a paginated response for the list of members from the view with search parameters. I need to also return the total number of responses for the front end to know in how many pages the results will be returned in.

Currently the Members_VW is made with a query like:

select
    col1, col2, col3
from
    table1 1
    inner join table2 2 on 1.key = 2.key
    inner join tble3 3 on 3.key = 2.key
where
    defaultcondition1 = '1'
        and
    defaultcondition2 = '2'

I referred to this answer and tried using CTE which ended up changing my view to using a query like this:

with cte1 as (
select
    col1, col2, col3
from
    table1 1
    inner join table2 2 on 1.key = 2.key
    inner join tble3 3 on 3.key = 2.key
where
    defaultcondition1 = '1'
        and
    defaultcondition2 = '2')
cte2 as (
select count(*) over() from cte1 )
select
    *
from
    cte1, cte2

But this didn't work because it would always return the total number of rows in cte1 without any of the filters applied.

So, I continued to try to construct queries to return the total number of rows after the conditions are applied and found that this query works:

select
    col1, col2, col3, count(*) over()
from
    table1 1
    inner join table2 2 on 1.key = 2.key
    inner join tble3 3 on 3.key = 2.key
where
    defaultcondition1 = '1'
        and
    defaultcondition2 = '2'

Currently, I'm trying to implement the same query with EF Core but am struggling to implement that.

I've tried implementing the solution provided here, but as one of the comments suggests, this implementation is no longer allowed.

I am trying to avoid an implementation where I use a raw query. Is there anyway to get the result from count(*) over() without using a raw query?

The following is my current implementation:

IQueryable<MembersVW> membersQuery = _context.MembersVW;
membersQuery = membersQuery.Where(u => u.MemberId == memberid);
membersQuery = membersQuery.OrderBy(m => m.MemberId).Skip(page * size).Take(size);

When I do: membersQuery = membersQuery.Count() I'm returned with the following error:

Error   CS0029  Cannot implicitly convert type 'int' to 'System.Linq.IQueryable<PersonalPolicyAPI.Models.VwPersonalPolicyMember>'

Again, thanks for reading my question, appreciate any help you can offer.

Answer 1

I've read your question about can it be done with one query. While I'm not aware of any way to do it with 1 query I can offer one more solution that will help with your concern about performance and 2 queries. I do this frequently. Try:

//execute both queries at the same time instead of sequentially
var countqry = membersQuery.CountAsync();
var pageqry = membersQuery.OrderBy(m => m.MemberId).Skip(page * size).Take(size).ToListAsync();

//wait for them both to complete
Task.WaitAll(countqry, pageqry);

//use the results
var count = countqry.Result;
var page = pageqry.Result;

Answer 2

You try to assign the Count Value, which is an Integer, to the variable of your query, which is an IQueryable. That's all there is to it.

If you want to do it in one single query, as you suggest in one of your comments, you can first execute the query to get all Entries, then count the result, and then filter the result with skip/take. This is most probably not the most efficient way to do this, but it should work.

I'd also suggest to use AsNoTracking() if you do not modify any data in this function/api.

EDIT: I'd suggest this solution for now. The counting is fast, as it actually doesn't fetch any data and just counts the rows. It is still two queries tho, gonna try to combine it & edit my answer later.

 var count = await yourContext.YourTable.CountAsync();
 var data = await yourContext.YourTable
    .OrderBy(x => x.YourProp)
    .Skip(10).Take(10)
    //.AsNoTracking()
    .ToListAsync();

EDIT2: Okay, so, I couldn't get it to just make on DB-Call yet, however, I could combine it syntactically. However, the approach in my first edit is easier to read and does basically the same. Still, gonna dig deeper into this, there's gotta be a funky way to do this.

var query = yourContext.YourTable.AsQueryable();
var result =  await query.OrderBy(x => x.Prop)
                .Select(x => new {Data = x, Count = query.Count()} )
                .Skip(50).Take(50)
                .AsNoTracking()
                .ToListAsync();
var count = result.FirstOrDefault()?.Count ?? 0; //If empty/null return 0
var data = result.Select(x => x.Data).ToList();

Answer 3

In membersQuery = membersQuery.Count() line you are assigning integer value to a queryable list, which is incorrect. You can get the list item counts after your query like this i.e.

membersQuery = membersQuery.OrderBy(m => m.MemberId).Skip(page * size).Take(size);
int totalCount = membersQuery.Count();

To get count column in same list, you first need to add Count property in your MembersVW class and then use LINQ projection to add column value.

Solution-1:

memberQuery = membersQuery.Select(p => new MembersVW 
              {
                  col1 = p.col1
                  col2 = p.col2
                  col3 = p.col3
                  count = totalCount
              });

Solution-2:

With LINQ foreach loop i.e.

membersQuery.ForEach(item => 
              {
                  item.count = totalCount;
              });

Answer 4

membersQuery.Count() returns integer not the queryable

you can do

     int count = membersQuery.Count();
     List<MemberVW> = membersQuery.OrderBy(m => m.MemberId).Skip(page * size).Take(size).ToList();

and you can return with

       public class MemberVwWithCount {
           public int Count{get;set;}
           public List<MemberVW> Members {get; set;}
         }