Entity Framework Core count with expression parameter

Question

I am trying to use Entity Framework Core to generate a performant server-side SQL query, that counts records in a few groups. For example, let's say I have the table:

CREATE TABLE ExOrders
(
    Id UNIQUEIDENTIFIER,
    Column1 VARCHAR(250),
    Column2 INT,
    ColumnN VARCHAR(500),
)

and the result should be the select query:

select 
    count(<count1Condition>) as C1,
    count(<count2Condition>) as C2
from 
    ExOrders
where 
    <whereGenericCondition>

For each condition I have an Expression<Func<T, bool>> expression already generated.

What I tried so far

The Linq Count() function

I am trying to get the above result with a query like:

Expression<Func<T, bool>> whereGenericCondition = GetExpression1();
Expression<Func<T, bool>> count1Condition = GetExpression2();
Expression<Func<T, bool>> count2Condition = GetExpression3();

var countRequestS1 = _dbcontext.Set<T>()
    .Where(whereGenericCondition)
    .GroupBy(s => 0)
    .Select(agg => new
                   {
                       C1 = agg.Count(count1Condition), // <- parameter error
                       C2 = agg.Count(count2Condition)  // <- parameter error
                   });

The problem is the Count extension doesn't support expression parameters.

Error:

Argument 2: cannot convert from 'System.Linq.Expressions.Expression<System.Func<T, bool>>' to 'System.Func<T, bool>'

The Linq Count() function with AsQueryable()

I tried to call AsQueryable before the Count method:

var countRequestS2 = _dbcontext.Set<T>()
    .Where(whereGenericCondition)
    .GroupBy(s => 0) // <- from this point onward it is executed clientside
    .Select(agg => new
    {
        C1 = agg.AsQueryable().Count(count1Condition), 
        C2 = agg.AsQueryable().Count(count2Condition) 
    });

But in this case it just gets all the data into the app, and processes it locally (unaceptable scenario in my case, because there are hundreds of thousands of lines).

Pre-compiling the expressions

I have also tried compiling count1Condition and count2Condition expressions before using them:

var countRequestS3 = _dbcontext.Set<T>()
    .Where(whereGenericCondition)
    .GroupBy(s => 0) // <- from this point onward it is executed clientside
    .Select(agg => new
    {
        C1 = agg.Count(x => count1Condition.Compile()(x)),
        C2 = agg.Count(x => count2Condition.Compile()(x))
    });

But in this case it just gets all the data into the app, same as #2.

Implementing my own Count()

My last attempt was to implement my own CountAfterGroupByMethod but I get an error related to generic T type functions.
In context model builder this crashes:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.HasDbFunction(typeof(CountAfterGroupByExtensions).GetMethod("CountAfterGroupBy"), options =>
    {
        options.HasTranslation(CountAfterGroupByExpressionTranslator.Translate);
    });
    // where CountAfterGroupBy is 
    // public static int CountAfterGroupBy<TSource>(this IEnumerable<TSource> source, Expression<Func<TSource, bool>> predicate)
}

with error:

System.ArgumentException: 'The DbFunction 'CountAfterGroupByExtensions.CountAfterGroupBy' is generic. Generic methods are not supported.'

Is there a flaw in #3 or #4 that I can't see? Or is there anything else I can try?

What is the sql you expect it to return here? normally in sql if you count two columns (which is what you seem to be doing) you'll get the same result twice unless you're trying to do a case statement of some kind. Here's another similar question: stackoverflow.com/questions/39590025/… — Mark Davies
Im pretty sure the LINQ translation doesn't support what you're trying to do. You'll probably have to run two separate queries and submit a feature proposal on the EF core GitHub page. — Kieran Devlin
What is the exact EF Core version (unfortunately that matters)? — Ivan Stoev

Holger Holger · Accepted Answer · 2019-12-02T16:20:38

A Count does not accept conditions, right. So put a

.Where(count1Condition).Count()

in front if your Count, and you have your conditional Count.

Entity Framework Core count with expression parameter

1 Answers