演练5-3：Contoso大学校园管理系统3
在前面的教程中，我们使用了一个简单的数据模型，包括三个数据实体。在这个教程汇中，我们将添加更多的实体和关系，按照特定的格式和验证规则等自定义数据模型。
Contoso大学校园管理系统的数据模型如下。
一、修改学生实体
1.DataType Attribute
using System; using System.Collections.Generic; using System.ComponentModel.DataAnnotations; namespace ContosoUniversity.Models { public class Student { public int StudentID { get; set; } public string LastName { get; set; } public string FirstMidName { get; set; } [DataType(DataType.Date)] [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)] public DateTime EnrollmentDate { get; set; } public virtual ICollection<Enrollment> Enrollments { get; set; } } }
DataType.Date定义了日期显示不包括时间。DataType的枚举定义了很多类型，比如Date、Time、PhoneNumber、Currency、EmailAddress等。它并不包含验证规则。
DisplayFormat属性用来显式指定日期格式，ApplyFormatInEditMode设置指定将当前格式应用到，值显示在可编辑的文本框中。
2.StringLengthAttribute
using System; using System.Collections.Generic; using System.ComponentModel.DataAnnotations; namespace ContosoUniversity.Models { public class Student { public int StudentID { get; set; } [StringLength(50)] public string LastName { get; set; } [StringLength(50, ErrorMessage = "First name cannot be longer than 50 characters.")] public string FirstMidName { get; set; } [DataType(DataType.Date)] [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)] public DateTime EnrollmentDate { get; set; } public virtual ICollection<Enrollment> Enrollments { get; set; } } }
StringLength规定字符串长度必须在某个值内，但是不会阻止用户提交空的字段值。复杂的验证规则可以使用正则表达式验证RegularExpression属性。
3.列属性
using System; using System.Collections.Generic; using System.ComponentModel.DataAnnotations; using System.ComponentModel.DataAnnotations.Schema; namespace ContosoUniversity.Models { public class Student { public int StudentID { get; set; } [StringLength(50)] public string LastName { get; set; } [StringLength(50, ErrorMessage = "First name cannot be longer than 50 characters.")] [Column("FirstName")] public string FirstMidName { get; set; } [DataType(DataType.Date)] [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)] public DateTime EnrollmentDate { get; set; } public virtual ICollection<Enrollment> Enrollments { get; set; } } }
FirstMidName属性对应到数据库中的列字段名为FirstName。
二、创建老师Instructor实体
using System; using System.Collections.Generic; using System.ComponentModel.DataAnnotations; using System.ComponentModel.DataAnnotations.Schema; namespace ContosoUniversity.Models { public class Instructor { public int InstructorID { get; set; } [Required] [Display(Name = "Last Name")] [StringLength(50)] public string LastName { get; set; } [Required] [Column("FirstName")] [Display(Name = "First Name")] [StringLength(50)] public string FirstMidName { get; set; } [DataType(DataType.Date)] [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)] [Display(Name = "Hire Date")] public DateTime HireDate { get; set; } public string FullName { get { return LastName + ", " + FirstMidName; } } public virtual ICollection<Course> Courses { get; set; } public virtual OfficeAssignment OfficeAssignment { get; set; } } }
你可以在一行上放入多个属性。
public class Instructor { public int InstructorID { get; set; } [Display(Name = "Last Name"),StringLength(50, MinimumLength=1)] public string LastName { get; set; } [Column("FirstName"),Display(Name = "First Name"),StringLength(50, MinimumLength=1)] public string FirstMidName { get; set; } [DataType(DataType.Date),Display(Name = "Hire Date"),DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)] public DateTime HireDate { get; set; } public string FullName { get { return LastName + ", " + FirstMidName; } } public virtual ICollection<Course> Courses { get; set; } public virtual OfficeAssignment OfficeAssignment { get; set; } }
FullName是一个计算属性，它返回连接两个属性的值，只有get，在数据库中并不创建列字段。
三、创建办公地点OfficeAssignment实体
using System.ComponentModel.DataAnnotations; using System.ComponentModel.DataAnnotations.Schema; namespace ContosoUniversity.Models { public class OfficeAssignment { [Key] [ForeignKey("Instructor")] public int InstructorID { get; set; } [StringLength(50)] [Display(Name = "Office Location")] public string Location { get; set; } public virtual Instructor Instructor { get; set; } } }
InstructorID是主键也是教师实体的外键，因为InstructorID不能识别为主键，主键必须是类名ID或者ID，所以需要加[Key]。
四、修改课程Course实体
using System.Collections.Generic; using System.ComponentModel.DataAnnotations; using System.ComponentModel.DataAnnotations.Schema; namespace ContosoUniversity.Models { public class Course { [DatabaseGenerated(DatabaseGeneratedOption.None)] [Display(Name = "Number")] public int CourseID { get; set; } [StringLength(50, MinimumLength = 3)] public string Title { get; set; } [Range(0, 5)] public int Credits { get; set; } [Display(Name = "Department")] public int DepartmentID { get; set; } public virtual Department Department { get; set; } public virtual ICollection<Enrollment> Enrollments { get; set; } public virtual ICollection<Instructor> Instructors { get; set; } } }
五、创建系部Department实体
using System; using System.Collections.Generic; using System.ComponentModel.DataAnnotations; using System.ComponentModel.DataAnnotations.Schema; namespace ContosoUniversity.Models { public class Department { public int DepartmentID { get; set; } [StringLength(50, MinimumLength=3)] public string Name { get; set; } [DataType(DataType.Currency)] [Column(TypeName = "money")] public decimal Budget { get; set; } [DataType(DataType.Date)] public DateTime StartDate { get; set; } [Display(Name = "Administrator")] public int? InstructorID { get; set; } public virtual Instructor Administrator { get; set; } public virtual ICollection<Course> Courses { get; set; } } }
Column属性改变了列字段的类型。
六、修改学生Student实体
using System; using System.Collections.Generic; using System.ComponentModel.DataAnnotations; using System.ComponentModel.DataAnnotations.Schema; namespace ContosoUniversity.Models { public class Student { public int StudentID { get; set; } [StringLength(50, MinimumLength = 1)] public string LastName { get; set; } [StringLength(50, MinimumLength = 1, ErrorMessage = "First name cannot be longer than 50 characters.")] [Column("FirstName")] public string FirstMidName { get; set; } [DataType(DataType.Date)] [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)] [Display(Name = "Enrollment Date")] public DateTime EnrollmentDate { get; set; } public string FullName { get { return LastName + ", " + FirstMidName; } } public virtual ICollection<Enrollment> Enrollments { get; set; } } }
七、修改注册Enrollment实体
using System.ComponentModel.DataAnnotations; namespace ContosoUniversity.Models { public enum Grade { A, B, C, D, F } public class Enrollment { public int EnrollmentID { get; set; } public int CourseID { get; set; } public int StudentID { get; set; } [DisplayFormat(NullDisplayText = "No grade")] public Grade? Grade { get; set; } public virtual Course Course { get; set; } public virtual Student Student { get; set; } } }
八、使用Seed方法为数据库注入测试数据
namespace ContosoUniversity.Migrations { using System; using System.Collections.Generic; using System.Data.Entity; using System.Data.Entity.Migrations; using System.Linq; using ContosoUniversity.Models; using ContosoUniversity.DAL; internal sealed class Configuration : DbMigrationsConfiguration<SchoolContext> { public Configuration() { AutomaticMigrationsEnabled = false; } protected override void Seed(SchoolContext context) { var students = new List<Student> { new Student { FirstMidName = "Carson", LastName = "Alexander", EnrollmentDate = DateTime.Parse("2010-09-01") }, new Student { FirstMidName = "Meredith", LastName = "Alonso", EnrollmentDate = DateTime.Parse("2012-09-01") }, new Student { FirstMidName = "Arturo", LastName = "Anand", EnrollmentDate = DateTime.Parse("2013-09-01") }, new Student { FirstMidName = "Gytis", LastName = "Barzdukas", EnrollmentDate = DateTime.Parse("2012-09-01") }, new Student { FirstMidName = "Yan", LastName = "Li", EnrollmentDate = DateTime.Parse("2012-09-01") }, new Student { FirstMidName = "Peggy", LastName = "Justice", EnrollmentDate = DateTime.Parse("2011-09-01") }, new Student { FirstMidName = "Laura", LastName = "Norman", EnrollmentDate = DateTime.Parse("2013-09-01") }, new Student { FirstMidName = "Nino", LastName = "Olivetto", EnrollmentDate = DateTime.Parse("2005-09-01") } }; students.ForEach(s => context.Students.AddOrUpdate(p => p.LastName, s)); context.SaveChanges(); var instructors = new List<Instructor> { new Instructor { FirstMidName = "Kim", LastName = "Abercrombie", HireDate = DateTime.Parse("1995-03-11") }, new Instructor { FirstMidName = "Fadi", LastName = "Fakhouri", HireDate = DateTime.Parse("2002-07-06") }, new Instructor { FirstMidName = "Roger", LastName = "Harui", HireDate = DateTime.Parse("1998-07-01") }, new Instructor { FirstMidName = "Candace", LastName = "Kapoor", HireDate = DateTime.Parse("2001-01-15") }, new Instructor { FirstMidName = "Roger", LastName = "Zheng", HireDate = DateTime.Parse("2004-02-12") } }; instructors.ForEach(s => context.Instructors.AddOrUpdate(p => p.LastName, s)); context.SaveChanges(); var departments = new List<Department> { new Department { Name = "English", Budget = 350000, StartDate = DateTime.Parse("2007-09-01"), InstructorID = instructors.Single( i => i.LastName == "Abercrombie").InstructorID }, new Department { Name = "Mathematics", Budget = 100000, StartDate = DateTime.Parse("2007-09-01"), InstructorID = instructors.Single( i => i.LastName == "Fakhouri").InstructorID }, new Department { Name = "Engineering", Budget = 350000, StartDate = DateTime.Parse("2007-09-01"), InstructorID = instructors.Single( i => i.LastName == "Harui").InstructorID }, new Department { Name = "Economics", Budget = 100000, StartDate = DateTime.Parse("2007-09-01"), InstructorID = instructors.Single( i => i.LastName == "Kapoor").InstructorID } }; departments.ForEach(s => context.Departments.AddOrUpdate(p => p.Name, s)); context.SaveChanges(); var courses = new List<Course> { new Course {CourseID = 1050, Title = "Chemistry", Credits = 3, DepartmentID = departments.Single( s => s.Name == "Engineering").DepartmentID, Instructors = new List<Instructor>() }, new Course {CourseID = 4022, Title = "Microeconomics", Credits = 3, DepartmentID = departments.Single( s => s.Name == "Economics").DepartmentID, Instructors = new List<Instructor>() }, new Course {CourseID = 4041, Title = "Macroeconomics", Credits = 3, DepartmentID = departments.Single( s => s.Name == "Economics").DepartmentID, Instructors = new List<Instructor>() }, new Course {CourseID = 1045, Title = "Calculus", Credits = 4, DepartmentID = departments.Single( s => s.Name == "Mathematics").DepartmentID, Instructors = new List<Instructor>() }, new Course {CourseID = 3141, Title = "Trigonometry", Credits = 4, DepartmentID = departments.Single( s => s.Name == "Mathematics").DepartmentID, Instructors = new List<Instructor>() }, new Course {CourseID = 2021, Title = "Composition", Credits = 3, DepartmentID = departments.Single( s => s.Name == "English").DepartmentID, Instructors = new List<Instructor>() }, new Course {CourseID = 2042, Title = "Literature", Credits = 4, DepartmentID = departments.Single( s => s.Name == "English").DepartmentID, Instructors = new List<Instructor>() }, }; courses.ForEach(s => context.Courses.AddOrUpdate(p => p.CourseID, s)); context.SaveChanges(); var officeAssignments = new List<OfficeAssignment> { new OfficeAssignment { InstructorID = instructors.Single( i => i.LastName == "Fakhouri").InstructorID, Location = "Smith 17" }, new OfficeAssignment { InstructorID = instructors.Single( i => i.LastName == "Harui").InstructorID, Location = "Gowan 27" }, new OfficeAssignment { InstructorID = instructors.Single( i => i.LastName == "Kapoor").InstructorID, Location = "Thompson 304" }, }; officeAssignments.ForEach(s => context.OfficeAssignments.AddOrUpdate(p => p.Location, s)); context.SaveChanges(); AddOrUpdateInstructor(context, "Chemistry", "Kapoor"); AddOrUpdateInstructor(context, "Chemistry", "Harui"); AddOrUpdateInstructor(context, "Microeconomics", "Zheng"); AddOrUpdateInstructor(context, "Macroeconomics", "Zheng"); AddOrUpdateInstructor(context, "Calculus", "Fakhouri"); AddOrUpdateInstructor(context, "Trigonometry", "Harui"); AddOrUpdateInstructor(context, "Composition", "Abercrombie"); AddOrUpdateInstructor(context, "Literature", "Abercrombie"); context.SaveChanges(); var enrollments = new List<Enrollment> { new Enrollment { StudentID = students.Single(s => s.LastName == "Alexander").StudentID, CourseID = courses.Single(c => c.Title == "Chemistry" ).CourseID, Grade = Grade.A }, new Enrollment { StudentID = students.Single(s => s.LastName == "Alexander").StudentID, CourseID = courses.Single(c => c.Title == "Microeconomics" ).CourseID, Grade = Grade.C }, new Enrollment { StudentID = students.Single(s => s.LastName == "Alexander").StudentID, CourseID = courses.Single(c => c.Title == "Macroeconomics" ).CourseID, Grade = Grade.B }, new Enrollment { StudentID = students.Single(s => s.LastName == "Alonso").StudentID, CourseID = courses.Single(c => c.Title == "Calculus" ).CourseID, Grade = Grade.B }, new Enrollment { StudentID = students.Single(s => s.LastName == "Alonso").StudentID, CourseID = courses.Single(c => c.Title == "Trigonometry" ).CourseID, Grade = Grade.B }, new Enrollment { StudentID = students.Single(s => s.LastName == "Alonso").StudentID, CourseID = courses.Single(c => c.Title == "Composition" ).CourseID, Grade = Grade.B }, new Enrollment { StudentID = students.Single(s => s.LastName == "Anand").StudentID, CourseID = courses.Single(c => c.Title == "Chemistry" ).CourseID }, new Enrollment { StudentID = students.Single(s => s.LastName == "Anand").StudentID, CourseID = courses.Single(c => c.Title == "Microeconomics").CourseID, Grade = Grade.B }, new Enrollment { StudentID = students.Single(s => s.LastName == "Barzdukas").StudentID, CourseID = courses.Single(c => c.Title == "Chemistry").CourseID, Grade = Grade.B }, new Enrollment { StudentID = students.Single(s => s.LastName == "Li").StudentID, CourseID = courses.Single(c => c.Title == "Composition").CourseID, Grade = Grade.B }, new Enrollment { StudentID = students.Single(s => s.LastName == "Justice").StudentID, CourseID = courses.Single(c => c.Title == "Literature").CourseID, Grade = Grade.B } }; foreach (Enrollment e in enrollments) { var enrollmentInDataBase = context.Enrollments.Where( s => s.Student.StudentID == e.StudentID && s.Course.CourseID == e.CourseID).SingleOrDefault(); if (enrollmentInDataBase == null) { context.Enrollments.Add(e); } } context.SaveChanges(); } void AddOrUpdateInstructor(SchoolContext context, string courseTitle, string instructorName) { var crs = context.Courses.SingleOrDefault(c => c.Title == courseTitle); var inst = crs.Instructors.SingleOrDefault(i => i.LastName == instructorName); if (inst == null) crs.Instructors.Add(context.Instructors.Single(i => i.LastName == instructorName)); } } }
九、模型迁移
在PMC, 输入add-migration命令:
PM> add-Migration Chap4
此时更新数据库，可能会得到如下错误：
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_dbo.Course_dbo.Department_DepartmentID". The conflict occurred in database "ContosoUniversity", table "dbo.Department", column 'DepartmentID'.
编辑<timestamp>_Chap4.cs模型迁移文件, 修改代码如下：
CreateTable( "dbo.CourseInstructor", c => new { CourseID = c.Int(nullable: false), InstructorID = c.Int(nullable: false), }) .PrimaryKey(t => new { t.CourseID, t.InstructorID }) .ForeignKey("dbo.Course", t => t.CourseID, cascadeDelete: true) .ForeignKey("dbo.Instructor", t => t.InstructorID, cascadeDelete: true) .Index(t => t.CourseID) .Index(t => t.InstructorID); // Create a department for course to point to. Sql("INSERT INTO dbo.Department (Name, Budget, StartDate) VALUES ('Temp', 0.00, GETDATE())"); // default value for FK points to department created above. AddColumn("dbo.Course", "DepartmentID", c => c.Int(nullable: false, defaultValue: 1)); //AddColumn("dbo.Course", "DepartmentID", c => c.Int(nullable: false)); AlterColumn("dbo.Course", "Title", c => c.String(maxLength: 50)); AddForeignKey("dbo.Course", "DepartmentID", "dbo.Department", "DepartmentID", cascadeDelete: true); CreateIndex("dbo.Course", "DepartmentID"); } public override void Down() {
(确保你已经删除了多余的AddColumn，如果迁移不成功，可将数据库删除后再试。)
