Problem
What’s the easiest approach to retrieve the maximum value from a LINQ query that may or may not return any results? If I only do this,
Dim x = (From y In context.MyTable _
Where y.MyField = value _
Select y.MyCounter).Max
When the query yields no results, I get an error. I believe I am capable.
Dim x = (From y In context.MyTable _
Where y.MyField = value _
Select y.MyCounter _
Order By MyCounter Descending).FirstOrDefault
However, for such a straightforward request, that seems a little obtuse. Is there a better way to accomplish it?
UPDATE: Here’s the backstory: I’m trying to extract the next eligibility counter from a child table (don’t get me started on old systems…). Each patient’s first eligibility row is always 1, the second is always 2, and so on (obviously this is not the primary key of the child table). So I’m selecting a patient’s maximum existing counter value and then adding 1 to it to make a new row. I need the query to return 0 when there are no existing child values (so adding 1 will give me a counter value of 1). I don’t want to rely on the raw number of child rows because the legacy app might produce gaps in the counter data (possible). My apologies.
Asked by gfrizzle
Solution #1
Because DefaultIfEmpty isn’t implemented in LINQ to SQL, I looked up the error it returned and came on an interesting article about null sets in aggregate functions. To recap my findings, you may circumvent this restriction by casting to a nullable within your select. My VB is rusty, but I believe it would go something like this:
Dim x = (From y In context.MyTable _
Where y.MyField = value _
Select CType(y.MyCounter, Integer?)).Max
Or in C#:
var x = (from y in context.MyTable
where y.MyField == value
select (int?)y.MyCounter).Max();
Answered by Jacob Proffitt
Solution #2
I just ran into a similar issue, but instead of query syntax, I was using LINQ extension methods on a list. The trick of casting to a Nullable also works there:
int max = list.Max(i => (int?)i.MyCounter) ?? 0;
Answered by Eddie Deyo
Solution #3
Sounds like DefaultIfEmpty is in order (untested code follows):
Dim x = (From y In context.MyTable _
Where y.MyField = value _
Select y.MyCounter).DefaultIfEmpty.Max
Answered by Jacob Proffitt
Solution #4
Consider what you’re asking!
Obviously, the maximum of 1, 2, 3, -1, -2, -3 is 3. The maximum value of 2 is, of course, 2. But what is the empty set’s maximum value? Clearly, that is a pointless inquiry. The empty set’s maximum value is simply unknown. It is a mathematical error to try to find a solution. Any set’s maximum value must be one of its elements. Because the empty set has no elements, saying that a certain number is the maximum of that set without being a member of it is a mathematical contradiction.
The computer should throw an exception when the programmer asks it to divide by zero, just as it should throw an exception when the programmer asks it to take the maximum of the empty set. Division by zero, taking the maximum of an empty set, wiggling the spacklerorke, and going to Neverland on a unicorn are all meaningless, impossible, and undefined.
So, what exactly do you want to do now?
Answered by yfeldblum
Solution #5
Double.MinValue might always be added to the sequence. This ensures that there is at least one element, with Max returning it only if it is the smallest. You should undertake appropriate benchmarking to determine which option is more efficient (Concat, FirstOrDefault, or Take(1)).
double x = context.MyTable
.Where(y => y.MyField == value)
.Select(y => y.MyCounter)
.Concat(new double[]{Double.MinValue})
.Max();
Answered by David Schmitt
Post is based on https://stackoverflow.com/questions/341264/max-or-default