在前面的教程中,我们使用了一个简单的数据模型,包括三个数据实体。在这个教程汇中,我们将添加更多的实体和关系,按照特定的格式和验证规则等自定义数据模型。
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()]
public string LastName { get; set; }
[StringLength(, 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()]
public string LastName { get; set; }
[StringLength(, 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()]
public string LastName { get; set; } [Required]
[Column("FirstName")]
[Display(Name = "First Name")]
[StringLength()]
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(, MinimumLength=)]
public string LastName { get; set; } [Column("FirstName"),Display(Name = "First Name"),StringLength(, MinimumLength=)]
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()]
[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(, MinimumLength = )]
public string Title { get; set; } [Range(, )]
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(, MinimumLength=)]
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(, MinimumLength = )]
public string LastName { get; set; } [StringLength(, MinimumLength = , 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 = ,
StartDate = DateTime.Parse("2007-09-01"),
InstructorID = instructors.Single( i => i.LastName == "Abercrombie").InstructorID },
new Department { Name = "Mathematics", Budget = ,
StartDate = DateTime.Parse("2007-09-01"),
InstructorID = instructors.Single( i => i.LastName == "Fakhouri").InstructorID },
new Department { Name = "Engineering", Budget = ,
StartDate = DateTime.Parse("2007-09-01"),
InstructorID = instructors.Single( i => i.LastName == "Harui").InstructorID },
new Department { Name = "Economics", Budget = ,
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 = , Title = "Chemistry", Credits = ,
DepartmentID = departments.Single( s => s.Name == "Engineering").DepartmentID,
Instructors = new List<Instructor>()
},
new Course {CourseID = , Title = "Microeconomics", Credits = ,
DepartmentID = departments.Single( s => s.Name == "Economics").DepartmentID,
Instructors = new List<Instructor>()
},
new Course {CourseID = , Title = "Macroeconomics", Credits = ,
DepartmentID = departments.Single( s => s.Name == "Economics").DepartmentID,
Instructors = new List<Instructor>()
},
new Course {CourseID = , Title = "Calculus", Credits = ,
DepartmentID = departments.Single( s => s.Name == "Mathematics").DepartmentID,
Instructors = new List<Instructor>()
},
new Course {CourseID = , Title = "Trigonometry", Credits = ,
DepartmentID = departments.Single( s => s.Name == "Mathematics").DepartmentID,
Instructors = new List<Instructor>()
},
new Course {CourseID = , Title = "Composition", Credits = ,
DepartmentID = departments.Single( s => s.Name == "English").DepartmentID,
Instructors = new List<Instructor>()
},
new Course {CourseID = , Title = "Literature", Credits = ,
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: ));
//AddColumn("dbo.Course", "DepartmentID", c => c.Int(nullable: false)); AlterColumn("dbo.Course", "Title", c => c.String(maxLength: ));
AddForeignKey("dbo.Course", "DepartmentID", "dbo.Department", "DepartmentID", cascadeDelete: true);
CreateIndex("dbo.Course", "DepartmentID");
} public override void Down()
{
(确保你已经删除了多余的AddColumn,如果迁移不成功,可将数据库删除后再试。)