Geek Answers Handbook

Nhibernate QueryOver Left Outer Joins Terms

I found several resources on the internet, but havent really been able to sort this

I basically have a query that has two left members.

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 may not be the right way to do this, but it does not seem to break. Now what I want to do is two tables that left links to exits. I want the Deleted column in both of these tables to be false.

However, whenever I add this restriction, the results are returned only when the foreign key column in the newsletter is filled, but since this value is null, and why I made it a left member, this is not desirable.

What is the best way to basically do this

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

I studied variety, futures and disjunctions, I'm not sure which approach should be taken, obviously, I can think of several ways (as my gut tells me) about how to do this, but right? :)

thanks

EDIT - Modify the accepted answer

 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 .Take(10) .List();
+4
source share
2 answers

I assume your query should look like this:

  Session.QueryOver<NewsPost>() .Left.JoinAlias(x => x.PostedBy, () => userAlias) .Left.JoinAlias(x => x.Category, () => categoryAlias) .Where(x => !x.Deleted) .And(x => !userAlias.Deleted) .And(x => !categoryAlias.Deleted);
+7
source

It seems to work ...

 var posts = session.QueryOver<NewsPost>() .Left.JoinAlias(x => x.Category, () => category) .Left.JoinAlias(x => x.PostedBy, () => user) .Where(x => x.Deleted == false) .Where(Restrictions .Or( Restrictions.Where(() => user.Deleted == false), Restrictions.Where<NewsPost>(x => x.PostedBy == null) ) ) .Where(Restrictions .Or( Restrictions.Where(() => category.Deleted == false), Restrictions.Where<NewsPost>(x => x.Category == null) ) ) .List();

Was this one of the ways you think it would be bad? If so, could you explain why? I don't know enough about sql optimization, so I ask ...

0
source

More articles:


All Articles