Entity Framework Advanced

Our client liked first version of Student application, and asked if we can extend it by adding new features. Students should get access to every course. Additionally teachers should be able to give them assessment. Every marks should have weight, so Exam marks will be the most important and homework mark less. Client said that one mark per weight will be good enough for now. We have all requirements, so we can start work.

Many to Many relationship.

From requirements we know that Student may have multiple Courses, and every course may have multiple participants (students). That means we need to change our database and create M to N relationship. It’s common to create additional table to specify many to many relationship. EF needs it, without junction table EF can’t realize we want to create many to many relationship. Let’s get going.

public class StudentCourse
{
    public int CourseId { get; set; }
    public int StudentId { get; set; }
    public int AssessmentId { get; set; }
    public Student Student { get; set; }
    public Course Course { get; set; }
    public Assessment Assessment { get; set; }
    public float Mark { get; set; }
}

This is our junction table, it needs FKs and entities for every table that it going to link. As you can see it will link Course, Student and Assessment. At the end we’ve added Mark, it will be mark with weight (Assessment) for one student for course.

public class Assessment
{
    public int Id { get; set; }
    [Required]
    public float Weight { get; set; }
    [Required, StringLength(50)]
    public string WeightType { get; set; }
    public ICollection<StudentCourse> StudentCourses { get; set; } = new HashSet<StudentCourse>();
}

Here’s our Assessment, it’s nothing too complicated. There’s one thing worth mentioning. StudentCourses collection. We need to add it to every entity that going to be in N to M relationship.

Junction table Primary Key

We’ve almost finished. Our new table needs PK, maybe you remember, that EF will create PK from property with name Id or ClassNameId, but we don’t want that. We need more control. That’s why we will use fluent API.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<StudentCourse>()
        .HasKey(x => new { x.CourseId, x.StudentId, x.AssessmentId });
}

Database Seed

Assessment is dictionary table that means, we know what we want to add there. EF lets you seed database with data. It is pretty useful feature, because you can create new database and have data in there immediately. It’s useful if you have multiple environments. Your database setup will be finished in one step. Seeding database is pretty easy, we need to use Fluent API to do that.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<StudentCourse>()
        .HasKey(x => new { x.CourseId, x.StudentId, x.AssessmentId });

    modelBuilder.Entity<Assessment>()
        .HasData(
        new Assessment { Id = 1, WeightType = "Homework", Weight = 0.2f },
        new Assessment { Id = 2, WeightType = "Quiz test", Weight = 0.3f },
        new Assessment { Id = 3, WeightType = "Work at school", Weight = 0.6f },
        new Assessment { Id = 4, WeightType = "Exam", Weight = 1.0f });
}

This is how easy is Seeding Data.

Outro

Now our project is ready to be extended. As you can see EF lets you do every usual operation, additionally EF makes it super easy. I like it, because EF provides complete solution, where using only SQL needs something else for database versioning. You can find whole project here https://github.com/MartinBialkowski/BlogAdvanced or here https://embeprojects.visualstudio.com/PublicShowProject/_git/BlogPostAdvanced. Feel free to ask questions if you have any.