Lee Lee - 1 year ago 127
C# Question

Nhibernate QueryOver Left Outer Joins with conditions

I found a few resources online but havent really been able to sort this one out

Basically I have a query which has two left outter joins on it

var query = session.QueryOver<NewsPost>(() => newsPostAlias)
.Left.JoinQueryOver(x => newsPostAlias.PostedBy, () => userAlias)
.Left.JoinQueryOver(x => newsPostAlias.Category, () => categoryAlias)
.Fetch(x => x.PostedBy).Eager
.Fetch(x => x.Category).Eager
.Where(x => !x.Deleted);

This might be an invalid way of doing it but it appears to not break. Now what I want to do is on the two tables which have left outter joins on i want to make sure the Deleted column in both these tables is false.

However whenever I add that restriction the results only return when the foreign key column in news post is populated, but since this is nullable and why i made it a left outter join this isnt desirable.

Whats the best way of basically making it

.Where(x => !x.Deleted && !x.PostedBy.Deleted && !x.Category.Deleted);

I've looked into multiqueries, futures and disjunctions, I'm not sure what approach should be taken, obviously I can think of a few ways (bad ways my gut tells me) of doing this but whats the right way? :)


EDIT - Accepted Answer Modification

return session.QueryOver(() => newsPostAlias)
.Fetch(x => x.PostedBy).Eager
.Fetch(x => x.Category).Eager
.Left.JoinQueryOver(() => newsPostAlias.PostedBy, () => postedByAlias)
.Left.JoinQueryOver(() => newsPostAlias.Category, () => categoryAlias)
.Where(() => !newsPostAlias.Deleted)
.And(() => newsPostAlias.PostedBy == null || !postedByAlias.Deleted)
.And(() => newsPostAlias.Category == null || !categoryAlias.Deleted)
.OrderBy(() => newsPostAlias.PostedDate).Desc

Answer Source

I suppose your query should look like this

            .Left.JoinAlias(x => x.PostedBy, () => userAlias)
            .Left.JoinAlias(x => x.Category, () => categoryAlias)
            .Where(x => !x.Deleted)
            .And(x => !userAlias.Deleted)
            .And(x => !categoryAlias.Deleted);