CODEWITHSUNDEEP
c#nhibernate
Syed
Syed

Reputation: 358

NHibernate Left Join

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.

Upvotes: 0

Views: 482

Answers (2)

Syed
Syed

Reputation: 358

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>();

Upvotes: 0

hpfs
hpfs

Reputation: 506

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.)

Upvotes: 1

Related Questions