Queryover where id is not in list

Question

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?

Billyebilobate

Answer 1

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));

Ariannaarianne

Answer 2

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)

Airburst