获得最大&实体框架中的分钟数,一次查询即可获得最佳查询 [英] Get max & min from Entity Framework, in one query and with best query possible
本文介绍了获得最大&实体框架中的分钟数,一次查询即可获得最佳查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我知道这个问题,但是我想做的就是获取一些与此生成的SQL相似的东西:
I'm aware of this question, but what I would like to do is obtain something close to this generated SQL:
select MAX(Column), MIN(Column) from Table WHERE Id = 1
当我尝试此操作时:
var query = from d in db.Table
where d.Id == 1
select new
{
min = db.Table.Max(s => s.Column),
max = db.Table.Min(s => s.Column)
};
生成的sql如下所示:
The generated sql looks like this:
SELECT
[Extent1].[Id] AS [Id],
[GroupBy1].[A1] AS [C1],
[GroupBy2].[A1] AS [C2]
FROM [dbo].[Table] AS [Extent1]
CROSS JOIN (SELECT
MAX([Extent2].[Column]) AS [A1]
FROM [dbo].[Table] AS [Extent2] ) AS [GroupBy1]
CROSS JOIN (SELECT
MIN([Extent3].[Column]) AS [A1]
FROM [dbo].[Table] AS [Extent3] ) AS [GroupBy2]
WHERE ([Extent1].[Id] = 1) AND (1 IS NOT NULL)
我也尝试过:
var query = from d in db.Table
where d.Id == 1
group d by d.Id into grp
let min = grp.Min(s => s.Column)
let max = grp.Max(s => s.Column)
select new { min, max };
哪个给这个:
SELECT
[Project2].[Id] AS [Id],
[Project2].[C1] AS [C1],
[Project2].[C2] AS [C2]
FROM ( SELECT
[Project1].[C1] AS [C1],
[Project1].[Id] AS [Id],
(SELECT
MAX([Extent2].[Column]) AS [A1]
FROM [dbo].[Table] AS [Extent2]
WHERE ([Extent2].[Id] = 1) AND (1 IS NOT NULL) AND ([Project1].[Id] = [Extent2].[Id])) AS [C2]
FROM ( SELECT
[GroupBy1].[A1] AS [C1],
[GroupBy1].[K1] AS [Id]
FROM ( SELECT
[Extent1].[Id] AS [K1],
MIN([Extent1].[Column]) AS [A1]
FROM [dbo].[Table] AS [Extent1]
WHERE ([Extent1].[Id] = 16) AND (16 IS NOT NULL)
GROUP BY [Extent1].[Id]
) AS [GroupBy1]
) AS [Project1]
) AS [Project2]
它们都起作用,并且性能影响几乎可以忽略不计,因此主要是美学上的:
生成的两个查询都伤害了我的眼睛.
They both work, and the performance hit is probably negligible, so it's mostly aesthetic:
The two generated queries both hurt my eyes.
推荐答案
尝试删除let
语句-以下将产生预期的结果:
Try removing the let
statements - the below produces expected results:
var q = from d in db.Table
where d.Id == 1
group d by d.Id into g
select new
{
Id = g.Key, // shown for illustrative purposes
ColumnMin = g.Min( gi => gi.Column ),
ColumnMax = g.Max( gi => gi.Column )
};
var result = q.SingleOrDefault();
产生的SQL:
SELECT
[GroupBy1].[K1] AS [Id],
[GroupBy1].[A1] AS [C1],
[GroupBy1].[A2] AS [C2]
FROM ( SELECT
[Extent1].[Id] AS [K1],
MIN([Extent1].[Column]) AS [A1],
MAX([Extent1].[Column]) AS [A2]
FROM [dbo].[Table] AS [Extent1]
WHERE 1 = [Extent1].[Id]
GROUP BY [Extent1].[Id]
) AS [GroupBy1]
这篇关于获得最大&实体框架中的分钟数,一次查询即可获得最佳查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文