How would I rewrite this SQL using QueryOver? I'm not sure how QueryOver's join precedence and resolution works.
SELECT DISTINCT T1.*
FROM T1
LEFT JOIN T2
JOIN T3
ON T2.T3Key = T3.PrimaryKey
ON T1.PrimaryKey = T2.T1Key
LEFT JOIN T4
JOIN T5
ON T4.T5Key = T5.PrimaryKey
ON T1.PrimaryKey = T4.T1Key
WHERE T3.Criteria = @Criteria
OR T5.Criteria = @Criteria
Assuming you have all the relationships setup, it's a matter of setting up alias variable to use in the QueryOver...
T2 t2Alias = null;
T3 t3Alias = null;
T4 t4Alias = null;
T5 t5Alias = null;
int criteria = 1;
var results = Session.QueryOver<T1>()
.Left.JoinAlias(x => x.T2, () => t2Alias)
.Left.JoinAlias(() => t2Alias.T3, () => t3Alias)
.Left.JoinAlias(x => x.T4, () => t4Alias)
.Left.JoinAlias(() => t4Alias.T5, () => t5Alias)
.Where(Restrictions.Disjunction()
.Add(Restrictions.Where(() => t3Alias.Criteria == criteria))
.Add(Restrictions.Where(() => t5Alias.Criteria == criteria)))
.TransformUsing(Transformers.DistinctRootEntity)
.List();
I don't think you'll be able to nest those inner joins...but it looks like you'd get the same results with all left joins.
