EF GroupBy query optimization

Question

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?

• Mathieu Guindon: It looks like the GroupBy is causing it... are you having performance issues? Missing index on Status?
• sDima: No, I haven't performance issues. I thought that this query can be more simpler.

Answer 1

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);

Answer 2

Building on MM's answer, you could simplify the query even more by getting rid of the Select:

var orders = await db.Orders.AsNoTracking()
 .GroupBy(x => x.Status)
 .ToDictionaryAsync(g => g.Key, g => g.Count());

UPDATE

As mentioned in sDima's comment below, this is actually less efficient than the original query.

Assuming a simple Consumers table with five fields:

Id(Int), 
DisplayName(NVarchar(128))
ImageUrl(NVarchar(128))
Gender(Int)
SignedUpOn(DateTimeOffset)

If we run this query:

Consumers.GroupBy(c => c.Gender).ToDictionary(g => g.Key, g => g.Count())

it will generate this SQL code:

SELECT [t0].[Gender] AS [Key]
FROM [Consumers] AS [t0]
GROUP BY [t0].[Gender]
GO
DECLARE @x1 Int = 1
SELECT [t0].[Id], [t0].[DisplayName], [t0].[ImageUrl], [t0].[Gender], [t0].[SignedUpOn]
FROM [Consumers] AS [t0]
WHERE @x1 = [t0].[Gender]
GO
DECLARE @x1 Int = 2
SELECT [t0].[Id], [t0].[DisplayName], [t0].[ImageUrl], [t0].[Gender], [t0].[SignedUpOn]
FROM [Consumers] AS [t0]
WHERE @x1 = [t0].[Gender]

which is quite yucky. But the original query form:

Consumers.GroupBy(c => c.Gender)
 .Select(g => new { Key = g.Key, Count = g.Count() })
 .ToDictionary(g => g.Key, g => g.Count)

will generate this SQL:

SELECT COUNT(*) AS [Count], [t0].[Gender] AS [Key]
FROM [Consumers] AS [t0]
GROUP BY [t0].[Gender]

which is pretty close to what you would write by hand.

• sDima: This is not effective, because SQL Server gets all columns of table.
• corvuscorax: You're right - I have updated the answer to illustrate.