Force the same behaviour using Linq to nHibernate with Oracle as Linq to collections when comparing columns containing null

Question

Suppose I have a Man class defined in nHibernate

public class Man
{
   public string Name {get;set;}
   public string WifeName{get;set;}
   public string OldestChildName {get;set;}
}

with some instances:

Name   |WifeName   |OldestChildeName
-------+-----------+-----------------
Mike   |Penny      |Jenny
Bill   |           |Julia       
Tom    |Patricia   |Patricia
Peter  |           |
Bart   |Mellany    |

To select all persons where the oldest child name is the same as their wifes name

var names = 
  from m in men
  where m.WifeName == m.OldestChildName
  select m.Name

For Linq to collections this returns Tom and Peter. But Linq to nHibernate to Oracle translates this to:

SELECT 
    m.name
FROM
    men m
WHERE 
    m.WifeName = m.OldestChildName;

which returns just Tom: in SQL comparisons to null evaluate to null, and null evaluates to false.

---

What I'd like to see generated is:

SELECT 
    m.name
FROM
    men m
WHERE 
    m.WifeName = m.OldestChildName
OR  (
        m.WifeName IS NULL
    AND m.OldestChildName IS NULL
    );

so in linq I need to type:

var names = 
  from m in men
  where m.WifeName == m.OldestChildName
    || (m.WifeName == null && m.OldestChildName == null)
  select m.Name

---

Is there a simple way to tell nHibernate to translate an equality comparison between two nullable colums, to a sql comparison and a null check?

In other words, is there a way to use the same linq query and get the same results regardless of if it's used for collections or databases?

All I could think of was building a pre-processor that parses the expression tree and modifies it to also emit the null checks. Has anybody already done this?

Answer 1

The sql query you need.

select * from Stack
where COALESCE (wifeName,'')=COALESCE (OldestChildeName,'')

Output.

You need to use Nhibernate queryover for it.

var left = Projections.SqlFunction("COALESCE",
NHibernateUtil.String,
Projections.Property<Stack>(pc => pc.OldestChildeName),
Projections.Constant(""));


var right = Projections.SqlFunction("COALESCE",
NHibernateUtil.String,
Projections.Property<Stack>(pc => pc.WifeName),
Projections.Constant("") );

var restriction = Restrictions.EqProperty(left, right);

var dd = contex.Session<Stack>().QueryOver<Stack>().Where(restriction).List();

Output.

ı suggest you lock