Fluent NHibernate One-to-Many Mapping Results in Bad Query

Question

I have a pretty simple one to many mapping using Fluent NHibernate and it works fine except the the queries NH generates are less than ideal. I found this when I checked the sql log from NH.

The query I am looking for is like

select p.*, c.* from parent p inner join child c on p.Id = c.parentId

where p.ID is the PK of the parent table and c.ParentId is a FK in the child table pointing to the PK of the parent table.

But what I found from the sql log is something like this:

select P.* from Parent

followed by a bunch of queries running against the child table like

select * from child c where c.ParentId = @p0

Not surprisingly this is causing performance issues.

My question is why NH is not generate the inner join query I think it should? What do I need to change so that NH will generate the desired query?

Radim Köhler Radim Köhler · Accepted Answer · 2014-01-30T04:03:38

The JOIN is not suitable in this scenario. It will produce SELECT resulting in more rows, then the Parent table has. I.e: each child of a parent will append new row.

The appropriate solution here is to keep these selects separated. Firstly select the parent, then load all children for all parents (just selected parents). NHibernate does have nice solution for this:

19.1.5. Using batch fetching
Fluent version described here: How to Eager Load Associations without duplication in NHibernate?

This would work a bit similar as you experienced. Firstly select parent, then go for child(ren). The biggest difference is, that instead of 1 + N (going for each children collection separately) we now go for more children in batches. so it could be 1 + 2

Examples of the batch size mapping

1) Colleciton

HasMany<Child>(x => x.Children)
  .BatchSize(25);

2) Entity itself

public ChildMap()
{
    Id(x => x....
    ...
    BatchSize(25);

There are many other advantages, where the PAGING is one of the most profitable. Because we are working with a flat (not multiplied) Parent, we can apply .Take() and .Skip()

Finally, fi we would like to find some Parents based on their children, we can use in this scenario Subqueries: 15.8. Detached queries and subqueries

Fluent NHibernate One-to-Many Mapping Results in Bad Query

1 Answers