Gigi2m02 Gigi2m02 - 1 year ago 82
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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download