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.
