Linq to SQL, aggregates and empty results

Posted on by
Warning: WP_Syntax::substituteToken(): Argument #1 ($match) must be passed by reference, value given in /customers/c/b/1/cactcxjko/webroots/www/blog/wp-content/plugins/wp-syntax/wp-syntax.php on line 380 Warning: WP_Syntax::substituteToken(): Argument #1 ($match) must be passed by reference, value given in /customers/c/b/1/cactcxjko/webroots/www/blog/wp-content/plugins/wp-syntax/wp-syntax.php on line 380 Warning: WP_Syntax::substituteToken(): Argument #1 ($match) must be passed by reference, value given in /customers/c/b/1/cactcxjko/webroots/www/blog/wp-content/plugins/wp-syntax/wp-syntax.php on line 380

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]

28 thoughts on “Linq to SQL, aggregates and empty results”

  1. Dude,

    Thank you so much you have just saved my time. Probably i would be spending few hours in some problem very similar to yours.

    🙂

  4. Only the third or fourth time I’ve looked this up. I’ll be back again as soon as I forget why I need this.
    Thank you.

  5. A better solution is this, as it does’t interfere with the type:

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

  6. Thanks. This post quickly helped me with the same problem. However, I could not get the DefaultIfEmpty() suggested by Fil to work. It gave some error like: “Could not format node ‘OptionalValue’ for execution as SQL.” I was working against a LinqToSql context, maybe that is why. However, the original solution in the blog post worked just fine. I then had to test the return value for null, and I was fine.

  8. does this work with a relationship such as…

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

    If Table2 has no results

  9. Thanks for the post! It helped me alot, I did not know that IQueryable.DefaultIfEmpty() was not supported by LinqToSql

  12. You saved me a great deal of time(had burnt out my brains trying to solve the problem). Cheers!

Leave a Reply

Your email address will not be published. Required fields are marked *