使用 Linq to SQL，如何找到表中列的最小值和最大值?

问题描述

我想找到通过单个 Linq 到 SQL 往返获取表中列的最小值和最大值的最快方法.所以我知道这将在两次往返中起作用:

I want to find the fastest way to get the min and max of a column in a table with a single Linq to SQL roundtrip. So I know this would work in two roundtrips:

int min = MyTable.Min(row => row.FavoriteNumber);
int max = MyTable.Max(row => row.FavoriteNumber);

我知道我可以使用 group 但我没有 group by 子句，我想聚合整个表！而且我不能在不先分组的情况下使用 .Min.我确实尝试过:

I know I can use group but I don't have a group by clause, I want to aggregate over the whole table! And I can't use the .Min without grouping first. I did try this:

from row in MyTable
group row by true into r
select new {
    min = r.Min(z => z.FavoriteNumber),
    max = r.Max(z => z.FavoriteNumber)
}

但是那个疯狂的 group 子句看起来很傻，而且它生成的 SQL 比它需要的更复杂.

But that crazy group clause seems silly, and the SQL it makes is more complex than it needs to be.

那么，有什么办法可以得到正确的 SQL 吗?

So, is there any way to just get the correct SQL out?

这些人也失败了:Linq toSQL:如何在没有 group by 的情况下进行聚合? ...如果真的没有答案，LINQ 设计者的疏忽.

These guys failed too: Linq to SQL: how to aggregate without a group by? ... lame oversight by LINQ designers if there's really no answer.

编辑 2:我在 SQL Server Management Studio 执行计划分析中查看了我自己的解决方案(使用无意义的常量 group by 子句)，在我看来它与以下生成的计划相同:

EDIT 2: I looked at my own solution (with the nonsensical constant group by clause) in the SQL Server Management Studio execution plan analysis, and it looks to me like it is identical to the plan generated by:

SELECT MIN(FavoriteNumber), MAX(FavoriteNumber)
FROM MyTable

所以除非有人能想出一个更简单或同样好的答案，否则我想我必须将其标记为自己回答.想法?

so unless someone can come up with a simpler-or-equally-as-good answer, I think I have to mark it as answered-by-myself. Thoughts?

推荐答案

正如问题中所述，这种方法似乎实际上生成了最佳的 SQL 代码，所以虽然它在 LINQ 中看起来有点古怪，但它应该是最佳的性能.

As stated in the question, this method seems to actually generate optimal SQL code, so while it looks a bit squirrely in LINQ, it should be optimal performance-wise.

from row in MyTable
group row by true into r
select new {
    min = r.Min(z => z.FavoriteNumber),
    max = r.Max(z => z.FavoriteNumber)
}

这篇关于使用 Linq to SQL，如何找到表中列的最小值和最大值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！