Gigi2m02 Gigi2m02 - 1 year ago 53
C# Question

How to 'translate' the SQL's EXCEPT to (N)Hibernate (Criteria API)?

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
SELECT u.Id, Displayname, Date, ProfilePicUrl
FROM MyDB.dbo.Users u
FULL OUTER JOIN MyDB.dbo.TaggedUsers t
ON u.Id=t.TargetId
t.ShooterId = '1234' OR
u.Id = '1234'

Answer Source

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:

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.