NHibernate Query problem

Question

I'm quite new to NHibernate and starting to find my way around.

I have a domain model that is somewhat like a tree.

Funds have Periods have Selections have Audits
Now I would like to get all Audits for a specific Fund

Would look like this if I made it in SQL

SELECT A.*
FROM Audit A
JOIN Selection S ON A.fkSelectionID = S.pkID
JOIN Period P ON S.fkPeriodID = P.pkID
JOIN Fund F ON P.fkFundID = F.pkID
WHERE F.pkID = 1

All input appreciated!

Answer 1

Try this

select elements(s.Audits)
from Fund as f inner join Period as p inner join Selection as s  
where f = myFundInstance

Answer 2

session.CreateCriteria ( typeof(Audit) )
  .CreateCriteria("Selection")
  .CreateCriteria("Period")
  .CreateCriteria("Fund")
  .Add(Restrinction.IdEq(fundId))

Answer 3

using LINQ ....

(from var p in Fund.Periods let fundPeriodSelections = p.Selections from var selection in fundPeriodSelections select selection.Audit).ToList()

... but it does depend on those many-to-many / one-to-many relations being 2-way. Also, I was thinking you may need a mapping table / class in bewteen the Period / Fund table.. but I guess you've already considered it.

Hope the LINQ statemanet above works ... it depends on those mentioend properties, but it's an apraoch we've used on our project that's really cleaned up the code.