NHibernate QueryOver

Question

I have this Scenario:

class User { Id, UserName } class UserRelationship { User GroupUser, User MemberUser } and query var query = QueryOver.Of<UserRelationship>() .JoinqueryOver(x=>x.MemberUser) .Where(x=>x.UserName == "TestUser"); 

Now I want to return List Distinct User, so I can not do

TransformUsing (Transformers.DistinctRootEntity)

because it will give me UserRelationship.

I need something like this:

 Select distinct user.ID from UserRelationship relationship inner join User user on user.ID = relationship.MemberUser_ID 

Please help thanks

Answer 1

Given the classes:

 public class User { public virtual int Id {get; set;} public virtual string UserName {get; set;} } public class UserRelationship { public virtual int Id {get; set;} public virtual User GroupUser {get; set;} public virtual User MemberUser {get; set;} } 

And quick displays:

 public class UserMap : ClassMap<User> { public UserMap() { Id(x=>x.Id).GeneratedBy.Native(); Map(x=>x.UserName); } } public class UserRelationshipMap : ClassMap<UserRelationship> { public UserRelationshipMap(){ Id(x=>x.Id).GeneratedBy.Native(); References(x=>x.GroupUser); References(x=>x.MemberUser); } } 

You want to get a list of individual "Users" based on "MemberUser" from the UserRelationship class.

 var distinctMemberUsers = QueryOver.Of<UserRelationship>() .Select(x => x.MemberUser.Id); var users = QueryOver.Of<User>() .WithSubquery.WhereProperty(x=>x.Id).In(distinctMemberUsers) 

This should use the In clause in SQL to provide you with an excellent list of users.

Answer 2

I know this post is old, but I just ran into the same problem and thought that I would share an answer, which, it seemed to me, is much simpler.

No matter what - NHibernate will request multiple rows for each parent object (unless you use SubSelect instead of Join). Because of this, we know that we are going to get a list of, say, 500 objects, when in fact there are only 100 unique objects.

Since these objects are already requested and already in memory - why not use LINQ?

Based on this question: LINQ's Distinct () for a particular property, the answer with the highest + gives a very eloquent solution. Create another list and LINQ will make a clear comparison. If we could make a difference in the database, that would be the best option - but since it is not an option, LINQ seems like a good solution.