5
\$\begingroup\$

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?

\$\endgroup\$
2
  • \$\begingroup\$ It looks like the GroupBy is causing it... are you having performance issues? Missing index on Status? \$\endgroup\$ Dec 21, 2014 at 19:19
  • \$\begingroup\$ No, I haven't performance issues. I thought that this query can be more simpler. \$\endgroup\$
    – sDima
    Dec 21, 2014 at 20:21

2 Answers 2

6
\$\begingroup\$

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);
\$\endgroup\$
4
\$\begingroup\$

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.

\$\endgroup\$
2
  • \$\begingroup\$ This is not effective, because SQL Server gets all columns of table. \$\endgroup\$
    – sDima
    Jan 30, 2015 at 14:02
  • 1
    \$\begingroup\$ You're right - I have updated the answer to illustrate. \$\endgroup\$ Jan 30, 2015 at 15:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.