C# 使用 Linq to SQL，如何找到表中列的最小值和最大值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2268175/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using Linq to SQL, how do I find min and max of a column in a table?
提问by Scott Stafford
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:
我想找到通过单个 Linq to SQL 往返获取表中列的最小值和最大值的最快方法。所以我知道这将在两次往返中起作用：
int min = MyTable.Min(row => row.FavoriteNumber);
int max = MyTable.Max(row => row.FavoriteNumber);
I know I can use groupbut I don't have a group byclause, I want to aggregate over the whole table! And I can't use the .Min without grouping first. I did try this:
我知道我可以使用group但我没有group by子句，我想聚合整个表！而且我不能在不先分组的情况下使用 .Min。我确实尝试过这个：
from row in MyTable
group row by true into r
select new {
min = r.Min(z => z.FavoriteNumber),
max = r.Max(z => z.FavoriteNumber)
}
But that crazy group clause seems silly, and the SQL it makes is more complex than it needs to be.
但是那个疯狂的 group 子句似乎很愚蠢，而且它生成的 SQL 比它需要的更复杂。
So, is there any way to just get the correct SQL out?
那么，有什么办法可以得到正确的 SQL 吗？
EDIT: 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.
编辑：这些人也失败了：Linq to SQL：如何在没有分组的情况下进行聚合？...如果真的没有答案，LINQ 设计师的疏忽。
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:
编辑 2：我在 SQL Server Management Studio 执行计划分析中查看了我自己的解决方案（使用无意义的常量 group 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?
因此，除非有人能想出一个更简单或同样好的答案，否则我想我必须将其标记为自己回答。想法？
采纳答案by Scott Stafford
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.
正如问题中所述，此方法似乎实际上生成了最佳 SQL 代码，因此虽然它在 LINQ 中看起来有点古怪，但它应该是最佳性能。
from row in MyTable
group row by true into r
select new {
min = r.Min(z => z.FavoriteNumber),
max = r.Max(z => z.FavoriteNumber)
}
回答by cllpse
You could select the whole table, and do your min and max operations in memory:
您可以选择整个表，并在内存中执行最小和最大操作：
var cache = // select *
var min = cache.Min(...);
var max = cache.Max(...);
Depending on how large your dataset is, this might be the way to go about not hitting your database more than once.
根据您的数据集有多大，这可能是避免多次访问数据库的方法。
回答by Bryan Watts
A LINQ to SQL query is a single expression. Thus, if you can't express your query in a single expression (or don't like it once you do) then you have to look at other options.
LINQ to SQL 查询是单个表达式。因此，如果您不能用单个表达式表达您的查询（或者一旦您不喜欢它），那么您必须查看其他选项。
Stored procedures, since they can have statements, enable you to accomplish this in a single round-trip. You will either have two output parameters or select a result set with two rows. Either way, you will need custom code to read the stored procedure's result.
存储过程，因为它们可以有语句，使您能够在一次往返中完成此操作。您将有两个输出参数或选择一个包含两行的结果集。无论哪种方式，您都需要自定义代码来读取存储过程的结果。
(I don't personally see the need to avoid two round-trips here. It seems like a premature optimization, especially since you will probably have to jump through hoops to get it working. Not to mention the time you will spend justifying this decision and explaining the solution to other developers.)
（我个人不认为有必要在这里避免两次往返。这似乎是一个过早的优化，特别是因为您可能不得不跳过箍以使其工作。更不用说您将花费的时间来证明这个决定的合理性并向其他开发人员解释解决方案。）
Put another way: you've already answered your own question. "I can't use the .Min without grouping first", followed by "that crazy group clause seems silly, and the SQL it makes is more complex than it needs to be", are clues that the simple and easily-understood two-round-trip solution is the best expression of your intent (unless you write custom SQL).
换句话说：你已经回答了你自己的问题。“我不能在没有分组的情况下使用 .Min”，然后是“那个疯狂的 group 子句看起来很傻，而且它制作的 SQL 比它需要的更复杂”，是简单易懂的两个线索——往返解决方案是您意图的最佳表达（除非您编写自定义 SQL）。
回答by Matt Ellen
I'm not sure how to translate it into C# yet (I'm working on it)
我还不确定如何将它翻译成 C#（我正在研究它）
This is the Haskell version
这是 Haskell 版本
minAndMax :: Ord a => [a] -> (a,a)
minAndMax [x] = (x,x)
minAndMax (x:xs) = (min a x, max b x)
where (a,b) = minAndMax xs
The C# version should involve Aggregatesome how (I think).
C# 版本应该涉及Aggregate一些如何（我认为）。
回答by dh.
I could find only this one which produces somewhat clean sql still not really effective comparing to select min(val), max(val) from table:
与从表中选择 min(val), max(val) 相比，我只能找到这个产生一些干净的 sql 仍然不是真正有效的：
var r =
(from min in items.OrderBy(i => i.Value)
from max in items.OrderByDescending(i => i.Value)
select new {min, max}).First();
the sql is
sql是
SELECT TOP (1)
[t0].[Value],
[t1].[Value] AS [Value2]
FROM
[TestTable] AS [t0],
[TestTable] AS [t1]
ORDER BY
[t0].[Value],
[t1].[Value] DESC
still there is another option to use single connection for both min and max queries (see Multiple Active Result Sets (MARS))
还有另一个选项可以为最小和最大查询使用单个连接（请参阅多个活动结果集（MARS））
or stored procedure..
或存储过程..
