title: How to get total available rows from paginated ef core query
body: <p>Thanks in advance for taking time to read this question.</p>
  <p>I have a view in my database, lets call it Members_VW</p>
  <p>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.</p>
  <p>Currently the Members_VW is made with a query like:</p>
  <pre><code>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'
  </code></pre>
  <p>I referred to <a href="https://stackoverflow.com/questions/61160156/get-paginated-rows-and-total-count-in-single-query">this</a> answer and tried using CTE which ended up changing my view to using a query like this:</p>
  <pre><code>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
  </code></pre>
  <p>But this didn't work because it would always return the total number of rows in <code>cte1</code> without any of the filters applied.</p>
  <p>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:</p>
  <pre><code>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'
  </code></pre>
  <p>Currently, I'm trying to implement the same query with EF Core but am struggling to implement that.</p>
  <p>I've tried implementing the solution provided <a href="https://stackoverflow.com/a/36488316/2786714">here</a>, but as one of the comments suggests, this implementation is no longer allowed.</p>
  <p>I am trying to avoid an implementation where I use a raw query. Is there anyway to get the result from <code>count(*) over()</code> without using a raw query?</p>
  <p>The following is my current implementation:</p>
  <pre><code>IQueryable&lt;MembersVW&gt; membersQuery = _context.MembersVW;
  membersQuery = membersQuery.Where(u =&gt; u.MemberId == memberid);
  membersQuery = membersQuery.OrderBy(m =&gt; m.MemberId).Skip(page * size).Take(size);
  </code></pre>
  <p>When I do:
  <code>membersQuery = membersQuery.Count()</code>
  I'm returned with the following error:</p>
  <pre><code>Error   CS0029  Cannot implicitly convert type 'int' to 'System.Linq.IQueryable&lt;PersonalPolicyAPI.Models.VwPersonalPolicyMember&gt;'
  </code></pre>
  <p>Again, thanks for reading my question, appreciate any help you can offer. </p>