title: NHibernate: How to eager fetch sub entities with a filter over sub entity through one sql query without lazyload?
body: <p>I'am using NHibernate 3.3.1 with FluentNhibernate 1.3 for Data Layer.</p>
  
  <p>I have the following entities:</p>
  
  <p><img src="https://i.stack.imgur.com/ITS8y.png" alt="enter image description here"></p>
  
  <p>Database Diagram:
  <img src="https://i.stack.imgur.com/GyE1N.png" alt="enter image description here"></p>
  
  <p>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. 
  <br/> <br/>I want NHibernate send a query like this:<br/></p>
  
  <pre><code>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 
  </code></pre>
  
  <p>I have tried the following options to solve this challenge; </p>
  
  <ol>
  <li><p>session<code>.QueryOver&lt; ProductEntity &gt;()</code>... <br/> 
  I have tried <code>Inner.JoinQueryOver&lt; .. &gt;().Fetch.Eager</code>...  but I couldn't fetch all the sub entities.</p></li>
  <li><p><code>session.CreateCriteria&lt; ProductEntity &gt;().SetFetchMode("",FetchMode.Eager)</code>...<br>
  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. </p></li>
  <li><p><code>session.Query&lt; ProductEntity &gt;().FetchMany(p=&gt;p.MediaList).ThenFetchMany(m=&gt;m.SellerList)</code>...<br>
  I couldn't create alias to pass mediaCategoryId filter in this case. Instead I used <code>.Where(x=&gt;x.MediaList.Any(m=&gt;m.CategoryList.Any(...)))</code> and the query generated is not optimum, too.</p></li>
  <li><p>(from p in session.Query&lt; ProductEntity >()<br>
         from m in p.MediaList<br>
         from c in m.MediaCategoryList<br>
         where c.Id==23<br>
         select p).Fetch(x=>x.MediaList);  </p>
  
  <p>this didn't work as I wanted, too..</p></li>
  <li><p><code>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 ";</code><br>
  THIS WORKS with "join fetch" in hql.<br>
  I need the best practice of this case, however Hql is the king.  </p>
  
  <p>Can we handle this case with <code>session.Query&lt;&gt;()</code> or <code>session.CreateCriteria,</code> or <code>QueryOver</code> ?</p></li>
  </ol>