GroupBy and Count by condition Entity Framework

Question

I am trying to group by a column and count the number of true/false rows in other columns.

For example we have a entity class:

public class Example {
    public int Id { get; set; }
    public int ParentId { get; set; }
    public string Type { get; set; }
    public bool Vote { get; set; }
}

I need to group by Type and count the number of true/false votes where the ParentId is a certain value. What I have:

await _dbContext.Example
    .Where(x => x.ParentId == parentid)
    .GroupBy(x => new { t.Type, t.Vote })
    .Select(x => new 
    {
        TrueVotes = x.Count(v => v.Vote == true),
        FalseVotes = x.Count(v => v.Vote == false),
        Type = x.Key.Type
    })
    .ToListAsync();

Entity throws an exception when this code runs saying it cannot be converted to SQL. The issue is with the Count call.

Performance does need to be taken into account as well.

https://stackoverflow.com/questions/7285714/linq-with-groupby-and-count — Flydog57, Sep 26 '20 at 15:24

score 3 · Accepted Answer · answered Sep 26 '20 at 16:12

Conditional Count SQL translation was not supported well in EF6 and at all in EF Core until now.

EF Core 5.0 (currently in preview) finally adds correct translation, so the sample code works without modifications.

In pre EFC Core 5.0 you could use the equivalent conditional Sum, i.e. instead of

Count(condition)

use

Sum(condition ? 1 : 0)

In your query

TrueVotes = x.Sum(v => v.Vote == true ? 1 : 0),
FalseVotes = x.Sum(v => v.Vote == false ? 1 : 0),

Unrelated, but you should probably exclude Vote from grouping key

//.GroupBy(x => new { t.Type, t.Vote })
.GroupBy(x => new { t.Type })

otherwise counts will always be 0 and 1 or vice versa.

GroupBy and Count by condition Entity Framework

1 Answers1