R – NHibernate executing extraneous select statements

nhibernatenhibernate-mapping

Problem

When executing a get for an entity with a many-to-one relationship with outer join set to true, not-found set to ignore and the entity row on the one side does not exist, an extra select is executed by NHibernate trying to load it even though the previous select which was just executed could not find it.

Question

Why is this extra select executed and is there any way to suppress it?

Context

I am working with a legacy database with no foreign key constraints where I have zero control over the schema. Modifying it is unfortunately out of the question. Keep in mind my actual mappings are more complex than this. This occurs several times for the actual entity and they are often loaded by ICriteria.List<T>() and not Session.Load<T>(id), resulting in alot of unnecessary queries and a big performance hit.

Simplified Example


Code

ISession Session = ...
...
Session.Get<Bid>(1);
...

Executed SQL

SELECT bid.Id, bid.ItemId, item.Id FROM Bid bid left outer join Item item on bid.ItemId=item.Id WHERE bid.Id=@p0;@p0 = 1
SELECT item.Id FROM Item item WHERE item.Id=@p0;@p0 = 222

Mapping

<?xml version="1.0" encoding="utf-8" ?>  
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="Example" namespace="Example">  
   <class name="Bid">  
     <id name="Id">  
       <generator class="native"/>  
     </id>  
     <many-to-one name="Item" column="ItemId" outer-join="true" not-found="ignore" />  
   </class>  
   <class name="Item">  
     <id name="Id">  
       <generator class="native"/>  
     </id>  
   </class>  
 </hibernate-mapping>  

Bid Table Contents

Id  ItemId    
1   222  

Item Table Contents

Id  
333  
444  

Best Answer

OK, looks like it's definitely a bug in NHibernate, as you can see here. At the moment, it's marked as minor, so I guess voting it up might raise it's profile and get a fix going.