Home > Development > Linq to SQL, aggregates and empty results

Linq to SQL, aggregates and empty results

May 15th, 2008 Leave a comment Go to comments

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]
Categories: Development Tags: , ,
  1. Giulliano Bueno
    June 11th, 2008 at 02:50 | #1
    Reply | Quote

    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.
    :-)

  2. Serge
    November 14th, 2008 at 11:56 | #2
    Reply | Quote

    You have my gratitude as well!
    I actually tried everything but your solution.

  3. Rishit
    April 28th, 2009 at 11:50 | #3
    Reply | Quote

    Thanks a lot…Was searching for the result since long time

  4. Sam
    September 16th, 2009 at 18:46 | #4
    Reply | Quote

    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. Fil Mackay
    October 18th, 2009 at 14:07 | #5
    Reply | Quote

    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. Matt Slay
    October 28th, 2009 at 04:01 | #6
    Reply | Quote

    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.

  7. Billy
    November 30th, 2009 at 13:57 | #7
    Reply | Quote

    Thanks couldnt figure this out.

  8. Peter
    February 15th, 2010 at 22:47 | #8
    Reply | Quote

    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. sshow
    February 19th, 2010 at 14:54 | #9
    Reply | Quote

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

  10. Anthony
    October 28th, 2010 at 16:36 | #10
    Reply | Quote

    Thx mate,
    saved me mutch time!

  11. rohit
    August 7th, 2011 at 07:14 | #11
    Reply | Quote

    thanks a lotttttt loveeee uuuuuuuuuuuu

  1. No trackbacks yet.