如何在实体框架中编写这个SQL查询?

I have this query that I want translated pretty much 1:1 from Entity Framework to SQL:

我想要将这个查询从Entity Framework转换成SQL:

SELECT GroupId, ItemId, count(*) as total
  FROM [TESTDB].[dbo].[TestTable]
  WHERE GroupId = '64025'
  GROUP BY GroupId, ItemId
  ORDER BY GroupId, total DESC

This SQL query should sort based on the number occurrence of the same ItemId (for that group).

这个SQL查询应该基于同一项id的出现次数(对于该组)进行排序。

I have this now:

我现在有这个:

from x in dataContext.TestTable.AsNoTracking()
where x.GroupId = 64025
group x by new {x.GroupId, x.ItemId}
into g
orderby g.Key.GroupId, g.Count() descending 
select new {g.Key.GroupId, g.Key.ItemId, Count = g.Count()};

But this generates the following SQL code:

但这会生成以下SQL代码:

SELECT 
  [GroupBy1].[K1] AS [GroupId], 
  [GroupBy1].[K2] AS [ItemId], 
  [GroupBy1].[A2] AS [C1]
FROM ( SELECT 
         [Extent1].[GroupId] AS [K1], 
         [Extent1].[ItemId] AS [K2], 
         COUNT(1) AS [A1], 
         COUNT(1) AS [A2]
       FROM [dbo].[TestTable] AS [Extent1]
       WHERE 64025 = [Extent1].[GroupId]
       GROUP BY [Extent1].[GroupId], [Extent1].[ItemId]
     )  AS [GroupBy1]
ORDER BY [GroupBy1].[K1] ASC, [GroupBy1].[A1] DESC

This also works but is a factor 2 slower than the SQL I created.

这也很有效，但是比我创建的SQL慢了一倍。

I've been fiddling around with the linq code for a while but I haven't managed to create something similar to my query.

我摆弄linq代码已经有一段时间了，但是我还没有创建类似于我的查询的东西。

Execution plan (only the last two items, the first two are identical):

执行计划(仅最后两项，前两项相同):

FIRST:   |--Stream Aggregate(GROUP BY:([Extent1].[ItemId]) DEFINE:([Expr1006]=Count(*), [Extent1].[GroupId]=ANY([TESTDB].[dbo].[TestTable].[GroupId] as [Extent1].[GroupId])))
           |--Index Seek(OBJECT:([TESTDB].[dbo].[TestTable].[IX_Group]), SEEK:([TESTDB].[dbo].[TestTable].[GroupId]=(64034)) ORDERED FORWARD)

SECOND:  |--Stream Aggregate(GROUP BY:([TESTDB].[dbo].[TestTable].[ItemId]) DEFINE:([Expr1007]=Count(*), [TESTDB].[dbo].[TestTable].[GroupId]=ANY([TESTDB].[dbo].[TestTable].[GroupId])))
           |--Index Seek(OBJECT:([TESTDB].[dbo].[TestTable].[IX_Group] AS [Extent1]), SEEK:([Extent1].[GroupId]=(64034)) ORDERED FORWARD)

1 个解决方案

#1

The query that Entity Framework generates and your hand crafted query are semantically the same and will give the same plan.

实体框架生成的查询和手工编写的查询在语义上是相同的，并且会给出相同的计划。

The derived table definition is inlined during query optimisation so the only difference might be some extremely minor additional overhead during parsing and compilation.

派生的表定义是在查询优化过程中内联的，因此惟一的区别可能是解析和编译过程中一些极小的额外开销。

The snippets of SHOWPLAN_TEXT you have posted are the same plan. The only difference is aliases. It looks as though your table definition is something like.