EF Core Linq with Multiple Count Using the Same Column

I am counting the total number of records based on a condition from the same column, basically, this is what I have now:

var checkTotal = await _dbRO.AllOpenExceptions
	.Where(x => x.ClientId == _user.ClientId)
	.CountAsync(x => x.ExceptionType == "Check");

var achTotal = await _dbRO.AllOpenExceptions
	.Where(x => x.ClientId == _user.ClientId)
	.CountAsync(x => x.ExceptionType == "ACH");

As you can see, I just need to count the number of records for ExceptionType column is "Check" vs. "ACH". Is there any way to combine these two separate queries into a single query?

I've tried group by with select but couldn't figure out the exact syntax. Thanks for any help!

We can group and filter data using the clientId and exceptionType and then convert it to a dictionary:

var totals = await _dbRO.AllOpenExceptions
                        .Where(x => x.ClientId == clientId && (x.ExceptionType == "ACH" || x.ExceptionType == "Check"))
                        .GroupBy(x => new { x.ClientId, x.ExceptionType })
                        .ToDictionaryAsync(
                              key => key.Key.ExceptionType,
                              value => value.Count(),
                              cancellationToken);

If you don't have a cancellation token, just get rid of it. And now you can access your total by key ACH or Check.

var achTotal = totals["ACH"];
var checkTotal = totals["Check"];

Going a bit further and avoiding some exceptions, you could even use the TryGetValue method:

totals.TryGetValue("ACH", out var achTotal);
totals.TryGetValue("Check", out var checkTotal);

Proof of concept

You can do that with GroupBy:

var totals = await _dbRO.AllOpenExceptions
	.Where(x => x.ClientId == _user.ClientId)
	.GroupBy(x => 1)
	.Select(g => new 
	{ 
		CheckCount = g.Sum(x => x.ExceptionType == "Check" ? 1 : 0), 
		AchCount   = g.Sum(x => x.ExceptionType == "ACH"   ? 1 : 0) 
	)
	.FirstOrDefaultAsync();

var checkTotal = totals?.CheckCount ?? 0;
var achTotal   = totals?.AchCount   ?? 0;