Nhibernate mapping List<Class> where Class has its own table

Question

I'm trying to create mapping for classes:

public class Employer
    {
        public virtual int Id { get; protected set; }
        public virtual string Name { get; set; }
        public virtual IList<Field> Fields { get; set; }
        public virtual string Location { get; set; }
        public virtual string Phone { get; set; }
    }

public class Field
    {
        public virtual int Id { get; protected set; }
        public virtual string Description { get; set; } 
    }

My mappings look like:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="lab" assembly="lab">

  <class name="Employer" table="Employer">
    <id name="Id">
      <generator class="native"/>
    </id>
    <property name="Name" />

    <list name="Fields" cascade="all-delete-orphan">
      <key column="EmployerId" />
      <index column="FieldIndex" />
      <one-to-many class="Field"/>
    </list>

    <property name="Location" />
    <property name="Phone" />
  </class>

</hibernate-mapping>

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="lab" assembly="lab">

  <class name="Field" table="Field">
    <id name="Id">
      <generator class="native"/>
    </id>
    <property name="Description" />
  </class>

</hibernate-mapping>

And code looks like:

var session = configuration.BuildSessionFactory().OpenSession();
var transaction = session.BeginTransaction();
var emp = new Employer();
emp.Name = "Mera";
emp.Fields = new List<Field> ();
emp.Fields.Add(new Field() { Description = "C#" });
emp.Fields.Add(new Field() { Description = "C++" });
emp.Location = "Street";
emp.Phone = "02";
session.Save(emp);
transaction.Commit();

var transaction2 = session.BeginTransaction();
var emp2 = new Employer();
emp2.Name = "Mera2";
emp2.Fields = session.CreateQuery("from Field").List<Field>();
emp2.Location = "Street";
emp2.Phone = "02";
session.Save(emp2);
transaction2.Commit();

This code just substitutes old entries in Field table for new one for the second employer. So Field table still has 2 records and Employer table has 2 records.

As you could understand I need that if 2 fields are equal (the same Description) both of 2 employers should have the same object references on it.

Answer 1

As JamesB has said in his comment this looks like a many-to-many relationship. Many-to-Many relationships require an intermediate table otherwise you will get the behavior you have seen.

To get the desired behavior you will need to create a new table called something like EmployerFields. This table will need to have two foreign key fields, one for Employer and the other for Fields, and a field for the index, FieldIndex. The FKs can be called something like EmployerId and FieldId respectively.

Then the list mapping will then need to be changed as shown below.

<list name="Fields" cascade="all-delete-orphan" table="EmployerFields">
  <key column="EmployerId" />
  <index column="FieldIndex" />
  <many-to-many class="Field" column="FieldId" />
</list>