I've been trying to accomplish this for a few days using QueryOver without much progress. I can't seem to find a way to add a conditional on a left outer join on a one to many relationship. I have Question and Answer entities, where a Question has multiple Answers (it's for a Survey, where each response is another Answer to the same Question). I'm trying to filter all the Answers based on some criteria (for example, all answers with a Score < 3), but anytime I attempt to add the conditional, it gets added to the WHERE clause and not on the JOIN.
Question:
public class Question : Entity<int>
{
public virtual IEnumerable<Answer> Answers { get; set; }
...
}
Answer:
public class Answer : Entity<int>
{
public virtual Question Question { get; set; }
public virtual int Score { get; set; }
...
}
I've tried many different variants of using JoinQueryOver...
session.QueryOver<Question>()
.Where(q => q.Survey.Id == id)
.Left.JoinQueryOver(q => q.Answers)
.Where(a => a.Score < 3)
...and JoinAlias and using the alias in the Where
session.QueryOver<Question>(() => questionAlias)
.Where(q => q.Survey.Id == id)
.Left.JoinAlias(() => questionAlias.Answers, () => answerAlias)
.Where(() => answerAlias.Score > 3);
I always get a query like this:
SELECT * FROM QUESTION q
left outer join ANSWER a on q.Id=a.Question_id
WHERE q.Survey_id = 1 and a.Score < 3
but I need:
SELECT * FROM QUESTION q
left outer join ANSWER a on q.Id=a.Question_id and a.Score < 3
WHERE q.Survey_id = 1
It is better to add the condition in the Join. Performance is more important than readability. For large datasets, it matters.
WHERE Conditions. The purpose of the ON clause is to specify the join conditions, that is, to define how the tables should be joined. Specifically, you define how the records should be matched.
Yes, you can! The longer answer is yes, there are a few ways to combine two tables without a common column, including CROSS JOIN (Cartesian product) and UNION.
One difference is that the first option hides the intent by expressing the join condition in the where clause. The second option, where the join condition is written out is more clear for the user reading the query. It shows the exact intent of the query.
Turns out I was using an older version of NHibernate (3.1) that didn't support this, but upgrading to 3.3 now has an additional withClause you can pass, like so:
Answer answerAlias = null;
var questionQuery = session.QueryOver<Question>()
.Where(q => q.Survey.Id == id)
.Left.JoinQueryOver(q => q.Answers, () => answerAlias, a => a.Score > 3);
I believe this is supported as of 3.2 (the accepted answer on this question is what tipped me off)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With