DevExpress Linq to XPO and NULL values

DevExpress’ Linq To XPO has some known difficulties handling NULL values in .Where() conditions.

I came across them using code like this:

string Name = NullableString( [some input value] );
string Street = NullableString( [some input value] );

with NullableString() defined as

string NullableString(string s)
{
  if (string.IsNullOrEmpty(s))
    return null;
  return s.Trim();
}

and the query

var q = new XPQuery<Addresses>(session)
  .Where(a => a.Name == Name && a.Street == Street)
  .FirstOrDefault();

The resulting SQL statement looks like this:

Executing sql 'select * from (
  select N0."ID" as F0,N0."NAME" as F1,N0."STREET" as F2 
  from "ADDRESSES" N0 
  where ((N0."NAME" = :p0) and (N0."STREET" = null)) 
  where RowNum <= 1' with parameters {....}

This result surprised me (comparing null values using ‘=’ instead of IS NULL), especially since the DevExpress support center (Q100139, B94052)  indicates that NULL value handling has been fixed years ago.

Fortunately, another article in the support center provides an explanation and a workaround:

When the LINQ expression is processed by XPO, the value of the variable isn’t yet recognized and we can’t determine whether the variable has a value.

The workaround consists of checking each of the parameters for NULL, and comparing to the literal null value:

var q = new XPQuery<Addresses>(session)
  .Where(a => some not-null conditions);

if (Name == null)
  q = q.Where(a => a.Name == null);
else
  q = q.Where(a => a.Name == Name);

if (Street == null)
  q = q.Where(a => a.Street == null);
else
  q = q.Where(a => a.Street == Street);

Executing this Linq query generates the following statement:

Executing sql 'select * from (
  select N0."ID" as F0,N0."NAME" as F1,N0."STREET" as F2 
  from "ADDRESSES" N0 
  where ((N0."NAME" = :p0) and N0."STREET" is null) 
  where RowNum <= 1' with parameters {...}

just as you’d expect. Note that the combination of .Where() conditions is correctly translated into AND operations.

Another entry in the support center suggests that there won’t be a native solution:

We don’t see another solution, that can be implemented at the XPO level, without risk of introducing another bug. We have discussed this issue again, and come to the decision to not change anything.