Fluent NHibernate: Many to Many mapping on a single table

Question

I have a Table of Companies, and I'm trying to build a matrix of competitors between them.

For example: McDonalds is a Competitor of Wendy's, and vice-versa.

Here's the mappings I've tried:

HasManyToMany(x => x.Competitors)
.ParentKeyColumn("IssuerID")
.ChildKeyColumn("CompetitorID")
.Table("Competitors")
.Cascade.All().Not.LazyLoad();

as well as:

  Table("Issuer");
            Id(x => x.Key).Column("Id").GeneratedBy.Assigned();
            Map(x => x.Name).Length(1000);   
  HasManyToMany(x => x.Competitors).ParentKeyColumn("IssuerID").ChildKeyColumn("CompetitorID").Table("Competitors").Cascade.All().Not.LazyLoad();

When I add a competitor to an Issuer, I can see the correct mapping in the database.

So If I do:

McDonalds.AddCompetitor(Wendys);

I will see the correct data in the DB. I will also see the correct DB in the Entities when i get McDonalds using NHibernate.

But, if I return Wendy's from Nhibernate and look at the Competitors object:

Wendys.Competitors

I don't see McDonalds. I can understand why because Wendy's was added to McDonalds as a Child.

How can I modify this so I can view the competitors from both directions?

check this if it helps - https://stackoverflow.com/questions/9108083/fluent-nhibernate-many-to-many-mapping-way — Mukesh Chudasama, Jul 13 '17 at 11:58

Uwe Hafner · Accepted Answer · 2017-07-17T11:19:11.143

I came up with the following solution in my local tests. Actually I am not really satisfied as I would hope for a better support. Maybe there is. If someone finds it please post.

Having a store like this (shrinked down to the relevant parts of competitors):

public class Store
{
    public virtual int Id { get; protected set; }

    public virtual IList<Store> Competitors { get; set; }
}

I used the following mapping (same as you):

HasManyToMany(x => x.Competitors)
    .ParentKeyColumn("Store_Id")
    .ChildKeyColumn("Competitor_Id")
    .Table("CompetitorsMapping");

As you state it does not work with this. But from the examples provided in the Git Repo of fluentnhibernate it wraps the add of the lists anyway.
The "FirstProject" example does something similar by adding an employee to a store. It first sets the store on the employee and then adds the employee to the Staff of the store (okay a bit different then). But it automatically takes care of dependencies.

So you could solve your problem by not setting directly but by wrapping the add and remove (From your sample code it looks like you already do that):

public class Store
{
    public virtual void AddCompetitor(Store competitor)
    {
        if (!Competitors.Any(x => x.Id == competitor.Id))
        {
            competitor.Competitors.Add(this);
            Competitors.Add(competitor);
        }
    }

    public virtual void RemoveCompetitor(Store competitor)
    {
        competitor.Competitors.Remove(this);
        Competitors.Remove(competitor);
    }
}

What I especially don't like is the contains check on Competitors. Don't know how it performs and behaves. But without you can add the same competitor over and over. Making the list long.

I am new to fluentnhibernate. Maybe some setting could prevent the double adds automatically. I'd be interested in that setting.

You should not use contains on the object isself but do !Competitors.Any(x => x.Id == competitor.Id). Reason: If the 2 objects are from different sessions you 'll get different objects/references from nHibernate and the Contains will return false. The methods are called convinient methods and are best practice ATM — Dominik, Jul 17 '17 at 11:13
Reading this led me to my solution so I'm marking it as an answer - thanks — Ryan Ternier, Jul 18 '17 at 20:33
@RyanTernier Sounds like you did something different? I would be very interested in your solution. I want to use fluentnhibernate in my next project and I usually stumble upon such problems as well. — Uwe Hafner, Jul 19 '17 at 07:33
The system I took over really encapsulated F NHibernate to the point you can't even write complex linq queries to get the data. I was trying to do it with only an Issuer entity, without using a Competitor. THe main difference was when I added a Competitor, I had to then go and add the Issuer as a competitor to the other Issuers. Ex: A <> B, then do B <> A — Ryan Ternier, Jul 19 '17 at 16:12

Fluent NHibernate: Many to Many mapping on a single table

1 Answers1