我正在尝试创建类似于以下查询的查询:
select randomId
from myView
where ...
group by randomId
注意:EF不支持distinct,因此我考虑用group by绕过它(或者我这么认为)
randomId是数字
Entity Framework V.6.0.2
这个查询在不到1秒的时间内给出了我期望的结果
当尝试使用EF执行相同操作时,我遇到了一些问题。
如果我使用类似于此的LINQ:
context.myView
.Where(...)
.GroupBy(mt => mt.randomId)
.Select({ Id = group.Key, Count = group.Count() } )
我将得到类似的结果,但是通过强制使用count并使查询时间超过6秒。
EF生成的SQL大致如下:
SELECT
1 AS [C1],
[GroupBy1].[K1] AS [randomId],
[GroupBy1].[A1] AS [C2]
FROM (
SELECT
[Extent1].[randomId] AS [K1],
COUNT(1) AS [A1]
FROM [dbo].[myView] AS [Extent1]
WHERE (...)
GROUP BY [Extent1].[randomId]
) AS [GroupBy1]
但是,如果查询中注释掉了 count 部分,执行时间会降至小于 1 秒。
如果我将 Select 更改为以下内容:
.Select({ Id = group.Key} )
在SQL查询中,我将获取所有没有group by语句和无需Distinct的行:
SELECT
[Extent1].[anotherField] AS [anotherField], -- 'this field got included automatically on this query and I dont know why, it doesnt affect outcome when removed in SQL server'
[Extent1].[randomId] AS [randomId]
FROM [dbo].[myView] AS [Extent1]
WHERE (...)
其他失败的尝试:
query.GroupBy(x => x.randomId).Select(group => group.FirstOrDefault());
生成的查询如下所示:
SELECT
[Limit1].ALL FIELDS,...
FROM (SELECT
[Extent1].[randomId] AS [randomId]
FROM [dbo].[myView] AS [Extent1]
WHERE (...) AS [Project1]
OUTER APPLY (SELECT TOP (1)
[Extent2].ALL FIELDS,...
FROM [dbo].[myView] AS [Extent2]
WHERE (...) AS [Limit1] -- same as the where above
这个查询表现不佳,却仍然成功返回了where子句的所有ID。
有没有人有办法强制使用group by而不需要像count这样的聚合函数?
在SQL中它可以工作,但我也使用了distinct关键字...
干杯, J
[anotherField]
对我来说看起来很奇怪。请确保应用了分组。 - Sergey Berezovskiy