Fluent NHibernate HasManyToMany the Normal Way

Summary

As I mentioned in my last blog post, there is a simple way to treat a many-to-many data relationship involving basic mapping and handling the connection using the LINQ query itself. Now I’m going to show how the many-to-many mapping occurs involving more mapping commands and one less table (the connecting table called StudentClass).

Code Changes

First, I commented out the StudentClass object. We’re not going to need this table. Instead, the table will be referenced in the HasManyToMany mapping method inside both the Student object and the Class object. Next, I modified my Student object as so:

public class Student
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual IList<Class> Class { get; set; }

    public Student()
    {
        Class = new List<Class>();
    }
}

public class StudentMap : ClassMap<Student>
{
    public StudentMap()
    {
        Id(u => u.Id).GeneratedBy.Identity();
        Map(u => u.Name).Nullable();
        HasManyToMany(u => u.Class)
            .Table("studentdata..studentclass")
            .ParentKeyColumn("StudentId")
            .ChildKeyColumn("ClassId");

        Table("studentdata..Student");
    }
}

Don’t forget to add a using for System.Collections.Generic (not shown in this code snippet), otherwise you’ll get a syntax error when trying to use an IList or List command. You’ll need a constructor for the IList for the insert CRUD operation. If you don’t include the constructor to create a new list, you’ll have no problem querying existing data, but your Class.Add(myclass) will not work.

The mapping for the Student class includes a new HasManyToMany mapping. The tricky part was getting the right fields for ParentKeyColumn and ChildKeyColumn. These refer to the two columns in the StudentClass table (which must also be specified using the “Table” mapping). The ParentKeycolumn always points to the current table containing the mapping. The Child points to the table containing the records that go into the IList.

OK, so I also modified the Class object to do the exact same thing in reverse:

public class Class
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual int TeacherId { get; set; }
    public virtual IList<Student> Student { get; set; }

    public Class()
    {
        Student= new List<Student>();
    }
}

public class ClassMap : ClassMap<Class>
{
    public ClassMap()
    {
        Id(u => u.Id).GeneratedBy.Identity();
        Map(u => u.Name).Nullable();
        HasManyToMany(u => u.Student)
            .Table("studentdata..studentclass")
            .ParentKeyColumn("ClassId")
            .ChildKeyColumn("StudentId");

        Map(u => u.TeacherId).Not.Nullable();

        Table("facultydata..Class");
    }
}

Again, I added an IList, this time creating a list of Student class objects. Then I did the exact same mapping with the ParentKeyColumn and ChildKeyColumn in reverse.

The LINQ that I used for testing the new mapping looks like this:

using (ISession db = MSSQLSessionFactory.OpenSession())
{
    var query = (from s in db.Query<Student>()
                 select s 
                 ).OrderBy(x => x.Name).ToList();

    Console.WriteLine("List of classes each student has");
    foreach (Student studentitem in query)
    {
        foreach (var classitem in studentitem.Class)
        {
            Console.WriteLine(studentitem.Name.Trim() + " " + classitem.Name.Trim());
        }
    }

    Console.WriteLine();

    var query2 = (from c in db.Query<Class>()
                  select c).OrderBy(x => x.Name).ToList();

    Console.WriteLine("List of students in each class");
    foreach (Class classitem in query2)
    {
        foreach (var studentitem in classitem.Student)
        {
            Console.WriteLine(classitem.Name.Trim() + " " + studentitem.Name.Trim());
        }
    }

    Console.ReadKey();
}

I did two queries, one listing the classes for each student to make sure the output matched my previous example exactly. Then I listed the students in each class to see if the reverse made sense.

CRUD Operations

This took some trial and error for me to get the insert to work right. Initially, I thought that I needed an “inverse()” mapping, but later realized that it was unnecessary. Here’s an example of inserting a student, a class and then connecting the two together:

// insert new student
Student student = new Student()
{
    Name = "New Student"
};

db.Save(student);

// insert a new class to the student
Class tempclass = new Class()
{
    Name = "CSC 300",
    TeacherId = 1
};

db.Save(tempclass);

student.Class.Add(tempclass);
db.Save(student);

db.Flush();

This entire operation will save a new student record, then a class record. The class is then added to the student list and saved again, but a Flush() is necessary, otherwise, the StudentClass connecting record will not be created. Also, if you put a break-point at the db.Save(student) line of code and look at the student object, you’ll see that the id is set to zero. After the save, NHibernate backfills the unique id from the database into the id field.

Next, I performed a delete to see if it cascaded correctly:

// Test delete student and connector, but not class
using (db.BeginTransaction())
{
    db.Delete(student);
    db.Transaction.Commit();
}

As it turns out, the operation above will delete the student and any StudentClass records for that student, but it will correctly leave the class intact. One thing to note is that the Class object is not reloaded with the new student record. So if you try and list all students in each class as before, you’ll get the same list of classes and students as before the insert. In other words, the new class and student are not loaded back automatically. To avoid this problem, close the context after performing an insert (or more appropriately, use one context for the insert operation alone), then open a new context to re-read the data from the database.

Now, let’s do the Class delete:

using (ISession db = MSSQLSessionFactory.OpenSession())
{
    var tempclass = (from c in db.Query<Class>() 
                     where c.Name == "CSC 300" 
                     select c).FirstOrDefault();
 
    // Test delete class and connector, but not student
    using (db.BeginTransaction())
    {
        db.Delete(tempclass);
        db.Transaction.Commit();
    }
}

This delete will remove the Class and the connecting StudentClass record but leave the Student intact. I’m not going to go through the updates in this blog post, since they are normally trivial.

One more thing I’d like to mention is that there is a LazyLoad() mapping that can be added to both HasManyToMany mappings (both the student and class objects). This can improve performance in situations where there are a lot of students and a/or a lot of classes.

Conclusion

I still have not performed a speed comparison between this method and the simple many-to-many method I used in my last blog post. I also have not done the CRUD operations for the simple method in my last blog post. I’ll save that for another post (I know, you want it NOWWW!!!). I apologize for leaving you in suspense. The HasManyToMany mapping turned out to be easier than I had expected. To be honest, I struggled with performing a HasManyToMany mapping on another table setup that I’ll also blog about in the future. The instance I’m talking about involves a composite key situation with the connector table. You may have noticed that my StudentClass table contains a primary key that is separate from the two foreign connector keys. This made my simple method easy because I can define the key as Id field and treat the other two fields as separate map fields. When I attempted to use a two-field StudentClass table with both foreign keys setup as a composite primary key, I discovered that there is a very complex mapping operation involving an override to the equal that is necessary because NHibernate doesn’t handle this situation directly. I’m planning to alter the StudentClass table and make it work so I can demo this situation for anybody that is stuck on composite keys (I saw a lot of stack overflow issues involving this problem, with varying methods of solving it).

You can download the code for this project here:

FluentNHibernateManyToMany.zip