I have this method:    public async Task<ActionResult> Index()    {        var orders = await db.Orders.AsNoTracking().GroupBy(x => x.Status).Select(g => new { Status = g.Key, Count = g.Count() }).ToDictionaryAsync(x => x.Status, x => 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?
EF GroupBy query optimization
c#;entity framework
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 => x.Status)                            .Select(g => new { Status = g.Key, Count = g.Count() })                            .ToDictionaryAsync(x => x.Status, x => x.Count);