Fluent NHibernate HasMany Mapping

Summary

This post will demonstrate how a one-to-many mapping can be done in Fluent NHibernate. As in previous articles, I’m going to use my facultydata database with two tables to demonstrate the HasMany and References mappings.

The Database

First, we’ll need to make sure that both tables have primary keys. Then add a foreign key constraint from the teacher table primary key to the class teacherid foreign key field. You can use this MS SQL script to do it:

ALTER TABLE [dbo].[class]  WITH CHECK ADD  CONSTRAINT [FK_Class_Teacher] FOREIGN KEY([teacherid])
REFERENCES [dbo].[teacher] ([id])
ALTER TABLE [dbo].[class] CHECK CONSTRAINT [FK_Class_Teacher]

Now create or modify your Class table mapping in C# to look like this:

public class Class
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual Teacher Teacher { get; set; }
}
 
public class ClassMap : ClassMap<Class>
{
    public ClassMap()
    {
        Id(u => u.Id).GeneratedBy.Identity();
        Map(u => u.Name).Nullable();
        References(x => x.Teacher)
            .Column("TeacherId")
            .Not.Nullable(); 
    }
}

The first thing you should notice is the missing foreign key defined in the class itself. This has been replaced by a pointer to the Teacher class. This will be used as a track back in the ORM itself. The mapping has been altered to include a “References” section.

You’ll also need to modify your Teacher table class and mappings like this:

public class Teacher
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual IList<Class> Classes { get; set; }
 
    public Teacher()
    {
        Classes = new List<Class>();
    }
}
 
public class TeacherMap : ClassMap<Teacher>
{
    public TeacherMap()
    {
        Id(u => u.Id).GeneratedBy.Identity();
        Map(u => u.Name).Nullable();
        HasMany(u => u.Classes)
            .KeyColumn("TeacherId")
            .Inverse()
            .Cascade.All();
    }
}

The Teacher object has been modified to add an IList of Class objects.  The point of this exercise is to connect these two objects (Teacher and Class) together so that the classes that belong to each Teacher is automatically loaded when the teacher records are queried.  I also added a constructor to the Teacher object to make sure that the IList is not null if the user creates a new Teacher record.

The real magic happens in the mapping.  The HasMany is tricky to get to work properly.  First is the KeyColumn setting, which must point to the foreign key in the Class table.  Second the Inverse() must be used because the primary keys of the child records (Class) are not null.  Last, the .Cascade.All() needs to be used for the cascade delete operation.  I did not put a cascade delete in MS SQL server.  I imagine that it would just be easier to put one in and the database can take care of itself when deleting a teacher record.  For this demonstration, I wanted to see if the ORM would do cascades on its own.

One other thing of note:  I setup the primary keys to be identity keys in MS SQL server (meaning MS SQL will create a unique primary key on inserts).  To take advantage of this feature, I also setup the primary keys in the ORM to have the .GeneratedBy.Identity().  One hazard of this is that the primary key is not filled in until the record is created (because the key doesn’t exist until MS SQL gives it one).  Fortunately, NHibernate will re-read the key back into the object after save.

The CRUD Operations

Here are the CRUD operations that I used to test this mapping:

using (ISession session = MSSQLSessionFactory.OpenSession())
{
    // Test query of data by selecting only the teacher records
    var query = (from t in session.Query<Teacher>()
                    select t).ToList();
 
    foreach (var teacheritem in query)
    {
        foreach (var classitem in teacheritem.Classes)
        {
            Console.WriteLine("Class:" + classitem.Name.Trim() + " Teacher:" + teacheritem.Name.Trim());
        }
    }
 
    // Test inserts
    Teacher newteacher = new Teacher() { Name = "Delete ME" };
    session.Save(newteacher);
 
    Class newclass = new Class() { Name = "TECH01", Teacher = newteacher };
    newteacher.Classes.Add(newclass);
    session.Save(newclass);
 
    newclass = new Class() { Name = "TECH02", Teacher = newteacher };
    newteacher.Classes.Add(newclass);
    session.Save(newclass);
 
    // Test update parent and children
    using (session.BeginTransaction())
    {
        newteacher.Classes[0].Name = "NONTECH01";
        newteacher.Classes[1].Name = "NONTECH02";
        newteacher.Name = "Delete 2";
        session.Update(newteacher);
        session.Transaction.Commit();
    }
 
    // Test cascade deletes
    using (session.BeginTransaction())
    {
        session.Delete(newteacher);
        session.Transaction.Commit();
    }
 
    Console.ReadKey();
}

The query itself is trivial. The inserts I had some trouble with. There are a lot of articles about how to insert parent child records. My simple fix is to save after each object is created (guaranteeing that the primary key is read back into the object). If you step through the code you’ll see the primary key of the newteacher and the primary keys of each newclass being set right after the save method is called.

After the inserts, I performed an update. I wanted to test the ability of the ORM to save all changes to the parent and child records with one save. So I setup a transaction, changed the name of the teacher record and all it’s children, then committed it. Then I checked the database, and it all updated as expected.

Last, I performed a cascade delete by deleting only the teacher record. If you attempt to manually delete the teacher record from MS SQL server, you’ll get a foreign key error. I checked the database after this operation and all records were deleted as expected.

Download the whole test project here: FluentNHibernateMappings.zip