I have been struggling with this for a while, so I hope some of you QueryOver experts can help.
I have a list of blog posts. You can vote on each blog post, and I would like (among other) to recieve a list of posts where the user hasn't voted.
First I was thinking about doing something like:
Session.QueryOver<BlogPost>()
.WhereRestrictionOn(bp => bp.Id)
.NotIn(existingBlogPostVotes);
(existingBlogPostVoteIds is the ids of the voted blogposts)
But this doesn't exist in the QueryOver framework.
I found out that I could do it in Criteria like this:
var crit =
Session.CreateCriteria<BlogPost>()
.Add(Restrictions.Not(Restrictions.In("Id",existingBlogPostVotes)));
But I would to do this in QueryOver and not Criteria.
How would this be done in QueryOver?
How about Not.IsIn()
:
Session.QueryOver<BlogPost>()
.WhereRestrictionOn(bp => bp.Id)
.Not.IsIn(existingBlogPostVotes);
Optionally this could be done in the Linq provider as well:
Session.Query<BlogPost>()
.Where(bp => !existingBlogPostVotes.Contains(bp.Id));
What about a standard query in which you use a sub query in the where clause. I don't have visual studio infront of me to verify the syntax, but essentially this is quering all Blog posts where there does not exist a blogPostVote record for the current user.
Session.QueryOver<BlogPost>()
.Where(bp => bp.Id)
.Where(
!Session.QueryOver<BlogPostVotes>()
.Where(blogPostVotes => blogPostVotes.BlogPostId == bp.Id)
.Where(blogPostVotes => blogPostVotes.UserId == currentUserId)
.Any());
That should give you the result your looking for. I know my syntax will work in Linq To Sql, if it doesn't work for NHibernate, look at this answer here (Subqueries with QueryOver)
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