I'm working on a case were I normally, in SQL, should have an 'except' keyword. At this moment I didn't found a solution to accomplish this in NHibernate.
It's a solution where you have two tables: The user table and a FK table (including a PK for easy work in NH). The meaning of the SQL below is to give al the records for the users that aren't tagged yet by a certain user. So if we have 3 users: John, Jane en Jelain. If John tagged nobody: give back jane and jelain If John tagged Jane: give back Jelain If John tagged both Jane and Jelain: give back nothing.
SQL to get users that aren't tagged yet:
SELECT Id, DisplayName, Date, ProfilePicUrl
FROM MyDB.dbo.Users u
EXCEPT
SELECT u.Id, Displayname, Date, ProfilePicUrl
FROM MyDB.dbo.Users u
FULL OUTER JOIN MyDB.dbo.TaggedUsers t
ON u.Id=t.TargetId
WHERE
t.ShooterId = '1234' OR
u.Id = '1234'
I have been checking the two answers by Anton and Bernhardrusch and gave it a try.
When reading one answer I thought it was possible to rewrite my query with a Left outer join and filter out the records with NOT INs.
This brought me to this code:
SELECT *
FROM MyDB.dbo.Users u
LEFT OUTER JOIN MyDB.dbo.TaggedUsers t ON u.Id=t.TargetId
WHERE t.ShooterId <> '6A17DC45-AB54-4534-B13B-A02001347664'
AND u.Id <> '6A17DC45-AB54-4534-B13B-A02001347664'
OR t.ShooterId is null
Translated it with Criteria API, but unfortunately this code doesn't work when using multiple users.
At this moment I decided to work it out with code instead of SQL.
So I will do two queries (select all users & select all tagged user corresponding an user id) with .future attached to it and filter out the first with the latter.
If someone knows a better solution. Please let me know.
Maybe is except coming into NHibernate later on. We'll see. I'll give my own answer as the answer to this question for now.