3

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'
Gigi2m02
  • 1,238
  • 3
  • 17
  • 33

3 Answers3

2

I think there's no EXCEPT in ICritearia, but you can use "Not IN":

session.QueryOver(T).WhereRestrictionOn(t => t.SomeProperty).Not.IsIn(object[])

I think it's the same, look at:

http://blog.sqlauthority.com/2007/05/22/sql-server-2005-comparison-except-operator-vs-not-in/

Anton
  • 1,583
  • 12
  • 17
1

Well - I think you can translate your statement to a statement using outer joins:

select id, DisplayName, Date, ProfilePicUrl
from MyDB.dbo.Users u 
right outer join MyDB.dbo.TaggedUsers t on u.id = t.targetId
where t.ShooterId = '1234' or u.ID <> '1234' and t.ShooterId is null

and this could be translated to a criteria statement - but I would have to know your mapping to be able to do this

.CreateAlias("TaggedUsers", "t", NHibernate.SqlCommand.JoinType.LeftOuterJoin)
.Add(Restrictions.IsNull("t.ShooterId")
.Add(Restrictions.Not(Restrictions.Eq("ID));

It's always hard to create this without testing - but I hope you get my idea.

EDIT

well - it looks like it should be a left outer join - not a right outer join

Community
  • 1
  • 1
bernhardrusch
  • 11,670
  • 12
  • 48
  • 59
  • That's not really the output that I'm looking for. It's the opposite output. I need the users that aren't tagget yet by one user ;-) Thought i could solve it with joins after seeing your code, but too bad. Watch my answer below. – Gigi2m02 Apr 02 '12 at 08:19
  • I think a left outer join should be right and working correctly - then you should get the not tagged users – bernhardrusch Apr 02 '12 at 11:34
0

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Gigi2m02
  • 1,238
  • 3
  • 17
  • 33
  • what do you think with using multiple users ? can you give the exact statement that is not working ? – bernhardrusch Apr 02 '12 at 11:35
  • There is no error statement, but the output isn't right. e.g.: If I log in with Shakira and tag John en Jane, and I do the same with Britney. Then when I login with Gaga I'll bee seeing John and Jane twice in my list of users. And that's logical if you think of the SQL and the tables. – Gigi2m02 Apr 02 '12 at 15:33
  • If you're using criteria API you can use .SetResultTransformer(NHibernate.Transform.Transformers.DistinctRootEntity). There are similar constructs for queryover or the linq style query – bernhardrusch Apr 03 '12 at 05:56