Search code examples
nhibernatefluent-nhibernate

Different query after nhibernate 5 (using oracle)

After upgrading from 4.1 to 5.2, nhibernate is creating a query a little different, putting an "OR" where it is not welcome.

4.1

...,
...,   
(select cast(count(guia5_.ID) as NUMBER(10, 0))
  from SAM_GUIA guia5_

 where guia5_.PEGID = peg1_.ID                        <<<<<<<<<<<<<<<

) as col_41_0_,

5.x

...,
...,   
(select cast(count(guia5_.ID) as NUMBER(10, 0))
   from SAM_GUIA guia5_

  where guia5_.PEGID = peg1_.ID                        <<<<<<<<<<<<<<<
     or (guia5_.PEGID is null)                         <<<<<<<<<<<<<<<
    and (peg1_.ID is null)                             <<<<<<<<<<<<<<<

) as col_41_0_,

The linq query related is:

...,
...,
RecordCount = (from c in repositoryGuia.All()
           where c.PegId == b.Id
           select c.Id
           ).Count(),

More information about mapping:

  • c.PegId: int?
  • b.Id: int

Using NHibernate 5.2.6 and Fluent 2.1.2.

Why version 5 is translating to a different SQL statement?

Solution

  • It's a known 5.x issue GH-1860. Details when and why behavior is changed explained here. AFAIK there is no workaround for LINQ so you have to use hql/QueryOver if it's a showstopper for you.

    But there is an open pull request here that should fix this behavior. So maybe in 5.3 it will be fixed.