NHibernate, map a collection where key can be two different columns

Question

There's an entity A.

In addition, there's an entity B which has two associations with A.

A has a collection of B.

This collection must load any B if one of associated A is the parent of loaded A.

Problem is collection mapping on A must filter children based on checking if one of two A associations is the parent one.

How can I achieve that?

Note: Order doesn't matter, so you can suggest some mapping using bag.

Note 2: Please suggest how to achieve that using an XML mapping, I won't do it in code.

UPDATE: Real-world scenario:

it's all about a friendship implementation. I want to map a collection of Friendship in an entity UserProfile. Friendship has two associations representing the relation: OneUser, OtherUser. If I want to get all friends for me, I need to check both properties, because one is my friend if one of both properties are myself.

Answer 1

This isn't really an answer, but I needed to be able to format and not have a character restriction.

I had this same problem, but because the solutions weren't very easy I decided to work around it instead by modifying my structure.

The few things I looked at that seemed promising were SQLQueries that return objects as an alias that was Joined via Queryover.

Or - adding a .Where clause to the "hasMany" mapping. Everything I have read about this option says it's very limited. Though the logic to do what you're asking seems pretty simple - Select Distinct ... From ... Where (OneUser = UserId or OtherUser = UserId)

EDIT:

Take a look at the JoinQueryOver here: http://nhibernate.info/blog/2009/12/17/queryover-in-nh-3-0.html

And Native SQL queries here: http://knol.google.com/k/nhibernate-chapter-14-native-sql#

While this isn't a mapping-level solution, you could specify criteria in the where clause when you select your users so that their Friends are populated properly. If you can't achieve the filtering you need with the Fluent QueryOver, I think you could make an Alias out of a Native SQL query and JoinQueryOver / JoinAlias to populate the Friends collection accordingly.

I apologize if this is all information you already know. Either way - I'm interested to see the solution to this problem.

** Edit 2: ** I played around with it a bit more, and this is as close as I could get it:

Public Class UserMapping
    Inherits ClassMap(Of User)
    Public Sub New()
        Id(Function(x) x.Id).Column("UserID")
        Map(Function(x) x.Name).Length(50).Not.Nullable().Column("UserName")
        HasMany(Function(x) x.Friends).Inverse.KeyColumn("UserID").Where("Users.UserID in (select Friendships.FriendOne from Friendships where Friendships.FriendTwo = UserID) or UserID in (select Friendships.FriendTwo from Friendships where Friendships.FriendOne = UserID)")
        Table("Users")
    End Sub
End Class

The query doesn't quite work because it keeps putting an additional constraint in the where clause that the UserID needs to equal the outer userId, which defeats the purpose of the subquery.

I had seen another post where two other solutions where offered.

1. Create two child collections. One that maps the friends where the current User is the Primary friend, and another where the current user is the Secondary friend. You could then create a property that would merge these together outside of how NH queries it.

2. Create redundant entries in the database so that each user has an entry where it is the PrimaryUser for all relationships it shares with other people.

Based on these two options - I would probably opt for Option #1.

Answer 2

IF you are willing to move the solution slightly out of the data domain, your users could have a one-to-many relationship of other users they have assigned as friends. This could be protected or private if you don't want it to leak out.

Then, you implement a public property that defines an actual Friendship as requiring the relationship in both directions. Maybe something like:

public List<User> Friends {
    this.assignedFriends.Where(f => f.assignFriends.Contains(this)).ToList();
}

You'll want to make sure you are using a form of 2nd level cache if you do this however, otherwise requests to this property will hammer your database.

---

If instead you need to return a collection of a dedicated Friendship object, it depends on how you are persisting those entries and how you have mapped that class.

One option would be to define Friendship to simply have three columns, a primary key, and two foreign keys back to the User table. You then require a little bit of logic whenever someone tries to add a friend: you first need to check if half of the relationship already exists..

var f = session.Query<Friend>()
    .Where(x => x.User1 == YourCurrentUser)
    .Cacheable()
    .FirstOrDefault();

then if it exists you assign the other half of the relationship

f.User2 = TheOtherUser;

otherwise create a new object:

f = new Friendship();
f.User1 = YourCurrentUser;

and after either branch:

session.SaveOrUpdate(f);

then a Users friends becomes

public property List<Friendship> {
    session.Query<Friendship>()
        .Where(f => 
            f.User1 != null && f.User2 != null
            && (f.User1 == this || f.User2 == this)
        ).Cacheable()
        .ToList();
}

Please note, I'm not near my installation of VS. Please excuse typos or if I've made obvious blundering errors (it's 2:30am where I am presenltly)