Geek Answers Handbook

LINQ query null exception when Where returns 0 rows

I have the following LINQ method that works as expected, except when there are no Found strings, I get a Null Exception. I fight for how I modify this to return 0 if that happens.

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 the Jon Skeet 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(); } }
+4
source share
3 answers

You're using

 .Where(...) .DefaultIfEmpty()

which means that if there are no results, pretend it with a single zero result. Then you try to use this null result when calling Max ...

Perhaps you can change it to:

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

Does it use overload by maximizing int? values int? , and returns int? null int? null if there were no values. Then ?? 0 ?? 0 converts this null value to 0. At least that LINQ to Objects behavior ... you will need to check if it gives the same result for you.

Of course you do not need to use ?? 0 ?? 0 , if you gladly change the method signature to return an int? . This will provide additional information, as the caller can then determine 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 DefaultIfEmpty() overload, which takes a value - like this:

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

In such situations, when there may or may not be a suitable element, I prefer to return an object rather than a value type. If you return the type of value, you should have some semantics about what the meaning of "there is nothing" means. I would modify it to return the last invoice, and then (when it is not zero) gets the invoice number from the invoice. Add a method to the class to return the line number number from the line.

 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... }
0
source

I had surprisingly similar experiences with IQueryable<T> and NHibernate. My decision:

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

The only downside is that you are stuck with the standard default instead of specifying it.

0
source

More articles:


All Articles