Can NHibernate solve N+1 without using batching or Criteria API for an Order->Customer relationship?

I have been reading and looking around to find this answer in black and white.

Let's talk about the familiar Customer and Order problem. Let's say I load 100 orders, and each order is linked to one and only one customer.

Using Fluent NHibernate, I will use Referenc开发者_如何学JAVAes() to link my Order to Customer and I will define my Not.LazyLoad() and Fetch.Join() as well.

Now I am thinking hypothetically, NHibernate can simply join these two tables and would be pretty easy to hydrate entities. However, in my tests I always see rather N+1 queries (in fact perhaps only the unique IDs). I can share my code and tables but it might bore you, so

• Is it possible to overcome N+1 for Order->Customer (one->one or rather Many->One)? Or I have to use batching or Criteria API?
• If possible can you please point me to an Fluent NHibernate example?

---

Frequently there is the complain that fetch="join" doesn't work. This is because it is not considered by HQL. You can declare it within HQL.

I used fetch="join" hoping to make performance better but stopped using it in many cases. The problem was that joining to many tables could make SQL server run into a maximal number of columns limit. In some cases you don't need the data at all and therefore it is not very useful to specify it globally in the mapping file.

So I would recommend to

• either use explicit join fetching in HQL, because there you know if the data is actually used.
• or for any other case, batches are a great solution, because they are transparent (your code doesn't need to know about), make use of lazy loading and reducing the N+1 problem at the same time.

---

I wouldn't look at the mapping as much as the actual querying you are doing. I leave ALL of my mappings as LazyLoad by default and override as needed.

I use the Criteria API to query, and I use CreateAlias to join other tables as needed. NHProf is highly recommended to find and eliminate situations like this.

---

Hi
There are two ways you can address your problem

a) Using Criteria query It will look something like this

Session.CreateQuery(typeof(Order))
.Add(<Restrictions if any>)
.SetFetchMode("Customer",FetchMode.Eager)
.List<Order>();

b) Using HQL

Session.CreateQuery("select o from Order inner join fetch o.Customer where <conditionifany>").List<Order>();

Hope this helps..

---

What query API are you using?

If it's HQL, you can use join fetch to retrieve an association eagerly.

For LINQ and QueryOver, use .Fetch()