LINQ query null exception when Where returns 0 rows

Question

I have the following LINQ method that works as expected except if there are No Rows Found then I get a Null Exception. I am struggling on how I modify this to return 0 if that occurs.

    public static int GetLastInvoiceNumber(int empNumber)
    {
        using (var context = new CmoDataContext(Settings.Default.LaCrosse_CMOConnectionString))
        {
            context.Log = Console.Out;

            IQueryable<tblGreenSheet> tGreenSheet = context.GetTable<tblGreenSheet>();

            return (tGreenSheet
                            .Where(gs => gs.InvoiceNumber.Substring(2, 4) == empNumber.ToString())
                            .DefaultIfEmpty()
                            .Max(gs => Convert.ToInt32(gs.InvoiceNumber.Substring(6, gs.InvoiceNumber.Length)))
                            );
        }
    }

Thanks

---

I tried one of Jon Skeet's suggestions, below, and now I get Unsupported overload used for query operator 'DefaultIfEmpty'

    public static int GetLastInvoiceNumber(int empNumber)
    {
        using (var context = new CmoDataContext(Settings.Default.LaCrosse_CMOConnectionString))
        {
            context.Log = Console.Out;

            IQueryable<tblGreenSheet> tGreenSheet = context.GetTable<tblGreenSheet>();

            return tGreenSheet
                            .Where(gs => gs.InvoiceNumber.Substring(2, 4) == empNumber.ToString())
                            .Select(gs => Convert.ToInt32(gs.InvoiceNumber.Substring(6, gs.InvoiceNumber.Length)))
                            .DefaultIfEmpty(0)
                            .Max();                                
        }
    }

Answer 1

You're using

.Where(...)
.DefaultIfEmpty()

which means if there are no results, pretend it's a sequence with a single null result. You're then trying to use that null result in the Max call...

You can probably change it to:

return tGreenSheet.Where(gs => ...)
                  .Max(gs => (int?) Convert.ToInt32(...)) ?? 0;

This uses the overload finding the maximum of int? values - and it returns an int? null if there were no values. The ?? 0 then converts that null value to 0. At least, that's the LINQ to Objects behaviour... you'll have to check whether it gives the same result for you.

Of course, you don't need to use the ?? 0 if you're happy to change the method signature to return int? instead. That would give extra information, in that the caller could then tell the difference between "no data" and "some data with a maximum value of 0":

return tGreenSheet.Where(gs => ...)
                  .Max(gs => (int?) Convert.ToInt32(...));

Another option is to use the overload of DefaultIfEmpty() which takes a value - like this:

return tGreenSheet.Where(gs => ...)
                  .Select(gs => Convert.ToInt32(...))
                  .DefaultIfEmpty(0)
                  .Max();