获得最大&实体框架中的分钟数，一次查询即可获得最佳查询

本文介绍了获得最大&实体框架中的分钟数，一次查询即可获得最佳查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我知道这个问题，但是我想做的就是获取一些与此生成的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]