Summary
In 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 ERD
First, 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 Data
I used the following data for this sample:
The top table is the student table, the middle table is the class table and the bottom table is the connecting studentclass table.
The C# Code
On to the code. I’m trying to demonstrate a very simple example. This may not suit your purposes, but it does demonstrate a very easy way to perform a many-to-many situation. In this example, there are many students in a class and students can have many classes. Here are my three tables:
Class table (see Class.cs file):
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: