Training course. Creating a complex data model for an ASP.NET MVC application, part 2

This is an extension of a series of articles devoted to developing with the Entity Framework and ASP.NET MVC 3. You can find the first chapters at the following links:

• Creating the Entity Framework data model for an ASP.NET MVC application
• Implement basic CRUD functionality with the Entity Framework in an ASP.NET MVC application .
• Sorting, filtering and paging with the Entity Framework in an ASP.NET MVC application
• Creating a complex data model for an ASP.NET MVC application

In previous lessons you learned how to work with a simple data model consisting of three entities. In this lesson you will add a few entities and links between them and learn how to work with annotations to manage classes of models.

Changes to Entity Course

In Models \ Course . cs replace the previously created code with:
')

using System; using System.Collections.Generic; using System.ComponentModel.DataAnnotations; namespace ContosoUniversity.Models { public class Course { [DatabaseGenerated(DatabaseGeneratedOption.None)] [Display(Name = "Number")] public int CourseID { get; set; } [Required(ErrorMessage = "Title is required.")] [MaxLength(50)] public string Title { get; set; } [Required(ErrorMessage = "Number of credits is required.")] [Range(0,5,ErrorMessage="Number of credits must be between 0 and 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; } } } 

DatabaseGenerated attribute

The DatabaseGenerated attribute with the None parameter specified for the CourseID property determines that the primary key value is set by the user and not generated by the database.

 [DatabaseGenerated(DatabaseGeneratedOption.None)] [Display(Name = "Number")] public int CourseID { get; set; } 

By default, the Entity Framework assumes the auto-generation of primary keys by the database, which is necessary in most situations. However, for the Course entity, numerical user-defined values ​​are used, such as 1000 for one department, 2000 for another, and so on.

Foreign Key and Navigation Properties

Properties-foreign keys and navigation properties in essence Course reflect the following connections:
The course is associated with one faculty, thus, there is a foreign key DepartmentID and Department navigation property:

public int DepartmentID {get; set; }
public virtual Department Department {get; set; }

An unlimited number of students can attend a course, so there is an Enrollments navigation property:

public virtual ICollection Enrollments {get; set; }

The course can be taught by various teachers, therefore the Instructors navigation property is available:

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

Creating an entity Department

Create Models \ Department . cs with the following code:

 using System; using System.Collections.Generic; using System.ComponentModel.DataAnnotations; namespace ContosoUniversity.Models { public class Department { public int DepartmentID { get; set; } [Required(ErrorMessage = "Department name is required.")] [MaxLength(50)] public string Name { get; set; } [DisplayFormat(DataFormatString="{0:c}")] [Required(ErrorMessage = "Budget is required.")] [Column(TypeName="money")] public decimal? Budget { get; set; } [DisplayFormat(DataFormatString="{0:d}", ApplyFormatInEditMode=true)] [Required(ErrorMessage = "Start date is required.")] 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 attribute

Earlier we used the Column attribute to change the mapping of the column name. In the code for the Department entity, this attribute is used to change the mapping of the SQL data type, that is, the column will be defined in the database with the SQL Server data type:

 [Column(TypeName="money")] public decimal? Budget { get; set; } 

This is usually not necessary because the Entity Framework automatically selects the most appropriate data type based on the CLR type that is defined for the property. Suppose the CLR type decimal becomes SQL Server type decimal. But in this case, you know for sure that the property will contain currency-related numbers, and the money type will be the most suitable for this property.

Foreign Key and Navigation Properties

Foreign keys and navigation properties reflect the following links:
The faculty may or may not contain an administrator, and the administrator is always = teacher. Therefore, the InstructorID property is defined as a foreign key for the Instructor entity, and a question mark after the int type indicates that the property may be nullable. Navigation property Administrator contains Instructor entity:

public int? InstructorID {get; set; }

public virtual Instructor Administrator {get; set; }
A faculty can have many courses, so there is a Courses navigation property:

public virtual ICollection Courses {get; set; }

Note The conventions state that the Entity Framework cascadingly deletes non-nullable foreign keys and in many-to-many communication cases. This can lead to iterative cascading deletion, causing an exception when running code. Suppose, if you do not define the Department. InstructorID as nullable, you will get the following exception when: "The referential relationship will not be allowed."

Changes related to the Student entity

In Models \ Student . cs replace the code with:

 using System; using System.Collections.Generic; using System.ComponentModel.DataAnnotations; namespace ContosoUniversity.Models { public class Student { public int StudentID { get; set; } [Required(ErrorMessage = "Last name is required.")] [Display(Name="Last Name")] [MaxLength(50)] public string LastName { get; set; } [Required(ErrorMessage = "First name is required.")] [Column("FirstName")] [Display(Name = "First Name")] [MaxLength(50)] public string FirstMidName { get; set; } [Required(ErrorMessage = "Enrollment date is required.")] [DisplayFormat(DataFormatString = "{0:d}", 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 Entity Changes

In Models \ Enrollment . cs replace the code with:

 using System; using System.Collections.Generic; using System.ComponentModel.DataAnnotations; namespace ContosoUniversity.Models { public class Enrollment { public int EnrollmentID { get; set; } public int CourseID { get; set; } public int StudentID { get; set; } [DisplayFormat(DataFormatString="{0:#.#}",ApplyFormatInEditMode=true,NullDisplayText="No grade")] public decimal? Grade { get; set; } public virtual Course Course { get; set; } public virtual Student Student { get; set; } } } 

Foreign Keys and Navigation Properties

Foreign keys and navigation properties reflect the following connections:
Each course entry corresponds to one course; therefore, the foreign key CourseID and Course navigation property is present:

public int CourseID {get; set; }

public virtual Course Course {get; set; }
Each student enrolled in the course corresponds to one student; therefore, the student ID and Student navigation property are present:

public int StudentID {get; set; }

public virtual Student Student {get; set; }

Many-to-many connections

The entities Student and Course are connected to each other by a many-to-many relationship, and the essence of the Enrollment corresponds to many-to-many join table. This means that the Enrollment table contains additional data besides foreign keys for the joined tables (in our case, the primary key and the Grade property).

The image below shows the relationships in the form of an entity diagram generated by the Entity Framework designer.



The line of each link has 1 at one end and * at the other, denoting a one-to-many connection.

If the Enrollment table does not contain grades, it is necessary to have only two foreign keys, CourseID and StudentID. In this case, it would be possible to join the table. The Instructor and Course entities are connected by a similar many-to-many relationship, and, as you can see, there is no entity class between them:



Although a merged table is required:



The Entity Framework automatically creates the CourseInstructor table, which is accessed indirectly, namely through Instructor.Courses and Course. Instructors navigation properties.

DisplayFormat attribute

The DisplayFormat attribute for the Grade property determines the formatting for the element:

 [DisplayFormat(DataFormatString="{0:#.#}",ApplyFormatInEditMode=true,NullDisplayText="No grade")] public decimal? Grade { get; set; } 

• The score is displayed in the format "3.5" or "4.0".
  
• The same formatting assessment has in the mode.
  
• If there is no grade, the inscription "No grade" is displayed.
  

Relationships on the entity diagram

The diagram below shows the linking system for the School model.



In addition to many-to-many (* - *) and one-to-many (1- *) relationships, you can also see a one-to-zero-or-one relationship (1-0..1) between Instructor and OfficeAssignment entities and zero-to-one-or-to-many (0..1 - *) Department and Instructor.

Database Context Tuning

Next, we will add new entities to the SchoolContext class and configure the mapping. In some cases, it will be necessary to use methods instead of attributes due to the fact that for some functionality the attributes simply do not exist. In other cases, you can choose to use methods or attributes (some people prefer not to use attributes).

Replace the code in DAL \ SchoolContext . cs on:

 using System; using System.Collections.Generic; using System.Data.Entity; using ContosoUniversity.Models; using System.Data.Entity.ModelConfiguration.Conventions; namespace ContosoUniversity.Models { public class SchoolContext : DbContext { public DbSet<Course> Courses { get; set; } public DbSet<Department> Departments { get; set; } public DbSet<Enrollment> Enrollments { get; set; } public DbSet<Instructor> Instructors { get; set; } public DbSet<Student> Students { get; set; } public DbSet<OfficeAssignment> OfficeAssignments { get; set; } protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.Conventions.Remove<PluralizingTableNameConvention>(); modelBuilder.Entity<Instructor>() .HasOptional(p => p.OfficeAssignment).WithRequired(p => p.Instructor); modelBuilder.Entity<Course>() .HasMany(c => c.Instructors).WithMany(i => i.Courses) .Map(t => t.MapLeftKey("CourseID") .MapRightKey("InstructorID") .ToTable("CourseInstructor")); modelBuilder.Entity<Department>() .HasOptional(x => x.Administrator); } } } 

The OnModelCreating method defines the following relationships:
One-to-zero-or-one between Instructor and OfficeAssignment:

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

Many-to-many between Instructor and Course. The code defines the table and the columns for the joined table. Code First can configure many-to-many relationships without a code, but if you do not call it, standard names will be taken for the columns, such as InstructorInstructorID for InstructorID.

modelBuilder.Entity <Course> ()
.HasMany (c => c.Instructors) .WithMany (i => i.Courses)
.Map (t => t.MapLeftKey ("CourseID")
.MapRightKey ("InstructorID")
.ToTable ("CourseInstructor"));

One-to-zero-or-one between the Department and the Instructor, using the Department.Administrator navigation property:

modelBuilder.Entity <Department> (). HasOptional (x => x.Administrator);

For more information on what is being done “behind the scenes,” read the Fluent API on the ASP.NET User Education Team blog.

Filling the database with test data

Before that, you created the DAL \ SchoolInitializer . cs to fill the database with test data. Now replace the old code with the new one, which takes into account the presence of new entities.

 using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Data.Entity; using ContosoUniversity.Models; namespace ContosoUniversity.DAL { public class SchoolInitializer : DropCreateDatabaseIfModelChanges<SchoolContext> { protected override void Seed(SchoolContext context) { var students = new List<Student> { new Student { FirstMidName = "Carson", LastName = "Alexander", EnrollmentDate = DateTime.Parse("2005-09-01") }, new Student { FirstMidName = "Meredith", LastName = "Alonso", EnrollmentDate = DateTime.Parse("2002-09-01") }, new Student { FirstMidName = "Arturo", LastName = "Anand", EnrollmentDate = DateTime.Parse("2003-09-01") }, new Student { FirstMidName = "Gytis", LastName = "Barzdukas", EnrollmentDate = DateTime.Parse("2002-09-01") }, new Student { FirstMidName = "Yan", LastName = "Li", EnrollmentDate = DateTime.Parse("2002-09-01") }, new Student { FirstMidName = "Peggy", LastName = "Justice", EnrollmentDate = DateTime.Parse("2001-09-01") }, new Student { FirstMidName = "Laura", LastName = "Norman", EnrollmentDate = DateTime.Parse("2003-09-01") }, new Student { FirstMidName = "Nino", LastName = "Olivetto", EnrollmentDate = DateTime.Parse("2005-09-01") } }; students.ForEach(s => context.Students.Add(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.Add(s)); context.SaveChanges(); var departments = new List<Department> { new Department { Name = "English", Budget = 350000, StartDate = DateTime.Parse("2007-09-01"), InstructorID = 1 }, new Department { Name = "Mathematics", Budget = 100000, StartDate = DateTime.Parse("2007-09-01"), InstructorID = 2 }, new Department { Name = "Engineering", Budget = 350000, StartDate = DateTime.Parse("2007-09-01"), InstructorID = 3 }, new Department { Name = "Economics", Budget = 100000, StartDate = DateTime.Parse("2007-09-01"), InstructorID = 4 } }; departments.ForEach(s => context.Departments.Add(s)); context.SaveChanges(); var courses = new List<Course> { new Course { CourseID = 1050, Title = "Chemistry", Credits = 3, DepartmentID = 3, Instructors = new List<Instructor>() }, new Course { CourseID = 4022, Title = "Microeconomics", Credits = 3, DepartmentID = 4, Instructors = new List<Instructor>() }, new Course { CourseID = 4041, Title = "Macroeconomics", Credits = 3, DepartmentID = 4, Instructors = new List<Instructor>() }, new Course { CourseID = 1045, Title = "Calculus", Credits = 4, DepartmentID = 2, Instructors = new List<Instructor>() }, new Course { CourseID = 3141, Title = "Trigonometry", Credits = 4, DepartmentID = 2, Instructors = new List<Instructor>() }, new Course { CourseID = 2021, Title = "Composition", Credits = 3, DepartmentID = 1, Instructors = new List<Instructor>() }, new Course { CourseID = 2042, Title = "Literature", Credits = 4, DepartmentID = 1, Instructors = new List<Instructor>() } }; courses.ForEach(s => context.Courses.Add(s)); context.SaveChanges(); courses[0].Instructors.Add(instructors[0]); courses[0].Instructors.Add(instructors[1]); courses[1].Instructors.Add(instructors[2]); courses[2].Instructors.Add(instructors[2]); courses[3].Instructors.Add(instructors[3]); courses[4].Instructors.Add(instructors[3]); courses[5].Instructors.Add(instructors[3]); courses[6].Instructors.Add(instructors[3]); context.SaveChanges(); var enrollments = new List<Enrollment> { new Enrollment { StudentID = 1, CourseID = 1050, Grade = 1 }, new Enrollment { StudentID = 1, CourseID = 4022, Grade = 3 }, new Enrollment { StudentID = 1, CourseID = 4041, Grade = 1 }, new Enrollment { StudentID = 2, CourseID = 1045, Grade = 2 }, new Enrollment { StudentID = 2, CourseID = 3141, Grade = 4 }, new Enrollment { StudentID = 2, CourseID = 2021, Grade = 4 }, new Enrollment { StudentID = 3, CourseID = 1050 }, new Enrollment { StudentID = 4, CourseID = 1050, }, new Enrollment { StudentID = 4, CourseID = 4022, Grade = 4 }, new Enrollment { StudentID = 5, CourseID = 4041, Grade = 3 }, new Enrollment { StudentID = 6, CourseID = 1045 }, new Enrollment { StudentID = 7, CourseID = 3141, Grade = 2 }, }; enrollments.ForEach(s => context.Enrollments.Add(s)); context.SaveChanges(); var officeAssignments = new List<OfficeAssignment> { new OfficeAssignment { InstructorID = 1, Location = "Smith 17" }, new OfficeAssignment { InstructorID = 2, Location = "Gowan 27" }, new OfficeAssignment { InstructorID = 3, Location = "Thompson 304" }, }; officeAssignments.ForEach(s => context.OfficeAssignments.Add(s)); context.SaveChanges(); } } } 

Pay attention to the processing of the Course entity, which is connected by a many-to-many relationship with the Instructor entity:

 var courses = new List { new Course { CourseID = 1050, Title = "Chemistry", Credits = 3, DepartmentID = 3, Instructors = new List() }, ... }; courses.ForEach(s => context.Courses.Add(s)); context.SaveChanges(); courses[0].Instructors.Add(instructors[0]); ... context.SaveChanges(); 

When creating the Course object as an empty collection (Instructors = new List ()), it makes possible the addition of Instructor entities associated with the Course using the Instructor.Add () method. If you have not created an empty List, you will not be able to add such relationships, because the Instructors property will be null and will not have an Add method.

Note Remember to delete the entire base initialization code before deploying a project to a production server.

Delete and re-create database

Run the project and select the Student Index page.



The page looks the same as it did before, but “behind the scenes” the database was deleted and re-created.

If the page does not open or you receive an error that the School.sdf file is already in use (image below), you must open Server Explorer again and close the connection to the database and then try to open the page again.



After that, open the database in Server Explorer and look in the Tables for new tables.



In addition to EdmMetadata, notice the table for which you did not create the CourseInstructor class. This is a table from the Instructor and Course.

Click on the CourseInstructor table and click Show Table Data to make sure that the data added earlier on the Course.Instructors navigation property is available.



You now have a complex data model and corresponding database. Next we will learn different ways to access data.

Acknowledgments

Thanks for the help in the translation of Alexander Belotserkovsky ( ahriman ).