Fluent-NHibernate : how to map one-to-many relationship into 3 tables (many-to-many, kinda)

Question

I have to 2 entities like this:

class A
{
   int id { get; set; }
   string Name { get; set; }
}

class B
{
   int id { get; set; }
   A RefToA { get; set; }
   string Name { get; set; }
}

How can I map this 2 classes so that i would have 3 tables like this:

• table A with 2 columns: id and name

• table B with 2 columns: id and name

• table AB with 2 columns: AId and BId

Answer 1

This is a typical ManyToMany relationship. In FluentNHibernate you would use

HasManyToMany<T>();

http://wiki.fluentnhibernate.org/Fluent_mapping#HasManyToMany_.2F_many-to-many

Answer 2

what you want is References(x => x.RefToA);

The following below is from the fluent nhibernate documenation.

References / many-to-one

References is for creating many-to-one relationships between two entities; you're referencing another entity, so you use the References method. A reference is to a single instance of an entity

http://wiki.fluentnhibernate.org/Fluent_mapping

Answer 3

If I understand this correct you are creating a ref table because you want the reference to be nullable. If that is the case, you do not need a ref table. Simply set the FK in table b as nullable. Then you can map it a simple reference. Then you would have tables like this:

• table A with 2 columns: id and name
• table B with 2 columns: id, name and Aid (nullable)

And you can map it like this:

public class AMap : ClassMap<A>
{
  public AMap()
  {
      Id(x => x.Id); 
      Map(x => x.Name);
  }
}

public class BMap : ClassMap<B>
{
  public BMap()
  {
     Id(x => x.Id); 
     Map(x => x.Name);
     References(x => x.RefToA);
  }
}

Update

There is no way of mapping this how you want in nhibernate (and no other orm for that matter). The reason for this is quite simple: it violates quite a few rules and there is never a reason to do it this way. The correct way to do this is to have a nullable fk reference in table b. That is how you represent a reference in a sql database. It is simply bad design to use many-to-many when you mean one-to-many and it will most certainly give you trouble later on.

Answer 4

You should not be mapping a one-to-many relation as a many-to-many relation, then forcing it to be a one-to-many. Its bad design, and does not promote data integrity. If I were you, I would create your tables as follows

CREATE TABLE A(
    Id INT PRIMARY KEY IDENTITY(1,1),
    --Your other columns
)

CREATE TABLE B(
    Id INT PRIMARY KEY IDENTITY(1,1),
    AId INT REFERENCES A NULL,
    --Your other columns
)

That way, you can map them the way you want to in Fluent NHibernate, or any other ORM.