Fluent NHibernate one-to-many with intervening join table?

Question

I'm having trouble getting the Fluent Nhibernate Automapper to create what I want. I have two entities, with a one-to-many relationship between them.

class Person
    {
        public string name;
        IList<departments> worksIn;

    }

    class Department
    {
        public string name;
    }

The above is obviously bare bones, but I would be expecting to generate the fleshed out schema of:

Person{id, name}
Department{id, name}
PersonDepartment{id(FK person), id(Fk Department)}

Unfortunately, I am instead getting:

Person{id, name}
Department{id, name, personid(FK)}

I don't want the FK for Person included on the department table, I want a separate join/lookup table (PersonDepartment above) which contains the primarykeys of both tables as a composite PK and also Fks.

I'm not sure if I am drawing up my initial classes wrong (perhaps should just be LIst workIn - representing ids, rather than List worksIn), or if I need to manually map this?

Can this be done?

Ian Nelson Ian Nelson · Accepted Answer · 2011-09-26T21:35:58

The way the classes have been structured suggests a one-to-many relationship (and indeed that's how you describe it in your question), so it should not be a surprise that FNH opts to model the database relationship in that way.

It would be possible, as you suggest, to manually create a many-to-many table mapping. But, is this definitely what you want?

I tend to find that pure many-to-many relationships are quite rare, and there is usually a good case for introducing an intermediate entity and using two one-to-many relationships. This leaves open the possibility of adding extra information to the link (e.g. a person's "primary" department, or perhaps details of their office within each of their departments).

Some example "bare-bones" classes illustrating this kind of structure:

public class Person
{
    public int Id { get; set;}

    public string Name { get; set;}

    public IList<PersonDepartment> Departments { get; set; }
}

public class PersonDepartment
{
    public int Id { get; set; }

    public Person Person { get; set; }

    public Department Department { get; set; }

    public bool IsPrimary { get; set; }

    public string Office { get; set; }
}

public class Department
{
    public int Id { get; set; }

    public IList<PersonDepartment> Personnel { get; set; }

    public string Name { get; set; }
}

Fluent NHibernate one-to-many with intervening join table?

1 Answers