Does Entity Framework support COUNT(*) OVER()

Question

Introduction

Just like this question related to nhibernate, I was wondering if something like this exists for Entity Framework 6. I did some research but didn't find anything usable.

In addition to querying paginated results using .Skip(x) and .Take(y), we want to get the count of all items matching our where clauses before applying pagination.

We're now putting the LINQ query, with where clauses applied, in a variable, so we can use it twice, once to get the count, once to get the paginated results.

But for performance reasons we want to do both in one query, like you would with COUNT(*) OVER() in a RAW SQL query.

Question

Does Entity Framework support COUNT(*) OVER() somehow? If not, can you give a pointer on where to start looking for implementing this.

Proposals

First I thought about creating an IQueryable<T> extension method: CountOver(out long totalCount), but this might not work as we're still building the query.

Then I thought about introducing an attribute [CountOver] which we could use in our entity models:

public class Person
{
    public string Fullname { get; set; }
    [CountOver]
    public long TotalCount { get; set; }
}

If we could modify the source, the latter would not be that hard to implement, but currently I have no idea if this is even possible...

Answer 1

No. LINQ does not have a query operator that conceptually translates to COUNT() OVER(). But you can reuse a query to both Count() its rows, and select its results.

eg, something like

        var q = db. . . ;

        var q2 = from r in q
                 select new { r, count = q.Count() };