CODEWITHSUNDEEP
c#nhibernatefluent-nhibernatenhibernate-mappingnhibernate-criteria
user315648
user315648

Reputation: 1965

How to do multiple joins with NHibernate Criteria API

I have the following data model:

Page
- Id      // Pk
- Type    // int

Section
- Id      // Pk
- Page    // Fk

Comment
- Id      // Pk
- Section // Fk
- Date    // DateTime

I'm trying to query all comments that are associated with a certain Page (Say page.id = 2 and page.Type = 1) within a time limit. I tried it like this:

   var criteria = session.CreateCriteria<Comment>()

   .Add(Restrictions.Eq("Section.Page.Id", pageId))
   .Add(Restrictions.Eq("Section.Page.Type", pageType))
   .Add(Restrictions.Ge("Date", start))
   .Add(Restrictions.Lt("Date", end));

However, this fails as I get an error says "could not resolve property: Page of: TestNamespace.Comment". This normally would indicate mapping errors, but it works in all other cases, so I'm inclined to belive the error lies in the query.

To make matters worse, Comment.Section might be null in some cases (there are comments that are not associated with a section nor page). In that case I want to ignore those comments.

Any advice ?

Thanks!

Upvotes: 5

Views: 5567

Answers (2)

kamui
kamui

Reputation: 3419

You can use additional CreateCriteria calls to navigate through the database structure

var criteria = session.CreateCriteria<Comment>()
               .Add(Restrictions.Ge("Date", start))
               .Add(Restrictions.Lt("Date", end))
               .CreateCriteria("Section")
               .CreateCriteria("Page")
               .Add(Restrictions.Eq("Id", pageId))
               .Add(Restrictions.Eq("Type", pageType));

Upvotes: 0

Meligy
Meligy

Reputation: 36594

  var criteria = session.CreateCriteria<Comment>()
     .CreateAlias("Section", "section")
     .CreateAlias("section.Page", "page")
     .Add(Restrictions.Eq("page.Id", pageId))
     .Add(Restrictions.Eq("page.Type", pageType))
     .Add(Restrictions.Ge("Date", start))
     .Add(Restrictions.Lt("Date", end));

I updated the code based n your comment. The 2nd line specifically was added, and the 3rd line is using the alias in 2nd line instead of the property, and so on.

Upvotes: 6

Related Questions