Nested Query in Fluent nHibernate

Question

I have this query:

IQueryable<OrderEntity> _records = All().Fetch(x => x.client).FetchMany(x => x.orderItems).ThenFetch(y => y.righeDistinte);

Simplyfing for the matter of this question, these are the involved entities:

public class OrderEntity : IEntity
{
    public virtual ClientEntity client { get; set; }
    public virtual ICollection<OrderItemEntity> orderItems { get; set; }
}

public class ClientEntity
{
    public virtual String cod_clifor { get; set; }
    public virtual String des_ragsoc { get; set; }
}

public class OrderItemEntity
{
    public virtual ICollection<DistintaItemEntity> righeDistinte { get; set; }
}

public class DistintaItemEntity
{
    public virtual OrderItemEntity orderItem { get; set; }
    public virtual DistintaEntity distinta { get; set; }
}

So, each OrderEntity instance references one ClientEntity and 0 to many OrderItemEntity objects. In turn, each OrderItemEntity can reference 0 to many DistintaItemEntity.

The query on top of this post returns the collection of all orders with the related client and orderitems and each orderitem has the distintaitementity fetched (all mappings are well set). All in just one SQL query.

So far, so good.

The problem is with DistintaEntity, which is not forced loaded, so if I want to access some of its properties I need to have the session open because of lazy loading (as long as there's an open session, it works, but there are additional queries of course).

I'd like to add to the query a directive to force direct fetch of the DistintaEntity object associated with each DistintaItemEntity, but I don't know how to do it without loosing the single query result.

All one to many relations are left join.

Thanks, Mario

I think it should be `ThenFetchMany(y => y.righeDistinte)`. – Gert Arnold Aug 22 '12 at 20:25 — Gert Arnold, Aug 22 '12 at 20:25
Thanks, this put me on track to find the solution! – mabian69 Aug 23 '12 at 09:21 — mabian69, Aug 23 '12 at 09:21

score 1 · Answer 1 · edited May 23 '17 at 12:04

Follow up: the query I mentioned in my earlier answer does work, but, as mentioned by handprint in a comment above, can easily lead to multiple children due to nHibernate improperly scanning the cartesian product.

I tried one of the solutions mentioned in the linked article: changing the mappings to add AsSet() directives to the HasMany() seem to have fixed this problem as well.

Full Stack Overflow question here: NHibernate ThenFetchMany is retrieving duplicate children.

score 0 · Answer 2 · answered Aug 23 '12 at 09:24

0

Following the first comment I managed to find the answer: by replacing the last ThenFetch with ThenFetchMany I was able to specify a further chained Fetch that produces exactly the wanted result, still one SQL query with added join and the DistintaEntity loaded not lazy.

The final query is:

IQueryable<OrderEntity> _records = All().Fetch(x => x.client).FetchMany(x => x.orderItems).ThenFetchMany(y => y.righeDistinte).ThenFetch(p => p.Distinta);

Thank you very much for helping.

answered Aug 23 '12 at 09:24

mabian69

706
6
12

Since you are using ...FetchMany(...).ThenFetchMany(...), I recommend you take a quick read [here](http://stackoverflow.com/questions/6194734/nhibernate-thenfetchmany-is-retrieving-duplicate-children). – Handprint Aug 23 '12 at 13:05
Thank you very much. This is indeed happening, will try applying some of the workarounds described in the linked question. – mabian69 Aug 27 '12 at 15:28

Nested Query in Fluent nHibernate

2 Answers2