Max or Default?

Question

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

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.

Answer 1

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

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

Answer 2

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:

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