该例子是我临时想出来的,不具有任何的实际意义。类图如图1所示。
图1
类代码:
[Table("student")] public class Student { public int id { get; set; } public string name { get; set; } public int grade_id { get; set; } public int address_id { get; set; } [NotMapped] public Address address { get; set; } [NotMapped] public List<Subject> subjects { get; set; } [NotMapped] public List<Teacher> teachers { get; set; } }
[Table("teacher")] public class Teacher { public int id { get; set; } public string name { get; set; } public int subject_id { get; set; } public int depart_id { get; set; } public int address_id { get; set; } [NotMapped] public Address address { get; set; } [NotMapped] public List<Student> students { get; set; } }
[Table("grade")] public class Grade { public int id { get; set; } public string name { get; set; } public int depart_id { get; set; } [NotMapped] public List<Student> students { get; set; } }
[Table("subject")] public class Subject { public int id { get; set; } public string name { get; set; } public int depart_id { get; set; } [NotMapped] public List<Teacher> teachers { get; set; } [NotMapped] public List<Student> students { get; set; } }
[Table("depart")] public class Department { public int id { get; set; } public string name { get; set; } [NotMapped] public List<Address> addresses { get; set; } [NotMapped] public List<Grade> grades { get; set; } [NotMapped] public List<Subject> subjects { get; set; } [NotMapped] public List<Teacher> teachers { get; set; } }
[Table("address")] public class Address { public int id { get; set; } public string country { get; set; } public string city { get; set; } [NotMapped] public List<Department> departs { get; set; } }
类之间的关系如表1描述。
表1
Department与Grade, Subject, Teacher之间都是一对多的关系。以Teacher为例,Department与Teacher之间的对应关系是通过depart_id外键实现的,那么在构建Department模型时,该关系用代码描述为:
builder.Entity<Department>() .HasMany(d => d.teachers) .WithOne(t => t.depart).HasForeignKey(t => t.depart_id);
其中t=>t.depart可以省略,即
builder.Entity<Department>() .HasMany(d => d.teachers) .WithOne().HasForeignKey(t => t.depart_id);
Department与Address之间是多对多的关系,这种关系映射在数据库时通常需要有一张关系表,用于记录Department和Address之间的关系。同样地,要给这张关系表也建立一个相应的类:
[Table("depart_address")] public class Depart_Address { public int id { get; set; } public int depart_id { get; set; } public int address_id { get; set; } [NotMapped] public Department depart { get; set; } [NotMapped] public Address address { get; set; } }
多对多的关系模型描述如下:
builder.Entity<Department>() .HasMany(d => d.addresses) .WithMany(a => a.departs) .UsingEntity<Depart_Address>( j => j.HasOne(pt => pt.address) .WithMany().HasForeignKey(pt => pt.address_id), j => j.HasOne(pt => pt.depart) .WithMany().HasForeignKey(pt => pt.depart_id), j => j.HasKey(t => new { t.depart_id, t.address_id }));
整段模型代码为:
protected override void OnModelCreating(ModelBuilder builder) { #region Student Relationships // students-teachers : multi-to-multi builder.Entity<Student>() .HasMany(s => s.teachers) .WithMany(t => t.students) .UsingEntity<StudentTeacher>( j => j.HasOne(pt => pt.teacher) .WithMany() .HasForeignKey(pt => pt.teacher_id), j => j.HasOne(pt => pt.student) .WithMany() .HasForeignKey(pt => pt.student_id), j => j.HasKey(t => new { t.student_id, t.teacher_id })); // students-subjects: multi-to-multi builder.Entity<Student>() .HasMany(s => s.subjects) .WithMany(su => su.students) .UsingEntity<StudentSubject>( j => j.HasOne(pt => pt.subject) .WithMany() .HasForeignKey(pt => pt.subject_id), j => j.HasOne(pt => pt.student) .WithMany() .HasForeignKey(pt => pt.student_id), j => j.HasKey(t => new { t.student_id, t.subject_id })); // student-address: multi-to-one builder.Entity<Student>() .HasOne(s => s.address) .WithMany() .HasForeignKey(s => s.address_id); #endregion #region Teacher Relationships // teachers-address: multi-to-one builder.Entity<Teacher>() .HasOne(t => t.address) .WithMany() .HasForeignKey(t => t.address_id); builder.Entity<Teacher>() .HasMany(t => t.students) .WithMany(s => s.teachers) .UsingEntity<StudentTeacher>( j => j.HasOne(pt => pt.student) .WithMany().HasForeignKey(pt => pt.student_id), j => j.HasOne(pt => pt.teacher) .WithMany().HasForeignKey(pt => pt.teacher_id), j => j.HasKey(t => new { t.teacher_id, t.student_id })); #endregion #region Department Relationships // department-grades: one-to-multi builder.Entity<Department>() .HasMany(d => d.grades) .WithOne().HasForeignKey(g => g.depart_id); // department-teachers: one-to-multi builder.Entity<Department>() .HasMany(d => d.teachers) .WithOne().HasForeignKey(t => t.depart_id); // department-subjects: one-to-multi builder.Entity<Department>() .HasMany(d => d.subjects) .WithOne().HasForeignKey(s => s.depart_id); // departments-addresses: multi-to-multi builder.Entity<Department>() .HasMany(d => d.addresses) .WithMany(a => a.departs) .UsingEntity<Depart_Address>( j => j.HasOne(pt => pt.address) .WithMany().HasForeignKey(pt => pt.address_id), j => j.HasOne(pt => pt.depart) .WithMany().HasForeignKey(pt => pt.depart_id), j => j.HasKey(t => new { t.depart_id, t.address_id })); #endregion #region Grade Relationships // grade-students: one-to-multi builder.Entity<Grade>() .HasMany(g => g.students) .WithOne().HasForeignKey(s => s.grade_id); #endregion #region Subject Relationships // subjects-students: multi-to-multi builder.Entity<Subject>() .HasMany(s => s.students) .WithMany(stu => stu.subjects) .UsingEntity<StudentSubject>( j => j.HasOne(pt => pt.student) .WithMany() .HasForeignKey(pt => pt.student_id), j => j.HasOne(pt => pt.subject) .WithMany() .HasForeignKey(pt => pt.subject_id), j => j.HasKey(t => new { t.subject_id, t.student_id })); builder.Entity<Subject>() .HasMany(s => s.teachers) .WithOne().HasForeignKey(t => t.subject_id); #endregion }
测试:
1.构建Department对象
Department depart = context.departs .Include(d => d.subjects) .ThenInclude(s=>s.students) .ThenInclude(s=>s.teachers) .Include(d=>d.addresses) .Include(d => d.teachers) .ThenInclude(t=>t.students) .Include(d => d.grades) .ThenInclude(g => g.students).First();
Console.WriteLine("department name: " + depart.name); Console.WriteLine(); Console.WriteLine("department subjects:"); foreach(var item in depart.subjects) { Console.WriteLine((depart.subjects.IndexOf(item)+1).ToString()+"." + item.name); Console.WriteLine("Students choose this subject:"); foreach (var stu in item.students) Console.Write(stu.name+","); Console.WriteLine(); Console.WriteLine("Teachers who teach this subject:"); foreach (var t in item.teachers) Console.Write(t.name+","); Console.WriteLine(); } Console.WriteLine(); Console.WriteLine("department teachers:"); foreach(var item in depart.teachers) { Console.WriteLine((depart.teachers.IndexOf(item)+1).ToString()+"."+item.name); Console.WriteLine("his or her students:"); foreach (var stu in item.students) Console.Write(stu.name + ","); Console.WriteLine(); } Console.WriteLine(); Console.WriteLine("department grades:"); foreach(var item in depart.grades) { Console.WriteLine((depart.grades.IndexOf(item) + 1).ToString() + "." + item.name); Console.WriteLine("students in this grade:"); foreach (var stu in item.students) Console.Write(stu.name + ","); Console.WriteLine(); } Console.WriteLine(); Console.WriteLine("department addresses:"); foreach(var item in depart.addresses) { Console.WriteLine(item.country + "." + item.city); }
测试结果:
2.构建Students对象
List<Student> students = context.students .Include(s => s.subjects) .Include(s => s.teachers) .Include(s => s.address).ToList();
Console.WriteLine("Students List:"); foreach(var item in students) { Console.WriteLine((students.IndexOf(item) + 1).ToString() + "." + item.name); Console.WriteLine("his or her subjects:"); foreach(var sub in item.subjects) { Console.Write(sub.name + ","); } Console.WriteLine(); Console.WriteLine("his or her teachers:"); foreach (var t in item.teachers) Console.Write(t.name + ","); Console.WriteLine(); Console.WriteLine("his or her address:" + item.address.country + "." + item.address.city); }
完整代码路径:
https://github.com/Larissa1990/EFcore_demo