NHibernate executing extraneous select statements

Question

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

Answer 1

Without setting this up, I would guess that the key is to replace the outer-join="true" with fetch="join" in the many-to-one association. By default the fetch mode of a many-to-one association is "select", and I suspect that this is causing the select to be executed.

If you modify that mapping to be:

 <many-to-one name="Item" column="ItemId" fetch="join" not-found="ignore" />

it should behave more like you expect it to.

Answer 2

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.