EF GroupBy query optimization I have this method:

    public async Task&lt;ActionResult&gt; Index()
    {
        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);
        ViewBag.InWork = orders[WebApplication.Models.Order.OrderStatus.InWork];
        ViewBag.InProcess = orders[WebApplication.Models.Order.OrderStatus.InProcess];
        ViewBag.Accepted = orders[WebApplication.Models.Order.OrderStatus.Accepted];
        ViewBag.Deleted = orders[WebApplication.Models.Order.OrderStatus.Deleted];

        return View();
    }


and it's generated this SQL:

SELECT 
    [GroupBy1].[K1] AS [Status], 
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT 
        [Extent1].[Status] AS [K1], 
        COUNT(1) AS [A1]
        FROM [dbo].[Orders] AS [Extent1]
        GROUP BY [Extent1].[Status]
    )  AS [GroupBy1]


How can I escape from SELECT FROM SELECT and optimize this query?
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);