Fluent NHibernate and friend relationship

Question

I need to model a friend relationship with Fluent NHibernate. My company model has a List<Company> Related with related companies. Relations between companies are modeled in my database in a table, related which looks like this:

customer_id | related_id

Both columns is a foreign key to the PK in my customers table.

The problem is that relations are only saved once for each pair (do you call it bi-directional?).

I'm able to change the table structure if it's easier to solve in another way.

I need to map Fluent NHibernate so that when i do customer.Related(), it generates a query like:

SELECT * FROM companies LEFT JOIN related ON customer_id = id OR related_id = id

I've tried to map this in a number of different ways, the closest i've tried is:

HasManyToMany(x => x.Related)
       .Inverse()
       .ParentKeyColumn("customer_id")
       .ChildKeyColumn("related_id")
       .Table("relations")
       .Cascade.All();

However, this (of course) only maps when customer_id matches.

How do I solve this?

Edit: I think it's similar to Fluent NHibernate: How to create one-to-many bidirectional mapping?, but it does not help me much.

@Michael, thanks for your answer. I already has .Inverse(). Or am I missunderstanding you? — alexn

detay detay · Accepted Answer · 2011-05-22T13:08:25

I think what you want to achieve is already half way done. You've mapped 2 entities with a Many2Many relation already. I wouldn't touch mapping any further.

Instead I would query what I want thru that mapping. Something like this.

function GetRelated(long id){

   return Session.Query<Related>()
                 .Where(r=>r.Customer.Id == id || r.Related.Id == id)
                 .ToList(); 
}

A reccomendation tho, the mapped entity's name is Related and you have a related field that might sound confusing, so I'd suggest you to rename it into something else (if possible).

Hope it helps.

Fluent NHibernate and friend relationship

1 Answers