C# 最大还是默认？

Max or Default?

提问by gfrizzle

What is the best way to get the Max value from a LINQ query that may return no rows? If I just do

从可能不返回任何行的 LINQ 查询中获取最大值的最佳方法是什么？如果我只是这样做

Dim x = (From y In context.MyTable _
         Where y.MyField = value _
         Select y.MyCounter).Max

I get an error when the query returns no rows. I could do

当查询不返回任何行时，我收到错误消息。我可以

Dim x = (From y In context.MyTable _
         Where y.MyField = value _
         Select y.MyCounter _
         Order By MyCounter Descending).FirstOrDefault

but that feels a little obtuse for such a simple request. Am I missing a better way to do it?

但是对于这样一个简单的请求，这感觉有点迟钝。我错过了更好的方法吗？

UPDATE: Here's the back story: I'm trying to retrieve the next eligibility counter from a child table (legacy system, don't get me started...). The first eligibility row for each patient is always 1, the second is 2, etc. (obviously this is not the primary key of the child table). So, I'm selecting the max existing counter value for a patient, and then adding 1 to it to create a new row. When there are no existing child values, I need the query to return 0 (so adding 1 will give me a counter value of 1). Note that I don't want to rely on the raw count of child rows, in case the legacy app introduces gaps in the counter values (possible). My bad for trying to make the question too generic.

更新：这是背景故事：我正在尝试从子表中检索下一个资格计数器（旧系统，不要让我开始......）。每个患者的第一个合格行始终为 1，第二个为 2，依此类推（显然这不是子表的主键）。因此，我为患者选择最大现有计数器值，然后将其加 1 以创建新行。当没有现有的子值时，我需要查询返回 0（所以加 1 会给我一个计数器值 1）。请注意，我不想依赖子行的原始计数，以防旧应用程序在计数器值中引入间隙（可能）。我试图使问题过于笼统。

采纳答案by Jacob Proffitt

Since DefaultIfEmptyisn't implemented in LINQ to SQL, I did a search on the error it returned and found a fascinating articlethat deals with null sets in aggregate functions. To summarize what I found, you can get around this limitation by casting to a nullable within your select. My VB is a little rusty, but I thinkit'd go something like this:

由于DefaultIfEmpty没有在 LINQ to SQL 中实现，我搜索了它返回的错误并找到了一篇有趣的文章，该文章处理聚合函数中的空集。总结一下我发现的内容，您可以通过在选择中强制转换为可空值来解决此限制。我的 VB 有点生疏，但我认为它会是这样的：

Dim x = (From y In context.MyTable _
         Where y.MyField = value _
         Select CType(y.MyCounter, Integer?)).Max

Or in C#:

或者在 C# 中：

var x = (from y in context.MyTable
         where y.MyField == value
         select (int?)y.MyCounter).Max();

回答by David Schmitt

You could always add Double.MinValueto the sequence. This would ensure that there is at least one element and Maxwould return it only if it is actually the minimum. To determine which option is more efficient (Concat, FirstOrDefaultor Take(1)), you should perform adequate benchmarking.

您可以随时添加Double.MinValue到序列中。这将确保至少有一个元素，并且Max只有当它实际上是最小值时才会返回它。要确定哪个选项更有效（Concat，FirstOrDefault或Take(1)），您应该执行适当的基准测试。

double x = context.MyTable
    .Where(y => y.MyField == value)
    .Select(y => y.MyCounter)
    .Concat(new double[]{Double.MinValue})
    .Max();

回答by Rex Miller

One interesting difference that seems worth noting is that while FirstOrDefault and Take(1) generate the same SQL (according to LINQPad, anyway), FirstOrDefault returns a value--the default--when there are no matching rows and Take(1) returns no results... at least in LINQPad.

一个值得注意的有趣区别是，虽然 FirstOrDefault 和 Take(1) 生成相同的 SQL（无论如何，根据 LINQPad），FirstOrDefault 返回一个值——默认值——当没有匹配的行并且 Take(1) 返回时没有结果……至少在 LINQPad 中。

回答by Jacob Proffitt

Sounds like a case for DefaultIfEmpty(untested code follows):

听起来像一个案例DefaultIfEmpty（未经测试的代码如下）：

Dim x = (From y In context.MyTable _
         Where y.MyField = value _
         Select y.MyCounter).DefaultIfEmpty.Max

回答by Rex Miller

Another possibility would be grouping, similar to how you might approach it in raw SQL:

另一种可能性是分组，类似于您在原始 SQL 中处理它的方式：

from y in context.MyTable
group y.MyCounter by y.MyField into GrpByMyField
where GrpByMyField.Key == value
select GrpByMyField.Max()

The only thing is (testing again in LINQPad) switching to the VB LINQ flavor gives syntax errors on the grouping clause. I'm sure the conceptual equivalent is easy enough to find, I just don't know how to reflect it in VB.

唯一的问题是（在 LINQPad 中再次测试）切换到 VB LINQ 风格会导致分组子句出现语法错误。我确信概念上的等价物很容易找到，我只是不知道如何在 VB 中反映它。

The generated SQL would be something along the lines of:

生成的 SQL 将类似于以下内容：

SELECT [t1].[MaxValue]
FROM (
    SELECT MAX([t0].[MyCounter) AS [MaxValue], [t0].[MyField]
    FROM [MyTable] AS [t0]
    GROUP BY [t0].[MyField]
    ) AS [t1]
WHERE [t1].[MyField] = @p0

The nested SELECT looks icky, like the query execution would retrieve all rows then select the matching one from the retrieved set... the question is whether or not SQL Server optimizes the query into something comparable to applying the where clause to the inner SELECT. I'm looking into that now...

嵌套的 SELECT 看起来很糟糕，就像查询执行会检索所有行，然后从检索到的集合中选择匹配的行……问题是 SQL Server 是否将查询优化为类似于将 where 子句应用于内部 SELECT 的内容。我现在正在研究这个...

I'm not well-versed in interpreting execution plans in SQL Server, but it looks like when the WHERE clause is on the outer SELECT, the number of actual rows resulting in that step is all rows in the table, versus only the matching rows when the WHERE clause is on the inner SELECT. That said, it looks like only 1% cost is shifted to the following step when all rows are considered, and either way only one row ever comes back from the SQL Server so maybe it's not that big of a difference in the grand scheme of things.

我不精通解释 SQL Server 中的执行计划，但看起来当 WHERE 子句位于外部 SELECT 时，导致该步骤的实际行数是表中的所有行，而只有匹配的行当 WHERE 子句位于内部 SELECT 时。也就是说，当考虑所有行时，看起来只有 1% 的成本会转移到以下步骤，而且无论哪种方式，只有一行从 SQL Server 返回，所以在总体方案中可能没有那么大的差异.

回答by tvanfosson

I think the issue is what do you want to happen when the query has no results. If this is an exceptional case then I would wrap the query in a try/catch block and handle the exception that the standard query generates. If it's ok to have the query return no results, then you need to figure out what you want the result to be in that case. It may be that @David's answer (or something similar will work). That is, if the MAX will always be positive, then it may be enough to insert a known "bad" value into the list that will only be selected if there are no results. Generally, I would expect a query that is retrieving a maximum to have some data to work on and I would go the try/catch route as otherwise you are always forced to check if the value you obtained is correct or not. I'd rather that the non-exceptional case was just able to use the obtained value.

我认为问题是当查询没有结果时你想发生什么。如果这是一个例外情况，那么我会将查询包装在 try/catch 块中并处理标准查询生成的异常。如果可以让查询不返回任何结果，那么您需要弄清楚在这种情况下您想要的结果是什么。可能是@David 的答案（或类似的东西会起作用）。也就是说，如果 MAX 始终为正数，那么将已知的“坏”值插入列表中可能就足够了，只有在没有结果时才会选择该值。一般来说，我希望检索最大值的查询有一些数据可以处理，我会走 try/catch 路线，否则你总是被迫检查你获得的值是否正确。一世'

Try
   Dim x = (From y In context.MyTable _
            Where y.MyField = value _
            Select y.MyCounter).Max
   ... continue working with x ...
Catch ex As SqlException
       ... do error processing ...
End Try

回答by yfeldblum

Think about what you're asking!

想想你在问什么！

The max of {1, 2, 3, -1, -2, -3} is obviously 3. The max of {2} is obviously 2. But what is the max of the empty set { }? Obviously that is a meaningless question. The max of the empty set is simply not defined. Attempting to get an answer is a mathematical error. The max of any set must itself be an element in that set. The empty set has no elements, so claiming that some particular number is the max of that set without being in that set is a mathematical contradiction.

{1, 2, 3, -1, -2, -3} 的最大值显然是 3。 {2} 的最大值显然是 2。但是空集 { } 的最大值是多少？显然，这是一个毫无意义的问题。空集的最大值根本没有定义。试图得到答案是一个数学错误。任何集合的最大值本身必须是该集合中的一个元素。空集没有元素，因此声称某个特定数字是该集合的最大值而不在该集合中是数学上的矛盾。

Just as it is correct behavior for the computer to throw an exception when the programmer asks it to divide by zero, so it is correct behavior for the computer to throw an exception when the programmer asks it to take the max of the empty set. Division by zero, taking the max of the empty set, wiggering the spacklerorke, and riding the flying unicorn to Neverland are all meaningless, impossible, undefined.

正如当程序员要求计算机除以零时计算机抛出异常是正确的行为一样，当程序员要求计算机取空集的最大值时计算机抛出异常也是正确的行为。除以零，取空集的最大值，摇摆spacklerorke，骑飞独角兽到梦幻岛都是毫无意义的，不可能的，未定义的。

Now, what is it that you actuallywant to do?

现在，你真正想做的是什么？

回答by Dom Ribaut

litt late, but I had the same concern...

有点晚了，但我也有同样的担忧......

Rephrasing your code from the original post, you want the max of the set S defined by

从原始帖子改写您的代码，您需要由定义的集合 S 的最大值

(From y In context.MyTable _
 Where y.MyField = value _
 Select y.MyCounter)

Taking in account your last comment

考虑到你最后的评论

Suffice to say that I know I want 0 when there are no records to select from, which definitely has an impact on the eventual solution

可以说我知道当没有可供选择的记录时我想要 0，这肯定会对最终解决方案产生影响

I can rephrase your problem as: You want the max of {0 + S}. And it looks like the proposed solution with concat is semantically the right one :-)

我可以将您的问题重新表述为：您想要 {0 + S} 的最大值。看起来提出的 concat 解决方案在语义上是正确的 :-)

var max = new[]{0}
          .Concat((From y In context.MyTable _
                   Where y.MyField = value _
                   Select y.MyCounter))
          .Max();

回答by Nix

Just to let everyone out there know that is using Linq to Entities the methods above will not work...

只是为了让每个人都知道使用 Linq to Entities 上面的方法不起作用......

If you try to do something like

如果你尝试做类似的事情

var max = new[]{0}
      .Concat((From y In context.MyTable _
               Where y.MyField = value _
               Select y.MyCounter))
      .Max();

It will throw an exception:

它会抛出异常：

System.NotSupportedException: The LINQ expression node type 'NewArrayInit' is not supported in LINQ to Entities..

System.NotSupportedException：LINQ to Entities 不支持 LINQ 表达式节点类型“NewArrayInit”。

I would suggest just doing

我建议只是做

(From y In context.MyTable _
                   Where y.MyField = value _
                   Select y.MyCounter))
          .OrderByDescending(x=>x).FirstOrDefault());

And the FirstOrDefaultwill return 0 if your list is empty.

FirstOrDefault如果您的列表为空，它将返回 0。

回答by Eddie Deyo

I just had a similar problem, but I was using LINQ extension methods on a list rather than query syntax. The casting to a Nullable trick works there as well:

我刚刚遇到了类似的问题，但我在列表上使用了 LINQ 扩展方法，而不是查询语法。对 Nullable 技巧的转换也适用于：

int max = list.Max(i => (int?)i.MyCounter) ?? 0;