LINQ Max Value When No Rows Are Returned

by Robert 8. July 2011 16:22

I ran into an issue today where I was trying to get the max value for a column using LINQ to SQL but was getting an exception when no rows were returned.  I have a table of "Questions" and one of its columns is "DisplayOrder", which is an int field that allows for sorting on the front end.  When a new Question is added, I want it to show up last in the sorted list, so I want to insert the new record with a DisplayOrder that is the current maximum value + 1 (or with a DisplayOrder of 1 if it will be the first record).  My first attempt at getting the maximum value was:

int? currentMaxDisplayOrder = dc.Questions.Where(q => !q.IsDeleted).Max(q => q.DisplayOrder);

This worked fine when there were existing records but threw an exception when there were no matching questions.  Searching the internet provided me with guidance for a solution.  With that, I changed my LINQ statement to:

int? currentMaxDisplayOrder = dc.Questions.Where(q => !q.IsDeleted).Select(q => (int?)q.DisplayOrder).Max();
....
// And the new record's DisplayOrder
question.DisplayOrder = currentMaxDisplayOrder.GetValueOrDefault(0) + 1;

And FYI...the LINQ statement above evaluates to the following SQL

SELECT MAX([t1].[value]) AS [value]
FROM (
    SELECT [t0].[DisplayOrder] AS [value], [t0].[IsDeleted]
    FROM [Question] AS [t0]
    ) AS [t1]
WHERE NOT ([t1].[IsDeleted] = 1)

Add comment




  Country flag
biuquote
  • Comment
  • Preview
Loading


Tag cloud