NHibernate QueryOver distinct

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 cannot do

TransformUsing(Transformers.DistinctRootEntity)

because this will give me the 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 the fluent mappings of:

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 retrieve a list of distinct "User" 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 a In clause in the SQL to give you a distinct list of User.

Answer 2

I know this post is old but I just came across the same problem and thought I would share an answer I found to be much simpler.

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

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

Based on this question: LINQ's Distinct() on a particular property the answer with the most +'s gives a very eloquent solution. Create another list, and have LINQ do the distinct comparison. If we could do distinct at the database it would clearly be the better option - but since that's not an option, LINQ seems to be a good solution.