appelgren

Hopefully I’m not the only one having trouble with aggregates, Linq to SQL and empty results. The query I had was something like the following.

var result = (from v in db.Table select v.IntColumn).Max();

This fails with an exception if Table is empty.

The null value cannot be assigned to a member with type
System.Int32 which is a non-nullable value type.

So after some reading on MSDN I found the extension method Queryable.DefaultIfEmpty, it returns a collection with one element that has the default value of the type of the IQueryable<T> if the IQueryable<T> is empty, that looked like it could solve my problem. But I quickly found out that it is not supported by Linq to SQL.

Could not format node 'OptionalValue' for execution as SQL.

So then after reading a blog post about this I ended up with the following query that does what I want.

var result = (from v in db.Table select (int?)v.IntColumn).Max()
    ?? 0;

It has the side effect that the generated SQL query contains an extra nested select but I think that is acceptable.

SELECT MAX([t1].[VALUE]) AS [VALUE]
FROM (
    SELECT [t0].[IntValue] AS [VALUE]
    FROM [TABLE] AS [t0]
) AS [t1]