SQL Server Relationship With Entity Framework - Contoso University

2023-02-20

筆記 SQL Server Relationship 以及 Entity Framework 物件的對應關係，以 Contoso University 為討論案例。

說明

Course

public int CourseID { get; set; }
public string Title { get; set; }
public int Credits { get; set; }
public int DepartmentID { get; set; }
public virtual Department Department { get; set; }

public virtual ICollection<Enrollment> Enrollment { get; set; }
public virtual ICollection<Instructor> Instructor { get; set; }

One To Many With Enrollment
C: virtual ICollection of Enrollment
E: virtual Course

Many To One With Department
C: virtual Department
D: virtual ICollection of Course

Many To Many With Instructor
C: virtual ICollection of Instructor
I: virtual ICollection of Course

Instructor

public int ID { get; set; }
public string LastName { get; set; }
public string FirstName { get; set; }
public System.DateTime HireDate { get; set; }

public virtual ICollection<Department> Department { get; set; }
public virtual OfficeAssignment OfficeAssignment { get; set; }
public virtual ICollection<Course> Course { get; set; }

Zero Or One To Many With Department
I: virtual ICollection of Department
D: virtual Instructor

One To Zero Or One With OfficeAssignment
I: virtual OfficeAssignment (InstructorId)
O: virtual Instructor (InstructorId)

Student

public int ID { get; set; }
public string LastName { get; set; }
public string FirstName { get; set; }
public System.DateTime EnrollmentDate { get; set; }

public virtual ICollection<Enrollment> Enrollment { get; set; }

One To Zero Or One With Enrollment
S: virtual ICollection of Enrollment
E: virtual Student

Enrollment

public int EnrollmentID { get; set; }
public int CourseID { get; set; }
public int StudentID { get; set; }
public Nullable<int> Grade { get; set; }

public virtual Course Course { get; set; }
public virtual Student Student { get; set; }

Department

public int DepartmentID { get; set; }
public string Name { get; set; }
public decimal Budget { get; set; }
public System.DateTime StartDate { get; set; }
public Nullable<int> InstructorID { get; set; }

public virtual ICollection<Course> Course { get; set; }
public virtual Instructor Instructor { get; set; }

OfficeAssignment

public int InstructorID { get; set; }
public string Location { get; set; }

public virtual Instructor Instructor { get; set; }

Code First From Exsiting Database

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Course>()
        .HasMany(e => e.Instructor)
        .WithMany(e => e.Course)
        .Map(
          m => m.ToTable("CourseInstructor")
          .MapLeftKey("CourseID").MapRightKey("InstructorID")
        );

    modelBuilder.Entity<Department>()
        .Property(e => e.Budget)
        .HasPrecision(19, 4);

    modelBuilder.Entity<Instructor>()
        .HasOptional(e => e.OfficeAssignment)
        .WithRequired(e => e.Instructor);
}