Is null-checking on Linq queries idiomatic?

Question

Using Linq-to-SQL, I wonder which is more idiomatic of the following three,

• foos.Where(foo => foo.Bar.HasValue && foo.Bar.Value < 42)
• foos.Where(foo => foo.Bar.Value < 42)
• foos.Where(foo => foo.Bar < 42)

The first option generates an extra Bar IS NOT NULL predicate that is probably being optimized away in most DBMS'es. If one queried objects instead of a database, the null-check would be mandatory, but since one can create generic IQueriable<Foo> queries that might fail on objects but not on databases, the first option would always work, although both the Linq and SQL code is a little longer than the second option. The third option, provided by Michael Liu, seems to be the best of both worlds, but will not work in the case foo.Bar has type bool?: foos.Where(foo => foo.Bar) (results in a type error as implicit conversion is not made here).

Should one strive to write generic queries that will not fail if used outside of the context they were initially designed for?

Answer 1

Underlying data storage technology has a way of changing over the years. Code seems to live on much longer than anyone thought it ever would. I would not build in assumptions about the underlying storage provider.

If Linq is your abstraction layer, assume any Linq provider might be plugged in at some point in the application's lifecycle.

The former generates an extra Bar IS NOT NULL predicate that is probably being optimized away in most DBMS'es.

I'm not a database performance expert, but I would think the performance cost for the extra IS NOT NULL will be small compared to the overall cost of many queries.

Answer 2

A third option is to use the "lifted" < operator, which returns false if either operand is null. This lets you omit the explicit check for null and works for all LINQ providers:

foos.Where(foo => foo.Bar < 42)

If the compile-time type of foos is IEnumerable<Foo>, then foo.Bar < 42 is equivalent to

foo.Bar.GetValueOrDefault() < 42 && foo.Bar.HasValue

except that foo.Bar is accessed only once. (I got this by decompiling the program in Reflector. It's interesting that the compiler chooses to perform the comparison before checking for null, but it makes sense as a micro-optimization.)

If the compile-time type of foos is IQueryable<Foo>, then foo.Bar < 42 is equivalent to

foo.Bar < (int?)42

and it's up to the LINQ provider whether and how to check for null values.

---

UPDATE: If the type of foo.Bar is bool? and you only want to include records where foo.Bar is true (i.e., neither false nor null), then you can write

foos.Where(foo => foo.Bar == true)

or

foos.Where(foo => foo.Bar.GetValueOrDefault())