How to translate 'where not in' into an NHibernate query where the subquery table is not an entity?

Question

I have two entities: User and Notice, they are linked together via the DismissedNoticeToUser table (UserId and NoticeId are the respective columns).

Here's my FluentNHibernate mapping for the User class:

mapping.HasManyToMany<Notice>(u => u.DismissedNotices)
    .Table("DismissedNoticeToUser")
    .ParentKeyColumn("UserId")
    .ChildKeyColumn("NoticeId")
    .Inverse();

Every time a use "dismisses" a notice, a pair of the User.Id and Notice.Id are added to the DismissedNoticeToUser class. This is easy in code:

var notice = this.session.Load<Notice>(noticeId);
var user = this.session.Load<User>(this.userSession.Id);
user.DismissedNotices.Add(notice);

If I want to list all notices not dismissed by the user, I write something like this in raw SQL:

select * from [Notice]
    where Id not in 
    (select NoticeId from [DismissedNoticeToUser] where UserId=@userId)

However, I'm not really sure how to do the same thing using NHibernate. I've tried the following, but it does a left join on the user's notices as a separate query.

this.session.Query<Notice>().Where(n => !user.DismissedNotices.Contains(n));

I could drop down and use NHibernate to write raw SQL, but I feel as though I'm probably missing something. I don't care which NHibernate query API I use.

score 2 · Answer 1 · edited May 23 '17 at 12:14

2

I think the following post describes how you can query these many to many relationships:

FluentNHibernate query on many-to-many relationship objects

There are probably tons of other similar posts to this effect.

Edit

Here is an example closer to what you are asking for:

var subquery = QueryOver.Of<Notice>()
                .JoinQueryOver<User>(x => x.DismissedNotices)
                .Where(x => x.Id == userId)
                .Select(x => x.Id);

IList<Notice> groupsFound =
         session.QueryOver<Notice>()
                .WithSubquery.WhereProperty(x => x.Id).NotIn(subquery)
                .List<Notice>();

If you are wanting to generate the query exactly as you have above I'm not sure you can do that unless you map the table DismissedNoticeToUser as an entity. If you were to do that then you could probably do something like this:

var subquery = QueryOver.Of<DismissedNoticeToUser>()
                        .Where(x => x.UserId == userId)
                        .Select(x => x.NoticeId);

IList<Notice> noticesFound = session.QueryOver<Notice>()
                               .WithSubquery.WhereProperty(x => x.Id).NotIn(subquery)
                               .List<Notice>();

edited May 23 '17 at 12:14

Community

1
1

answered Mar 06 '12 at 20:16

Cole W

15,123
6
51
85

Sorry but it doesn't. That first grabs the results and loads them into memory and then does the *not in* with those results. That does two queries vs one which is completely different than the SQL query in the OP. – TheCloudlessSky Mar 07 '12 at 11:05
Yeah - I knew I could map it as an entity... I just think it's overkill when I've already defined that relationship in my mappings. Also, it just feels wrong to have to define such an entity. – TheCloudlessSky Mar 07 '12 at 13:42
I admit it does seem like overkill to map the entity but if you are dead set on generating that exact query I'm not sure that there is another way around it. The other query should work above. Also in the example link I posted you could use Futures if you want to avoid the 2 hits to the db. – Cole W Mar 07 '12 at 14:44
Still - it seems as though the abstraction of the ORM has failed here. I think I'm going to go with the SQL query since it's the most logical and performant solution. – TheCloudlessSky Mar 07 '12 at 15:04

Michael Buen · Accepted Answer · 2012-03-09T01:14:34.197

2

If you can't map Users to Notice, try to code Query against a Query too. I haven't tried partnering Load to Query, using Load against Query might be causing NH to generate separate queries.

Try this one:

this.session.Query<Notice>().Where( n => 
    this.session.Query<User>().Any(u => u.UserId == "007"
            && !u.DismissedNotices.Contains(n) );

edited Mar 09 '12 at 01:14

answered Mar 07 '12 at 15:17

Michael Buen

38,643
9
94
118

That wouldn't even compile because `Where()` expects a predicate. – TheCloudlessSky Mar 07 '12 at 16:32
I forgot to change the second `Where` to `Any` – Michael Buen Mar 08 '12 at 00:39
Dude - it worked! It only does one query, slightly different than the SQL but roughly equivalent. Thanks! – TheCloudlessSky Mar 08 '12 at 15:31

score 0 · Answer 3 · answered Apr 17 '20 at 15:34

Hi I made it for Your sample Notice and DismissedNoticeToUser entities.

var userIdParam = 111;

var results = session.GetSession().CreateCriteria(typeof(Notice));

var notInSubQuery = DetachedCriteria.For<DismissedNoticeToUser>()
                        .Add(Expression.Eq("userId", userIdParam))
                        .SetProjection(NHibernate.Criterion.Projections.Id());

results.Add(Subqueries.PropertyNotIn("id",notInSubQuery));

var result = results.List<Notice>();

Notice, that "userId" and "id" are names of primary key column from entities, not from db. I didn't test exactly Your case, bus something very similar worked for me perfectly.

Michael Buen · Answer 4 · 2012-03-07T13:58:40.230

0

You're almost there, change Contains to Any:

string userId = "007";

this.session.Query<Notice>().Where( n => 
    !n.Users.Any(x => x.UserId == userId) );

edited Mar 07 '12 at 13:58

answered Mar 07 '12 at 13:31

Michael Buen

38,643
9
94
118

Thanks, but `DismissedNotices` is a property of `User`, not `Notice`. – TheCloudlessSky Mar 07 '12 at 13:43
Overlooked on my part. By the way, your mapping is many-to-many, so your Notice must have Users list too, right? If it is, you can use that Users in your Notice's where clause. Check my edit – Michael Buen Mar 07 '12 at 14:01
Notice doesn't have a `Users` property, it doesn't need to and doesn't make sense. A notice/user pair is added to the `DismissedNoticeToUser` table when a notice is dismissed. – TheCloudlessSky Mar 07 '12 at 14:13
It's good to have(Users on Notice entity), though you might not have a need for it.. yet. If your entities are truly many-to-many, when A have Bs, then it follows that B have A's too, e.g. Language has many Speakers, Speaker has many Languages. I think it would be more natural for you to map Users on your Notice entity. Doing so, coding Linq against your entities will *freely flow*; then the above answer will work – Michael Buen Mar 07 '12 at 14:53
Sorry but it's not true. Users don't have many Notices, they have many *dismissed* notices. If you look at the OP, you'll see what I mean. – TheCloudlessSky Mar 07 '12 at 15:02

How to translate 'where not in' into an NHibernate query where the subquery table is not an entity?

4 Answers4

Edit