CODEWITHSUNDEEP
nhibernatenhibernate-mapping
I Am The Enterprise
I Am The Enterprise

Reputation: 93

NHibernate executing extraneous select statements

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

Upvotes: 4

Views: 404

Answers (2)

Deeksy
Deeksy

Reputation: 5434

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.

Upvotes: 1

Deeksy
Deeksy

Reputation: 5434

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.

Upvotes: 0

Related Questions