CODEWITHSUNDEEP
c#sql-serverlinqentity-framework-coreexpression
Razvan
Razvan

Reputation: 17

Entity Framework Core count with expression parameter

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

  1. 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>'

  1. 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).

  1. 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.

  1. 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?

Upvotes: 1

Views: 3091

Answers (1)

Holger
Holger

Reputation: 2652

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

.Where(count1Condition).Count()

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

Upvotes: 0

Related Questions