NHibernate: How to eager fetch sub entities with a filter over sub entity through one sql query without lazyload?

Question

I'am using NHibernate 3.3.1 with FluentNhibernate 1.3 for Data Layer.

I have the following entities:

Database Diagram:

I need a method that gets the Products by MediaCategory of Medias of Products. I want NHibernate to send only one query to db and fetch all sub properties of products.

I want NHibernate send a query like this:

declare @mediaCategoryId int = 13
select * 
from Product p 
inner join Media m on m.ProductId=p.Id 
inner join MediaCategoryMedia mcm on mcm.MediaId=m.Id 
inner join MediaCategory mc on mc.Id=mcm.MediaCategoryId 
left join ProductSeller ps on ps.ProductId=p.Id 
left join Seller s on ps.SellerId=s.Id 
where mc.Id=@mediaCategoryId 

I have tried the following options to solve this challenge;

1. session.QueryOver< ProductEntity >()...
   I have tried Inner.JoinQueryOver< .. >().Fetch.Eager... but I couldn't fetch all the sub entities.

2. session.CreateCriteria< ProductEntity >().SetFetchMode("",FetchMode.Eager)...
   In this case lazy load works and I dont want lazyload. If I disable lazyload from mappings NH sends lots of queries.. what I want is eager load with one single query that fetches all sub entities.

3. session.Query< ProductEntity >().FetchMany(p=>p.MediaList).ThenFetchMany(m=>m.SellerList)...
   I couldn't create alias to pass mediaCategoryId filter in this case. Instead I used .Where(x=>x.MediaList.Any(m=>m.CategoryList.Any(...))) and the query generated is not optimum, too.

4. (from p in session.Query< ProductEntity >()
   from m in p.MediaList
   from c in m.MediaCategoryList
   where c.Id==23
   select p).Fetch(x=>x.MediaList);

   this didn't work as I wanted, too..

5. var hql=@"select p from ProductEntity as p join fetch p.MediaList as m join fetch m.MediaCategoryList as mc left join fetch p.SellerList as s where mc.Id=:catId ";
   THIS WORKS with "join fetch" in hql.
   I need the best practice of this case, however Hql is the king.

   Can we handle this case with session.Query<>() or session.CreateCriteria, or QueryOver ?

Answer 1

For a direct translation of your query...

Media mediaAlias = null;
MediaCategory categoryAlias = null;

return session.QueryOver<Product>()
    .JoinAlias(x => x.Medias, () => mediaAlias)
    .JoinAlias(() => mediaAlias.Categories, () => categoryAlias)
    .Fetch(x => x.Sellers).Eager
    .Where(() => categoryAlias.Id == mediaCategoryId)
    .List();

JoinAlias does an inner join by default, and Fetch(...).Eager does a left outer join. JoinAlias allows us to dig down through Media to the categories, and it also eagerly fetches the data.

Note that there is a Cartesian product between Sellers and Medias in this query. If there are 20 Medias and 20 Sellers on a single Product, then this query would return 20 * 20 = 400 rows, which is not ideal for performance. You can address this by splitting the Media fetching and the Seller fetching into separate queries, but batch them together in one round-trip to the database using Future(), meaning the query would return 20 + 20 = 40 rows. Much better.

Also, this query will not return all of the categories associated with a Media. If you need this, then you should apply the mediaCategoryId constraint in an Exists sub-query.