CODEWITHSUNDEEP
c#linqfluent-nhibernate
mabian69
mabian69

Reputation: 716

Nested Query in Fluent nHibernate

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

Upvotes: 2

Views: 803

Answers (2)

mabian69
mabian69

Reputation: 716

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.

Upvotes: 1

mabian69
mabian69

Reputation: 716

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.

Upvotes: 0

Related Questions