NHibernate self-join many-to-many symmetric relationship (person friends problem)

Question

Is there any way to set-up a symmetric self-join relationship mapping in NHibernate? Suppose we have two tables:

Users
  id

Relations
  id
  user1
  user2
  relation_type

The User and Relation classes should look like this:

class User
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual ISet<Relation> Relations { get; set; }
}

class Relation
{
    public virtual int Id { get; set; }
    public virtual User User1 { get; set; }
    public virtual User User2 { get; set; }
    // Let's leave the RealationType as string for the sake of brevity 
    public virtual string RelationType { get; set; }
}

I do NOT want the relations table to have two rows for the same relation. But the relation MUST be symmetric, which means if there's a relation between two users, A and B, the Relations collection of the user A must contain a relation with user B and the relations of user B must contain a relation to A.

It sounds almost like a challenge. But, can someone solve this? Please, if you can, post the xml mapping. I'm not using Fluent.

Answer 1

You can use Key-Many-To-One mapping and remove the Id field from the relation entity. Also you better use inheritance for different relation types.

Answer 2

I doubt it. If you think about the manual SQL query you'd need to write to pull a User & all his Relations out in an outer join query, you can see why NHibernate would struggle to generate something like this. Updates would be an even bigger headache - how do you decide which ids go in which field for a new Relation?

If you're stuck on this model, all I can suggest as a workaround is to map two private collections and implement a Union()ed read-only public collection. Implement update/remove methods that locate & modify the appropriate relation, and a round-robin Add() method. You won't have any NHibernate query support for queries on this collection.

Your other option is to change your data model so that User has a many-to-many relationship to Relation (eg a UserRelation table), rely on application code to enforce a 'two users per relation' rule, and add convenience methods like IList<User> GetRelations(RelationType)