C# Linq Parent Children FK

Question

So, I have the following code files, obtained from: https://github.com/entityframeworktutorial/EF6-Code-First-Demo/tree/master/EF6CodeFirstDemo

I am just a bit confused here, a little newbie. Anyway, I wondering, how would one insert this correctly?

The problem is that I do not get ID of Grade in Student, only NULL. But, "Grade_ID" appears in Student because class Grade has an ICollection<Student> Students { get; set; } line.

Add Student and Grades:

public static void SaveStudent(
    string name, DateTime? birthday, decimal height, float weight,
    List<Grade> grades)
{
    var student = new Student {
               StudentName = name, DateOfBirth = birthday, Height = height, Weight = weight };
    

    using (var db = new SchoolContext()) {
        db.Students.Add(student);
        db.Grades.AddRange(grades);
        db.SaveChanges();
    }
}

---

 public static int AddStudent(string name, DateTime? birthday, decimal height, float weight) 
        {
            var student = new Student {
               StudentName = name, DateOfBirth = birthday, Height = height, Weight = weight };
            using(var db = new SchoolContext()) 
            {
                if (db.Students.SingleOrDefault(s => s.StudentName == name) == null) {
                    db.Students.Add(student);
                    db.SaveChanges();
                    return student.StudentID;
                }
            }

            return -1;
        }

---

Course.cs

using System.Collections.Generic;

namespace EF6CodeFirstDemo
{
    public class Course
    {
        public Course()
        {
            this.Students = new HashSet<Student>();
        }

        public int CourseId { get; set; }
        public string CourseName { get; set; }

        public virtual ICollection<Student> Students { get; set; }
    }
}

Student.cs

using System;
using System.Collections.Generic;

namespace EF6CodeFirstDemo
{
    public class Student
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int StudentID { get; set; }
        public string StudentName { get; set; }
        public DateTime? DateOfBirth { get; set; }
        public decimal Height { get; set; }
        public float Weight { get; set; }
        public byte[] RowVersion { get; set; }

        //fully defined relationship
        public int? GradeId { get; set; }

        public virtual Grade Grade { get; set; }

        public virtual StudentAddress Address { get; set; }
        public virtual ICollection<Course> Courses { get; set; }
    }
}

Grade.cs

using System.Collections.Generic;

namespace EF6CodeFirstDemo
{
    public class Grade
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int GradeId { get; set; }
        public string GradeName { get; set; }
        public string Section { get; set; }

        public virtual ICollection<Student> Students { get; set; }
    }
}

Answer 1

The first thing is to think in terms of CRUD operations. For a Students we will "Create" and "Update". When it comes to assigning a Grade to a student, will that happen when we Create the student, or as an Update to the student?

The next important detail with an ORM (EF) is that it is responsible for mapping the relationships between entities. A Grade looks to be representing a year level for a school. A Student would be designated as being in a particular grade. We don't need to go to the DbContext for every entity, and not every relationship needs to be resolvable in both directions. Grades will probably be something that are maintained separately as an administrative feature, so the act of linking grades to students would be more of an association rather than a parent-child relationship.

The last important detail applies to web applications in particular, but is still relevant for Windows applications as well. Entities are only really entities when they are tracked by a DbContext. Outside of the scope of their DbContext they are simple data containers. It is generally a good idea to make this distinction by using dedicated ViewModels rather than passing around entities outside of the scope of their DbContext. This point is possibly a contentious one because so many examples out there pass around entities, but believe me, it gets frustrating dealing with the issues that come up when this isn't done precisely according to the rules. (and it easily gets complex)

Creating a Student:

public static int AddStudent(string name, DateTime? birthday, decimal height, float weight) 
{
    var student = new Student 
    {
        StudentName = name, 
        DateOfBirth = birthday, 
        Height = height, 
        Weight = weight 
    };
    using(var db = new SchoolContext()) 
    {
        //if (db.Students.SingleOrDefault(s => s.StudentName == name) == null) 
        if (db.Students.Any(s => s.StudentName == name)) 
            return -1;

        db.Students.Add(student);
        db.SaveChanges();
        return student.StudentID;
    }

    return -1;
}

That function is pretty much what you are looking for. The one change would be to do an Any rather than checking for a #null after SingleOrDefault. The first will generate an EXISTS query rather than potentially returning an entire record that isn't used. I also changed the condition to adopt a "fail fast" assertion to avoid nesting in the conditional statements.

Now if in creating a student we know what grade they will be in, then we can add that to the CreateStudent method:

public static int AddStudent(string name, DateTime? birthday, decimal height, float weight, int gradeId) 
{
    using(var db = new SchoolContext()) 
    {
        if (db.Students.Any(s => s.StudentName == name)) 
            return -1;

        var grade = db.Grades.Single(x => x.GradeId == gradeId);
        var student = new Student 
        {
            StudentName = name, 
            DateOfBirth = birthday, 
            Height = height, 
            Weight = weight,
            Grade = grade 
        };

        db.Students.Add(student);
        db.SaveChanges();
        return student.StudentID;
    }
}

The first question might be "why pass in a Grade ID and not just pass in a Grade?" The reason is that we are scoping our DbContext inside this method (using (var db = ...)) so it will not know about any Grade entity that gets passed in. You can address this by using Attach to associate an existing grade and that should work perfectly fine in this example, however, later you will likely come across new patterns like Dependency Injection / IoC to manage the lifetime scope of dependencies like the DbContext. This can lead to new challenges if you're accustomed to passing Entities around and expecting to attach them because a DbContext may already be tracking another instance of that entity with the same ID leading to situational exceptions. As a general rule it's better to not rely on entities outside of the scope of the DbContext they were read. It may seem inefficient to re-read a Grade entity, however EF can read entities by ID extremely fast, and if a DbContext happens to be already tracking that entity, it will return it without touching the DB.

My advice when it comes to relationships between entities is to keep them single-direction wherever possible, bi-directional only when needed. So for instance if we have:

public class Student
{
   // ...
   public virtual Grade Grade { get; set; }
}

we don't likely need:

public class Grade
{
    // ...
    public virtual ICollection<Student> Students { get; set; }
}

If you want to get all students in a grade:

var students = db.Students.Where(x => x.Grade.GradeId == gradeId).ToList();

rather than:

var students = db.Grades.Where(x => x.GradeId == gradeId).SelectMany(x => x.Students).ToList();

It's actually simpler Linq in most cases.

An additional tip when you are dealing with collection sets within entities is to initialize those collections so they are immediately ready for use. For example:

public class Student
{
   // ...
   public virtual ICollection<Course> Courses { get; set; } = new List<Course>();
}

This way it's ready to go immediately if we create a new student. If there are course references to add, we can load those entities and add them to the Student's Courses collection without worrying that it might not have been initialized. As a general rule when working with entities and collections you want to avoid ever setting the references to a new collection. I.e. statements like this can get you into trouble when saving:

student.Courses = db.Courses.Where(x => x.Grade.GradeId == gradeId).ToList();

Especially if you had a student in one grade switching to the next grade. With EF collections you should always explicitly add and remove related entities, don't replace the collection reference. I'll default to making all setters internal in entities within their own project to help guard against illegal/invalid modification.