If you really want to fetch it with one query.. then use a subquery: SELECT b.* FROM Books WHERE Book_Id IN (SELECT Book_Id FROM Pages WHERE Page_Id = @pageId)
You can do this in NHibernate via a DetachedCriteria for the selection of the correct Page and pass this to the query for the Book entity. -- Ramon On Wed, Jun 8, 2011 at 7:36 PM, DannyT <[email protected]> wrote: > I have a Book with a collection of Pages > I get a PageId from a service call and want to get the book which contains > that page *with all it's pages.* > * > * > In SQL: > > select b.*, ps.* > from Books b > left join Pages p on p.Book_Id = b.Book_Id > left join Pages ps on b.Book_Id = ps.Book_Id > where p.Page_Id = @pageId; > * > * > I've tried this: > > Book bookAlias = null; > Page pageAlias = null; > Page pagesAlias = null; > > var book = _session.QueryOver(() => bookAlias) > .Left.JoinAlias(c => c.Pages, () => pageAlias) > .Left.JoinQueryOver(c => bookAlias.Pages) > .Where(() => pageAlias.Id == pageId) > .SingleOrDefault(); > > But get a "duplicate association path: Pages" error. Without the duplicated > join I only get the Book with the one page. > > Any suggestions? > > -- > You received this message because you are subscribed to the Google Groups > "nhusers" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/nhusers?hl=en. > -- Ramon -- You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
