Fluent NHibernate Many-to-Many Without Using HasManyMany

SummaryIn my last Fluent NHibernate post I showed how to do a One-to-Many mapping using the HasMany mapping method and ILists.  Getting the mappings to work correctly was a bit painful, and the outcome was questionable.  So I thought I’d try my hand at mapping a many-to-many situation without using the HasManyToMany mapping method or ILists.The ERDFirst, I modified my Teacher, Student, Class databases I used in previous examples.  This is what I setup:

I’m sticking with two databases for now.  This will show as a table mapping in each mapping class.  Also, I left the teacherid foreign key inside the class table.  You can remove this field if you want to setup your own example.The DataI used the following data for this sample:

public class Class
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual int TeacherId { get; set; }
}

public class ClassMap : ClassMap<Class>
{
    public ClassMap()
    {
        Id(u => u.Id).GeneratedBy.Identity();
        Map(u => u.Name).Nullable();
        Map(u => u.TeacherId).Not.Nullable();

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

Student table (see Student.cs file):

public class Student
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
}

public class StudentMap : ClassMap<Student>
{
    public StudentMap()
    {
        Id(u => u.Id).GeneratedBy.Identity();
        Map(u => u.Name).Nullable();

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

And finally, the StudentClass table (see StudentClass.cs file):

public class StudentClass
{
    public virtual int StudentClassId { get; set; }
    public virtual int StudentId { get; set; }
    public virtual int ClassId { get; set; }
}

public class StudentClassMap : ClassMap<StudentClass>
{
    public StudentClassMap()
    {
        Id(u => u.StudentClassId).GeneratedBy.Identity();
        Map(u => u.StudentId).Not.Nullable();
        Map(u => u.ClassId).Not.Nullable();

        Table(“studentdata..StudentClass“);
    }
}

All the table objects are very simple and straightforward. I did not change anything in the SessionFactory object from previous examples. You can download the code at the end to get a complete running example.

The Query

I formed a query to spit out the student names next to the class that the student is assigned to:

using (ISession db = MSSQLSessionFactory.OpenSession())
{

    var query = (from c in db.Query<Class>()
                 join sc in db.Query<StudentClass>() 
                   on c.Id equals sc.ClassId
                 join s in db.Query<Student>() 
                   on sc.StudentId equals s.Id
                 select new { s, c }
                 ).OrderBy(x => x.s.Name).ToList();

    foreach (var item in query)
    {
        Console.WriteLine(item.s.Name.Trim() + ” ” + item.c.Name.Trim());
    }

    Console.ReadKey();
}

As you can see, the query is nothing more than a join between the three tables and then I selected two of the tables for output. I then ordered by the student name.

The Output

Here is the expected output:

Conclusion

Sometimes building a database and created the ORM mappings can be simple. The database itself can be setup with the correct referential integrity. Also, I did not perform any CRUD operations on this example (yet). If you decide to do an insert, remember the Class record and the Student record must exist before you can create a connecting record in the StudentClass table (yeah, I know, that’s database 101 knowledge).

I’m still planning to show the code using the HasManyToMany mapping of this setup. The mappings will be more complex and I would also like to do CRUD operations for both to compare the speed and difficulty in setting up the ORM code (and queries) for each.

For now, you can download the code here: FluentNHibernateEasyManyToMany.zip

Leave a Reply