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

I had a remarkably similar experience with IQueryable<T> and NHibernate. My solution:

public static TExpr MaxOrDefault<TItem, TExpr>(this IQueryable<TItem> query,
                                               Expression<Func<TItem, TExpr>> expression) {
  return query.OrderByDescending(expression).Select(expression).FirstOrDefault();
}

The only drawback is that you are stuck with the standard default value, instead of getting to specify one.

Answer 2

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();

Answer 3

In situations like this when there may or may not be a matching item, I prefer to return an object rather than a value type. If you return a value type, you have to have some semantics about what value means "there is nothing here." I would change it to return the last invoice, then (when it is non-null) get the invoice number from the invoice. Add a method to the class to return the numeric invoice number from the string.

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

        return context.GetTable<tblGreenSheet>()
                      .Where(gs => gs.InvoiceNumber.Substring(2, 4) == empNumber.ToString())
                      .OrderByDescending(gs => Convert.ToInt32(gs.InvoiceNumber.Substring(6, gs.InvoiceNumber.Length)))
                      .FirstOrDefault();
    }
}

public class tbleGreenSheet
{
    ....
    public int NumericInvoice
    {
        get { return Convert.ToInt32(InvoiceNumber.Substring(6, InvoiceNumber.Length)); }
    }
    ...
}

Used as

var invoice = Foo.GetLastInvoice( 32 );
if (invoice != null)
{
     var invoiceNumber = invoice.NumericInvoice;

     ...do something...
}
else
{
     ...do something else...
}