Category name:HQL

NHibernate implicit join quirk

Recently I had a problem where in an HQL query the result contained duplicates. I used implicit joins in the my query and it looked like the following:

from
    Order o
where
    (o.Code like :literal
    OR o.CustomData like :literal
    OR o.Customer.Code like :literal
    OR o.Customer.Name like :literal)

This HQL got translated to the following TSQL query:

select
    order0_.* — all order columns removed for readability
from
    nhtest.dbo.Order order0_,
    nhtest.dbo.Customer customer0_
where
    (order0_.Code like @p0 )
    OR(order0_.CustomData like @p1 )
    OR(customer0_.Code like @p2  and order0_.CustomerId=customer0_.Id) — Incorrect join!
    OR(customer0_.Name like @p3  and order0_.CustomerId=customer0_.Id) — Incorrect join!

The join isn’t correct as it should have been the following:

select
    order0_.* — all order columns removed for readability
from
    nhtest.dbo.Order order0_,
    JOIN nhtest.dbo.Customer customer0_ ON (order0_.CustomerId=customer0_.Id)
where
    order0_.Code like @p0 )
    OR(order0_.CustomData like @p1 )
    OR(customer0_.Code like @p2)
    OR(customer0_.Name like @p3)
)

The (N)Hibernate documentation says that it is better to make use of the explicit join syntax and that is working correct. So do not use implicit joins (well, atleast not in version 2.0.0.GA)!

  • Recent Posts
  • Recent Comments
  • Archives
  • Categories
  • Meta