NHibernate Left Join

Question

A very beginner question:

I have two classes, Review and ReviewSentences:

public class Review
{
    public virtual int recordId { get; set; }
    public virtual string reviewerId { get; set; }
    public virtual string reviewerName { get; set; }
    public virtual string country { get; set; }
    public virtual string zipCode { get; set; }
    public virtual string reviewProduct { get; set; }
    public virtual string reviewText { get; set; }
    public virtual string reviewTextLanguage { get; set; }
    public virtual double sentimentScore { get; set; }
    public virtual bool isScoreRefined { get; set; }
}

pulic class ReviewSentences
{
    public virtual int recordId { get; set; }
    public virtual int reviewId { get; set; }
    public virtual int sentenceId { get; set; }
    public virtual string sentence { get; set; }
    public virtual double sentimentScore { get; set; }
}

The property ReviewSentences.reviewId is a foreign key referring to Review.recordId. One review can have many sentences (Review:ReviewSentences is 1:Many)

I have been trying for a long time now but unable to replicate the following query in terms of NHibernate with session.CreateCriteria:

select * from Reviews r
left join
ReviewSentences rs
on
r.RecordId = rs.ReviewId
where rs.ReviewId is null

The query gives me all reviews from the Review table that do not have any records in the ReviewSentences table.

ORMs don't need references, you should use *relations* between entities to load child entities whenever you load a parent entity. Looking for a JOIN means there's a problem with the mapping — Panagiotis Kanavos, Aug 19 '16 at 15:58
Thanks for giving direction @PanagiotisKanavos. The issue is solved now — Syed, Aug 22 '16 at 08:50

score 1 · Answer 1 · answered Aug 19 '16 at 15:56

It is a matter of mapping you should include an array of ReviewSentences in your Review class and map it correctly.

public class Review
{
    public virtual int recordId { get; set; }
    public virtual string reviewerId { get; set; }
    public virtual string reviewerName { get; set; }
    public virtual string country { get; set; }
    public virtual string zipCode { get; set; }
    public virtual string reviewProduct { get; set; }
    public virtual string reviewText { get; set; }
    public virtual string reviewTextLanguage { get; set; }
    public virtual double sentimentScore { get; set; }
    public virtual bool isScoreRefined { get; set; }
    public virtual IList<ReviewSentences> sentences { get; set; }
}

pulic class ReviewSentences
{
    public virtual int recordId { get; set; }
    public virtual int reviewId { get; set; }
    public virtual int sentenceId { get; set; }
    public virtual string sentence { get; set; }
    public virtual double sentimentScore { get; set; }
}

then in the mapping you should refer sentences as a reference. but you did not said which kind of mapping your using (Fluent, conformist, etc.)

Thanks for giving direction to my question. That plus the answer here helped me reach what I wanted: http://stackoverflow.com/questions/29985158/minimal-and-correct-way-to-map-one-to-many-with-nhibernate — Syed, Aug 22 '16 at 08:49

score 0 · Answer 2 · answered Aug 23 '16 at 12:27

Fixed the mapping in hbm.xml files and got the required results using:

var reviews= session.CreateCriteria<Review>("r")
                .CreateCriteria("r.sentences", JoinType.LeftOuterJoin)
                .Add(Restrictions.IsNull("recordId"))
                .List<Review>();

NHibernate Left Join

2 Answers2