Entity Framework (EF) handles Many to Many relationships rather elegantly. However, if you need more control, wish to use something other than the default table name, or need to store additional properties in a Many to Many join, there is a little more work than what comes out of the box. Today I am sharing an example of one solution to this common database design.
I am emulating the Contoso education database for this example. I have two entities, Class and Student, that are related in a Many to Many relationship. I wish this relationship to be stored in a table named Enrollment. Also, in the Enrollment table, I wish to include two properties about the student; 1) DaysInAttendance, and 2) Grade.
First I start by creating the data model classes.
In order to create the classes I am importing features from other frameworks. First, I am using NuGet to import EntityFramework. For each data Class I include System.ComponentModel.DataAnnotations.Schema; For the SchoolContext class I include System.Data.Entity;
Here are the data class definitions.
public class Student
{
public int StudentId { get; set; }
public string Name { get; set; }
public virtual ICollection<Enrollment> Enrollments { set; get; }
}
public class Class
{
public int ClassId { get; set; }
public string Name { get; set; }
public virtual ICollection<Enrollment> Enrollments { set; get; }
}
public class Enrollment
{
// Many to Many Join Key Properties
public int StudentiD { get; set; }
public int ClassId { get; set; }
// Properties existing in the instance of a Many to Many Join
public string Grade { get; set; }
public int DaysInAttendence { get; set; }
// Pointers to the join class Instances
public virtual Student Student { get; set; }
public virtual Class Class { get; set; }
}
Out of the box this relationship will not be created by Entity Framework. What we are doing here is creating two different classes, both with a One to many relationship to the Enrollment table. The combination of these two relationships result in a Many to Many relationship between Class and Student.
By default, if we defined these as many to many inside the Class and Student tables, EF would create a table StudentClass, or something of that nature. Moreover, there would not be the ability to add any further properties to the join instance. In order to use our Enrollment table it is necessary to provide more information to EF regarding this intention. This is implemented by overriding the OnModelCreating event in the DbContext used to connect to your database. The following example directs EF in it’s communication with SQL Server as regards Student and Class models as database sets, and the Enrollment class as it interacts with Students and Classes.
public class SchoolContext : DbContext
{
public virtual DbSet<Class> Classes { get; set; }
public virtual DbSet<Student> Students { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
var enrollmentEntity = modelBuilder.Entity<Enrollment>();
// Set or change the table name using ToTable
enrollmentEntity.ToTable("Enrollment");
// Set requirements for non-key column properties
enrollmentEntity.Property(e => e.Grade)
.IsRequired()
.HasMaxLength(1)
.IsUnicode(false);
enrollmentEntity.Property(e => e.DaysInAttendence)
.IsRequired()
.HasColumnType("INT");
enrollmentEntity.Property(e => e.ClassId)
.IsRequired()
.HasColumnType("INT")
.HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
enrollmentEntity.Property(e => e.StudentiD)
.IsRequired()
.HasColumnType("INT")
.HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
enrollmentEntity.HasKey(k => new {k.StudentiD, k.ClassId});
enrollmentEntity.HasRequired(t => t.Class)
.WithMany(t => t.Enrollments)
.HasForeignKey(t => t.ClassId);
enrollmentEntity.HasRequired(t => t.Student)
.WithMany(t => t.Enrollments)
.HasForeignKey(t => t.StudentiD);
}
}
Note: Using this fluent syntax in the DbContext using the properties and methods available in the DbModelBuilder class provides the benefit of keeping your Model classes clean from db specific annotations, and has many more capabilities than those enabled through decorations.
For the Enrollment class, I qualify each of the properties as to its data type, and properties of each column. Then, I create the key for this entity, as a composite key of StudentId , ClassId. This means a student may be in one or more classes, and a class may have one or more students.
Finally, I have to associate the Enrollment into the collections of Class and Student. You see that with the clause, enrollmentEntity.HasRequired(t=> t.Class) saying an Enrollment has a Class (the class property in the Enrollment class), .WithMany(t => t.Enrollments), the Class object has many enrollments in its ICollection<Enrollment> Enrollments property, .HasForeignKey(t=> t.ClassId) meaning use the ClassId property to determine what records belong together.
The same association technique is used to join Students with Enrollments. If you haven’t noticed, these joins are like having cross linked objects. The Class has a collection of Enrollments. Enrollments have a pointer to a Class. Both directions are implemented depending on which class is instantiated.
Regardless of how you create your ORM, this kind of conversion of relationships to objects is tedious. If you think about it, most ORM generators don’t do this well out of the box. As I continue to understand the fluent syntax better, this implementation in EF seems to be a rather good technique for enabling the code generator to make the conversion. I find it much more intuitive and easier to test that performing mapping in an XML schema, etc.
Are you getting into the fluent syntax enabled by the EF DbModelBuilder? Have you found options that work better for your implementations. Share your experience in our comments.
Cheers,
Ben