EF GroupBy query optimization
I don't see anything wrong with the generated query. EF does generate verbose T-SQL, but that doesn't mean it's inefficient. If the generated query generates a decent execution plan there is no need to try to get EF to generate T-SQL the way you would have written it.

However the code would be easier to read like this:

var orders = await db.Orders.AsNoTracking()
                            .GroupBy(x =&gt; x.Status)
                            .Select(g =&gt; new { Status = g.Key, Count = g.Count() })
                            .ToDictionaryAsync(x =&gt; x.Status, x =&gt; x.Count);