Creating a complex data model - EF Core with ASP.NET Core MVC tutorial (5 of 10)
By Tom Dykstra and Rick Anderson
The Contoso University sample web application demonstrates how to create ASP.NET Core MVC web applications using Entity Framework Core and Visual Studio. For information about the tutorial series, see the first tutorial in the series.
In the previous tutorials, you worked with a simple data model that was composed of three entities. In this tutorial, you’ll add more entities and relationships and you’ll customize the data model by specifying formatting, validation, and database mapping rules.
When you’re finished, the entity classes will make up the completed data model that’s shown in the following illustration:
Customize the Data Model by Using Attributes
In this section you’ll see how to customize the data model by using attributes that specify formatting, validation, and database mapping rules. Then in several of the following sections you’ll create the complete School data model by adding attributes to the classes you already created and creating new classes for the remaining entity types in the model.
The DataType attribute
For student enrollment dates, all of the web pages currently display the time along with the date, although all you care about for this field is the date. By using data annotation attributes, you can make one code change that will fix the display format in every view that shows the data. To see an example of how to do that, you’ll add an attribute to the EnrollmentDate property in the Student class.
In Models/Student.cs, add a using statement for the System.ComponentModel.DataAnnotations namespace and add DataType and DisplayFormat attributes to the EnrollmentDate property, as shown in the following example:
[!code-csharpMain]
1: #define AfterInheritance // or Intro or StringLength or DataType or BeforeInheritance
2:
3: #if Intro
4: #region snippet_Intro
5: using System;
6: using System.Collections.Generic;
7:
8: namespace ContosoUniversity.Models
9: {
10: public class Student
11: {
12: public int ID { get; set; }
13: public string LastName { get; set; }
14: public string FirstMidName { get; set; }
15: public DateTime EnrollmentDate { get; set; }
16:
17: public ICollection<Enrollment> Enrollments { get; set; }
18: }
19: }
20: #endregion
21:
22: #elif DataType
23: #region snippet_DataType
24: using System;
25: using System.Collections.Generic;
26: using System.ComponentModel.DataAnnotations;
27:
28: namespace ContosoUniversity.Models
29: {
30: public class Student
31: {
32: public int ID { get; set; }
33: public string LastName { get; set; }
34: public string FirstMidName { get; set; }
35: [DataType(DataType.Date)]
36: [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
37: public DateTime EnrollmentDate { get; set; }
38:
39: public ICollection<Enrollment> Enrollments { get; set; }
40: }
41: }
42: #endregion
43:
44: #elif StringLength
45: #region snippet_StringLength
46: using System;
47: using System.Collections.Generic;
48: using System.ComponentModel.DataAnnotations;
49:
50: namespace ContosoUniversity.Models
51: {
52: public class Student
53: {
54: public int ID { get; set; }
55: [StringLength(50)]
56: public string LastName { get; set; }
57: [StringLength(50, ErrorMessage = "First name cannot be longer than 50 characters.")]
58: public string FirstMidName { get; set; }
59: [DataType(DataType.Date)]
60: [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
61: public DateTime EnrollmentDate { get; set; }
62:
63: public ICollection<Enrollment> Enrollments { get; set; }
64: }
65: }
66: #endregion
67:
68: #elif Column
69: #region snippet_Column
70: using System;
71: using System.Collections.Generic;
72: using System.ComponentModel.DataAnnotations;
73: using System.ComponentModel.DataAnnotations.Schema;
74:
75: namespace ContosoUniversity.Models
76: {
77: public class Student
78: {
79: public int ID { get; set; }
80: [StringLength(50)]
81: public string LastName { get; set; }
82: [StringLength(50, ErrorMessage = "First name cannot be longer than 50 characters.")]
83: [Column("FirstName")]
84: public string FirstMidName { get; set; }
85: [DataType(DataType.Date)]
86: [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
87: public DateTime EnrollmentDate { get; set; }
88:
89: public ICollection<Enrollment> Enrollments { get; set; }
90: }
91: }
92: #endregion
93:
94:
95: #elif BeforeInheritance
96: #region snippet_BeforeInheritance
97: using System;
98: using System.Collections.Generic;
99: using System.ComponentModel.DataAnnotations;
100: using System.ComponentModel.DataAnnotations.Schema;
101:
102: namespace ContosoUniversity.Models
103: {
104: public class Student
105: {
106: public int ID { get; set; }
107: [Required]
108: [StringLength(50)]
109: [Display(Name = "Last Name")]
110: public string LastName { get; set; }
111: [Required]
112: [StringLength(50, ErrorMessage = "First name cannot be longer than 50 characters.")]
113: [Column("FirstName")]
114: [Display(Name = "First Name")]
115: public string FirstMidName { get; set; }
116: [DataType(DataType.Date)]
117: [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
118: [Display(Name = "Enrollment Date")]
119: public DateTime EnrollmentDate { get; set; }
120: [Display(Name = "Full Name")]
121: public string FullName
122: {
123: get
124: {
125: return LastName + ", " + FirstMidName;
126: }
127: }
128:
129: public ICollection<Enrollment> Enrollments { get; set; }
130: }
131: }
132: #endregion
133: #elif AfterInheritance
134: #region snippet_AfterInheritance
135: using System;
136: using System.Collections.Generic;
137: using System.ComponentModel.DataAnnotations;
138: using System.ComponentModel.DataAnnotations.Schema;
139:
140: namespace ContosoUniversity.Models
141: {
142: public class Student : Person
143: {
144: [DataType(DataType.Date)]
145: [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
146: [Display(Name = "Enrollment Date")]
147: public DateTime EnrollmentDate { get; set; }
148:
149:
150: public ICollection<Enrollment> Enrollments { get; set; }
151: }
152: }
153: #endregion
154: #endif
The DataType attribute is used to specify a data type that is more specific than the database intrinsic type. In this case we only want to keep track of the date, not the date and time. The DataType Enumeration provides for many data types, such as Date, Time, PhoneNumber, Currency, EmailAddress, and more. The DataType attribute can also enable the application to automatically provide type-specific features. For example, a mailto: link can be created for DataType.EmailAddress, and a date selector can be provided for DataType.Date in browsers that support HTML5. The DataType attribute emits HTML 5 data- (pronounced data dash) attributes that HTML 5 browsers can understand. The DataType attributes do not provide any validation.
DataType.Date does not specify the format of the date that is displayed. By default, the data field is displayed according to the default formats based on the server’s CultureInfo.
The DisplayFormat attribute is used to explicitly specify the date format:
The ApplyFormatInEditMode setting specifies that the formatting should also be applied when the value is displayed in a text box for editing. (You might not want that for some fields – for example, for currency values, you might not want the currency symbol in the text box for editing.)
You can use the DisplayFormat attribute by itself, but it’s generally a good idea to use the DataType attribute also. The DataType attribute conveys the semantics of the data as opposed to how to render it on a screen, and provides the following benefits that you don’t get with DisplayFormat:
The browser can enable HTML5 features (for example to show a calendar control, the locale-appropriate currency symbol, email links, some client-side input validation, etc.).
By default, the browser will render data using the correct format based on your locale.
For more information, see the <input> tag helper documentation.
Run the app, go to the Students Index page and notice that times are no longer displayed for the enrollment dates. The same will be true for any view that uses the Student model.
The StringLength attribute
You can also specify data validation rules and validation error messages using attributes. The StringLength attribute sets the maximum length in the database and provides client side and server side validation for ASP.NET MVC. You can also specify the minimum string length in this attribute, but the minimum value has no impact on the database schema.
Suppose you want to ensure that users don’t enter more than 50 characters for a name. To add this limitation, add StringLength attributes to the LastName and FirstMidName properties, as shown in the following example:
[!code-csharpMain]
1: #define AfterInheritance // or Intro or StringLength or DataType or BeforeInheritance
2:
3: #if Intro
4: #region snippet_Intro
5: using System;
6: using System.Collections.Generic;
7:
8: namespace ContosoUniversity.Models
9: {
10: public class Student
11: {
12: public int ID { get; set; }
13: public string LastName { get; set; }
14: public string FirstMidName { get; set; }
15: public DateTime EnrollmentDate { get; set; }
16:
17: public ICollection<Enrollment> Enrollments { get; set; }
18: }
19: }
20: #endregion
21:
22: #elif DataType
23: #region snippet_DataType
24: using System;
25: using System.Collections.Generic;
26: using System.ComponentModel.DataAnnotations;
27:
28: namespace ContosoUniversity.Models
29: {
30: public class Student
31: {
32: public int ID { get; set; }
33: public string LastName { get; set; }
34: public string FirstMidName { get; set; }
35: [DataType(DataType.Date)]
36: [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
37: public DateTime EnrollmentDate { get; set; }
38:
39: public ICollection<Enrollment> Enrollments { get; set; }
40: }
41: }
42: #endregion
43:
44: #elif StringLength
45: #region snippet_StringLength
46: using System;
47: using System.Collections.Generic;
48: using System.ComponentModel.DataAnnotations;
49:
50: namespace ContosoUniversity.Models
51: {
52: public class Student
53: {
54: public int ID { get; set; }
55: [StringLength(50)]
56: public string LastName { get; set; }
57: [StringLength(50, ErrorMessage = "First name cannot be longer than 50 characters.")]
58: public string FirstMidName { get; set; }
59: [DataType(DataType.Date)]
60: [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
61: public DateTime EnrollmentDate { get; set; }
62:
63: public ICollection<Enrollment> Enrollments { get; set; }
64: }
65: }
66: #endregion
67:
68: #elif Column
69: #region snippet_Column
70: using System;
71: using System.Collections.Generic;
72: using System.ComponentModel.DataAnnotations;
73: using System.ComponentModel.DataAnnotations.Schema;
74:
75: namespace ContosoUniversity.Models
76: {
77: public class Student
78: {
79: public int ID { get; set; }
80: [StringLength(50)]
81: public string LastName { get; set; }
82: [StringLength(50, ErrorMessage = "First name cannot be longer than 50 characters.")]
83: [Column("FirstName")]
84: public string FirstMidName { get; set; }
85: [DataType(DataType.Date)]
86: [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
87: public DateTime EnrollmentDate { get; set; }
88:
89: public ICollection<Enrollment> Enrollments { get; set; }
90: }
91: }
92: #endregion
93:
94:
95: #elif BeforeInheritance
96: #region snippet_BeforeInheritance
97: using System;
98: using System.Collections.Generic;
99: using System.ComponentModel.DataAnnotations;
100: using System.ComponentModel.DataAnnotations.Schema;
101:
102: namespace ContosoUniversity.Models
103: {
104: public class Student
105: {
106: public int ID { get; set; }
107: [Required]
108: [StringLength(50)]
109: [Display(Name = "Last Name")]
110: public string LastName { get; set; }
111: [Required]
112: [StringLength(50, ErrorMessage = "First name cannot be longer than 50 characters.")]
113: [Column("FirstName")]
114: [Display(Name = "First Name")]
115: public string FirstMidName { get; set; }
116: [DataType(DataType.Date)]
117: [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
118: [Display(Name = "Enrollment Date")]
119: public DateTime EnrollmentDate { get; set; }
120: [Display(Name = "Full Name")]
121: public string FullName
122: {
123: get
124: {
125: return LastName + ", " + FirstMidName;
126: }
127: }
128:
129: public ICollection<Enrollment> Enrollments { get; set; }
130: }
131: }
132: #endregion
133: #elif AfterInheritance
134: #region snippet_AfterInheritance
135: using System;
136: using System.Collections.Generic;
137: using System.ComponentModel.DataAnnotations;
138: using System.ComponentModel.DataAnnotations.Schema;
139:
140: namespace ContosoUniversity.Models
141: {
142: public class Student : Person
143: {
144: [DataType(DataType.Date)]
145: [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
146: [Display(Name = "Enrollment Date")]
147: public DateTime EnrollmentDate { get; set; }
148:
149:
150: public ICollection<Enrollment> Enrollments { get; set; }
151: }
152: }
153: #endregion
154: #endif
The StringLength attribute won’t prevent a user from entering white space for a name. You can use the RegularExpression attribute to apply restrictions to the input. For example, the following code requires the first character to be upper case and the remaining characters to be alphabetical:
The MaxLength attribute provides functionality similar to the StringLength attribute but doesn’t provide client side validation.
The database model has now changed in a way that requires a change in the database schema. You’ll use migrations to update the schema without losing any data that you may have added to the database by using the application UI.
Save your changes and build the project. Then open the command window in the project folder and enter the following commands:
dotnet ef migrations add MaxLengthOnNamesdotnet ef database updateThe migrations add command warns that data loss may occur, because the change makes the maximum length shorter for two columns. Migrations creates a file named *<timeStamp>_MaxLengthOnNames.cs*. This file contains code in the Up method that will update the database to match the current data model. The database update command ran that code.
The timestamp prefixed to the migrations file name is used by Entity Framework to order the migrations. You can create multiple migrations before running the update-database command, and then all of the migrations are applied in the order in which they were created.
Run the app, select the Students tab, click Create New, and enter either name longer than 50 characters. When you click Create, client side validation shows an error message.
The Column attribute
You can also use attributes to control how your classes and properties are mapped to the database. Suppose you had used the name FirstMidName for the first-name field because the field might also contain a middle name. But you want the database column to be named FirstName, because users who will be writing ad-hoc queries against the database are accustomed to that name. To make this mapping, you can use the Column attribute.
The Column attribute specifies that when the database is created, the column of the Student table that maps to the FirstMidName property will be named FirstName. In other words, when your code refers to Student.FirstMidName, the data will come from or be updated in the FirstName column of the Student table. If you don’t specify column names, they are given the same name as the property name.
In the Student.cs file, add a using statement for System.ComponentModel.DataAnnotations.Schema and add the column name attribute to the FirstMidName property, as shown in the following highlighted code:
[!code-csharpMain]
1: #define AfterInheritance // or Intro or StringLength or DataType or BeforeInheritance
2:
3: #if Intro
4: #region snippet_Intro
5: using System;
6: using System.Collections.Generic;
7:
8: namespace ContosoUniversity.Models
9: {
10: public class Student
11: {
12: public int ID { get; set; }
13: public string LastName { get; set; }
14: public string FirstMidName { get; set; }
15: public DateTime EnrollmentDate { get; set; }
16:
17: public ICollection<Enrollment> Enrollments { get; set; }
18: }
19: }
20: #endregion
21:
22: #elif DataType
23: #region snippet_DataType
24: using System;
25: using System.Collections.Generic;
26: using System.ComponentModel.DataAnnotations;
27:
28: namespace ContosoUniversity.Models
29: {
30: public class Student
31: {
32: public int ID { get; set; }
33: public string LastName { get; set; }
34: public string FirstMidName { get; set; }
35: [DataType(DataType.Date)]
36: [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
37: public DateTime EnrollmentDate { get; set; }
38:
39: public ICollection<Enrollment> Enrollments { get; set; }
40: }
41: }
42: #endregion
43:
44: #elif StringLength
45: #region snippet_StringLength
46: using System;
47: using System.Collections.Generic;
48: using System.ComponentModel.DataAnnotations;
49:
50: namespace ContosoUniversity.Models
51: {
52: public class Student
53: {
54: public int ID { get; set; }
55: [StringLength(50)]
56: public string LastName { get; set; }
57: [StringLength(50, ErrorMessage = "First name cannot be longer than 50 characters.")]
58: public string FirstMidName { get; set; }
59: [DataType(DataType.Date)]
60: [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
61: public DateTime EnrollmentDate { get; set; }
62:
63: public ICollection<Enrollment> Enrollments { get; set; }
64: }
65: }
66: #endregion
67:
68: #elif Column
69: #region snippet_Column
70: using System;
71: using System.Collections.Generic;
72: using System.ComponentModel.DataAnnotations;
73: using System.ComponentModel.DataAnnotations.Schema;
74:
75: namespace ContosoUniversity.Models
76: {
77: public class Student
78: {
79: public int ID { get; set; }
80: [StringLength(50)]
81: public string LastName { get; set; }
82: [StringLength(50, ErrorMessage = "First name cannot be longer than 50 characters.")]
83: [Column("FirstName")]
84: public string FirstMidName { get; set; }
85: [DataType(DataType.Date)]
86: [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
87: public DateTime EnrollmentDate { get; set; }
88:
89: public ICollection<Enrollment> Enrollments { get; set; }
90: }
91: }
92: #endregion
93:
94:
95: #elif BeforeInheritance
96: #region snippet_BeforeInheritance
97: using System;
98: using System.Collections.Generic;
99: using System.ComponentModel.DataAnnotations;
100: using System.ComponentModel.DataAnnotations.Schema;
101:
102: namespace ContosoUniversity.Models
103: {
104: public class Student
105: {
106: public int ID { get; set; }
107: [Required]
108: [StringLength(50)]
109: [Display(Name = "Last Name")]
110: public string LastName { get; set; }
111: [Required]
112: [StringLength(50, ErrorMessage = "First name cannot be longer than 50 characters.")]
113: [Column("FirstName")]
114: [Display(Name = "First Name")]
115: public string FirstMidName { get; set; }
116: [DataType(DataType.Date)]
117: [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
118: [Display(Name = "Enrollment Date")]
119: public DateTime EnrollmentDate { get; set; }
120: [Display(Name = "Full Name")]
121: public string FullName
122: {
123: get
124: {
125: return LastName + ", " + FirstMidName;
126: }
127: }
128:
129: public ICollection<Enrollment> Enrollments { get; set; }
130: }
131: }
132: #endregion
133: #elif AfterInheritance
134: #region snippet_AfterInheritance
135: using System;
136: using System.Collections.Generic;
137: using System.ComponentModel.DataAnnotations;
138: using System.ComponentModel.DataAnnotations.Schema;
139:
140: namespace ContosoUniversity.Models
141: {
142: public class Student : Person
143: {
144: [DataType(DataType.Date)]
145: [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
146: [Display(Name = "Enrollment Date")]
147: public DateTime EnrollmentDate { get; set; }
148:
149:
150: public ICollection<Enrollment> Enrollments { get; set; }
151: }
152: }
153: #endregion
154: #endif
The addition of the Column attribute changes the model backing the SchoolContext, so it won’t match the database.
Save your changes and build the project. Then open the command window in the project folder and enter the following commands to create another migration:
dotnet ef migrations add ColumnFirstNamedotnet ef database updateIn SQL Server Object Explorer, open the Student table designer by double-clicking the Student table.
Before you applied the first two migrations, the name columns were of type nvarchar(MAX). They are now nvarchar(50) and the column name has changed from FirstMidName to FirstName.
[!Note] If you try to compile before you finish creating all of the entity classes in the following sections, you might get compiler errors.
Final changes to the Student entity
In Models/Student.cs, replace the code you added earlier with the following code. The changes are highlighted.
[!code-csharpMain]
1: #define AfterInheritance // or Intro or StringLength or DataType or BeforeInheritance
2:
3: #if Intro
4: #region snippet_Intro
5: using System;
6: using System.Collections.Generic;
7:
8: namespace ContosoUniversity.Models
9: {
10: public class Student
11: {
12: public int ID { get; set; }
13: public string LastName { get; set; }
14: public string FirstMidName { get; set; }
15: public DateTime EnrollmentDate { get; set; }
16:
17: public ICollection<Enrollment> Enrollments { get; set; }
18: }
19: }
20: #endregion
21:
22: #elif DataType
23: #region snippet_DataType
24: using System;
25: using System.Collections.Generic;
26: using System.ComponentModel.DataAnnotations;
27:
28: namespace ContosoUniversity.Models
29: {
30: public class Student
31: {
32: public int ID { get; set; }
33: public string LastName { get; set; }
34: public string FirstMidName { get; set; }
35: [DataType(DataType.Date)]
36: [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
37: public DateTime EnrollmentDate { get; set; }
38:
39: public ICollection<Enrollment> Enrollments { get; set; }
40: }
41: }
42: #endregion
43:
44: #elif StringLength
45: #region snippet_StringLength
46: using System;
47: using System.Collections.Generic;
48: using System.ComponentModel.DataAnnotations;
49:
50: namespace ContosoUniversity.Models
51: {
52: public class Student
53: {
54: public int ID { get; set; }
55: [StringLength(50)]
56: public string LastName { get; set; }
57: [StringLength(50, ErrorMessage = "First name cannot be longer than 50 characters.")]
58: public string FirstMidName { get; set; }
59: [DataType(DataType.Date)]
60: [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
61: public DateTime EnrollmentDate { get; set; }
62:
63: public ICollection<Enrollment> Enrollments { get; set; }
64: }
65: }
66: #endregion
67:
68: #elif Column
69: #region snippet_Column
70: using System;
71: using System.Collections.Generic;
72: using System.ComponentModel.DataAnnotations;
73: using System.ComponentModel.DataAnnotations.Schema;
74:
75: namespace ContosoUniversity.Models
76: {
77: public class Student
78: {
79: public int ID { get; set; }
80: [StringLength(50)]
81: public string LastName { get; set; }
82: [StringLength(50, ErrorMessage = "First name cannot be longer than 50 characters.")]
83: [Column("FirstName")]
84: public string FirstMidName { get; set; }
85: [DataType(DataType.Date)]
86: [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
87: public DateTime EnrollmentDate { get; set; }
88:
89: public ICollection<Enrollment> Enrollments { get; set; }
90: }
91: }
92: #endregion
93:
94:
95: #elif BeforeInheritance
96: #region snippet_BeforeInheritance
97: using System;
98: using System.Collections.Generic;
99: using System.ComponentModel.DataAnnotations;
100: using System.ComponentModel.DataAnnotations.Schema;
101:
102: namespace ContosoUniversity.Models
103: {
104: public class Student
105: {
106: public int ID { get; set; }
107: [Required]
108: [StringLength(50)]
109: [Display(Name = "Last Name")]
110: public string LastName { get; set; }
111: [Required]
112: [StringLength(50, ErrorMessage = "First name cannot be longer than 50 characters.")]
113: [Column("FirstName")]
114: [Display(Name = "First Name")]
115: public string FirstMidName { get; set; }
116: [DataType(DataType.Date)]
117: [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
118: [Display(Name = "Enrollment Date")]
119: public DateTime EnrollmentDate { get; set; }
120: [Display(Name = "Full Name")]
121: public string FullName
122: {
123: get
124: {
125: return LastName + ", " + FirstMidName;
126: }
127: }
128:
129: public ICollection<Enrollment> Enrollments { get; set; }
130: }
131: }
132: #endregion
133: #elif AfterInheritance
134: #region snippet_AfterInheritance
135: using System;
136: using System.Collections.Generic;
137: using System.ComponentModel.DataAnnotations;
138: using System.ComponentModel.DataAnnotations.Schema;
139:
140: namespace ContosoUniversity.Models
141: {
142: public class Student : Person
143: {
144: [DataType(DataType.Date)]
145: [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
146: [Display(Name = "Enrollment Date")]
147: public DateTime EnrollmentDate { get; set; }
148:
149:
150: public ICollection<Enrollment> Enrollments { get; set; }
151: }
152: }
153: #endregion
154: #endif
The Required attribute
The Required attribute makes the name properties required fields. The Required attribute is not needed for non-nullable types such as value types (DateTime, int, double, float, etc.). Types that can’t be null are automatically treated as required fields.
You could remove the Required attribute and replace it with a minimum length parameter for the StringLength attribute:
[Display(Name = "Last Name")]
[StringLength(50, MinimumLength=1)]
public string LastName { get; set; }The Display attribute
The Display attribute specifies that the caption for the text boxes should be “First Name”, “Last Name”, “Full Name”, and “Enrollment Date” instead of the property name in each instance (which has no space dividing the words).
The FullName calculated property
FullName is a calculated property that returns a value that’s created by concatenating two other properties. Therefore it has only a get accessor, and no FullName column will be generated in the database.
Create the Instructor Entity
Create Models/Instructor.cs, replacing the template code with the following code:
[!code-csharpMain]
1: #define AfterInheritance // or BeforeInheritance
2:
3: #if BeforeInheritance
4: #region snippet_BeforeInheritance
5: using System;
6: using System.Collections.Generic;
7: using System.ComponentModel.DataAnnotations;
8: using System.ComponentModel.DataAnnotations.Schema;
9:
10: namespace ContosoUniversity.Models
11: {
12: public class Instructor
13: {
14: public int ID { get; set; }
15:
16: [Required]
17: [Display(Name = "Last Name")]
18: [StringLength(50)]
19: public string LastName { get; set; }
20:
21: [Required]
22: [Column("FirstName")]
23: [Display(Name = "First Name")]
24: [StringLength(50)]
25: public string FirstMidName { get; set; }
26:
27: [DataType(DataType.Date)]
28: [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
29: [Display(Name = "Hire Date")]
30: public DateTime HireDate { get; set; }
31:
32: [Display(Name = "Full Name")]
33: public string FullName
34: {
35: get { return LastName + ", " + FirstMidName; }
36: }
37:
38: public ICollection<CourseAssignment> CourseAssignments { get; set; }
39: public OfficeAssignment OfficeAssignment { get; set; }
40: }
41: }
42: #endregion
43: #elif AfterInheritance
44: #region snippet_AfterInheritance
45: using System;
46: using System.Collections.Generic;
47: using System.ComponentModel.DataAnnotations;
48: using System.ComponentModel.DataAnnotations.Schema;
49:
50: namespace ContosoUniversity.Models
51: {
52: public class Instructor : Person
53: {
54: [DataType(DataType.Date)]
55: [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
56: [Display(Name = "Hire Date")]
57: public DateTime HireDate { get; set; }
58:
59: public ICollection<CourseAssignment> CourseAssignments { get; set; }
60: public OfficeAssignment OfficeAssignment { get; set; }
61: }
62: }
63: #endregion
64: #endif
Notice that several properties are the same in the Student and Instructor entities. In the Implementing Inheritance tutorial later in this series, you’ll refactor this code to eliminate the redundancy.
You can put multiple attributes on one line, so you could also write the HireDate attributes as follows:
The CourseAssignments and OfficeAssignment navigation properties
The CourseAssignments and OfficeAssignment properties are navigation properties.
An instructor can teach any number of courses, so CourseAssignments is defined as a collection.
If a navigation property can hold multiple entities, its type must be a list in which entries can be added, deleted, and updated. You can specify ICollection<T> or a type such as List<T> or HashSet<T>. If you specify ICollection<T>, EF creates a HashSet<T> collection by default.
The reason why these are CourseAssignment entities is explained below in the section about many-to-many relationships.
Contoso University business rules state that an instructor can only have at most one office, so the OfficeAssignment property holds a single OfficeAssignment entity (which may be null if no office is assigned).
Create the OfficeAssignment entity
Create Models/OfficeAssignment.cs with the following code:
[!code-csharpMain]
1: using System.ComponentModel.DataAnnotations;
2: using System.ComponentModel.DataAnnotations.Schema;
3:
4: namespace ContosoUniversity.Models
5: {
6: public class OfficeAssignment
7: {
8: [Key]
9: public int InstructorID { get; set; }
10: [StringLength(50)]
11: [Display(Name = "Office Location")]
12: public string Location { get; set; }
13:
14: public Instructor Instructor { get; set; }
15: }
16: }
The Key attribute
There’s a one-to-zero-or-one relationship between the Instructor and the OfficeAssignment entities. An office assignment only exists in relation to the instructor it’s assigned to, and therefore its primary key is also its foreign key to the Instructor entity. But the Entity Framework can’t automatically recognize InstructorID as the primary key of this entity because its name doesn’t follow the ID or classnameID naming convention. Therefore, the Key attribute is used to identify it as the key:
You can also use the Key attribute if the entity does have its own primary key but you want to name the property something other than classnameID or ID.
By default, EF treats the key as non-database-generated because the column is for an identifying relationship.
The Instructor navigation property
The Instructor entity has a nullable OfficeAssignment navigation property (because an instructor might not have an office assignment), and the OfficeAssignment entity has a non-nullable Instructor navigation property (because an office assignment can’t exist without an instructor – InstructorID is non-nullable). When an Instructor entity has a related OfficeAssignment entity, each entity will have a reference to the other one in its navigation property.
You could put a [Required] attribute on the Instructor navigation property to specify that there must be a related instructor, but you don’t have to do that because the InstructorID foreign key (which is also the key to this table) is non-nullable.
Modify the Course Entity
In Models/Course.cs, replace the code you added earlier with the following code. The changes are highlighted.
[!code-csharpMain]
1: #define Final // or Intro
2:
3: #if Intro
4: #region snippet_Intro
5: using System.Collections.Generic;
6: using System.ComponentModel.DataAnnotations.Schema;
7:
8: namespace ContosoUniversity.Models
9: {
10: public class Course
11: {
12: [DatabaseGenerated(DatabaseGeneratedOption.None)]
13: public int CourseID { get; set; }
14: public string Title { get; set; }
15: public int Credits { get; set; }
16:
17: public ICollection<Enrollment> Enrollments { get; set; }
18: }
19: }
20: #endregion
21:
22: #elif Final
23: #region snippet_Final
24: using System.Collections.Generic;
25: using System.ComponentModel.DataAnnotations;
26: using System.ComponentModel.DataAnnotations.Schema;
27:
28: namespace ContosoUniversity.Models
29: {
30: public class Course
31: {
32: [DatabaseGenerated(DatabaseGeneratedOption.None)]
33: [Display(Name = "Number")]
34: public int CourseID { get; set; }
35:
36: [StringLength(50, MinimumLength = 3)]
37: public string Title { get; set; }
38:
39: [Range(0, 5)]
40: public int Credits { get; set; }
41:
42: public int DepartmentID { get; set; }
43:
44: public Department Department { get; set; }
45: public ICollection<Enrollment> Enrollments { get; set; }
46: public ICollection<CourseAssignment> CourseAssignments { get; set; }
47: }
48: }
49: #endregion
50: #endif
The course entity has a foreign key property DepartmentID which points to the related Department entity and it has a Department navigation property.
The Entity Framework doesn’t require you to add a foreign key property to your data model when you have a navigation property for a related entity. EF automatically creates foreign keys in the database wherever they are needed and creates shadow properties for them. But having the foreign key in the data model can make updates simpler and more efficient. For example, when you fetch a course entity to edit, the Department entity is null if you don’t load it, so when you update the course entity, you would have to first fetch the Department entity. When the foreign key property DepartmentID is included in the data model, you don’t need to fetch the Department entity before you update.
The DatabaseGenerated attribute
The DatabaseGenerated attribute with the None parameter on the CourseID property specifies that primary key values are provided by the user rather than generated by the database.
[DatabaseGenerated(DatabaseGeneratedOption.None)]
[Display(Name = "Number")]
public int CourseID { get; set; }By default, Entity Framework assumes that primary key values are generated by the database. That’s what you want in most scenarios. However, for Course entities, you’ll use a user-specified course number such as a 1000 series for one department, a 2000 series for another department, and so on.
The DatabaseGenerated attribute can also be used to generate default values, as in the case of database columns used to record the date a row was created or updated. For more information, see Generated Properties.
Foreign key and navigation properties
The foreign key properties and navigation properties in the Course entity reflect the following relationships:
A course is assigned to one department, so there’s a DepartmentID foreign key and a Department navigation property for the reasons mentioned above.
A course can have any number of students enrolled in it, so the Enrollments navigation property is a collection:
A course may be taught by multiple instructors, so the CourseAssignments navigation property is a collection (the type CourseAssignment is explained later):
Create the Department entity
Create Models/Department.cs with the following code:
[!code-csharpMain]
1: #define Final
2:
3: #if Begin
4: #region snippet_Begin
5: using System;
6: using System.Collections.Generic;
7: using System.ComponentModel.DataAnnotations;
8: using System.ComponentModel.DataAnnotations.Schema;
9:
10: namespace ContosoUniversity.Models
11: {
12: public class Department
13: {
14: public int DepartmentID { get; set; }
15:
16: [StringLength(50, MinimumLength = 3)]
17: public string Name { get; set; }
18:
19: [DataType(DataType.Currency)]
20: [Column(TypeName = "money")]
21: public decimal Budget { get; set; }
22:
23: [DataType(DataType.Date)]
24: [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
25: [Display(Name = "Start Date")]
26: public DateTime StartDate { get; set; }
27:
28: public int? InstructorID { get; set; }
29:
30: public Instructor Administrator { get; set; }
31: public ICollection<Course> Courses { get; set; }
32: }
33: }
34: #endregion
35:
36:
37: #elif Final
38: #region snippet_Final
39: using System;
40: using System.Collections.Generic;
41: using System.ComponentModel.DataAnnotations;
42: using System.ComponentModel.DataAnnotations.Schema;
43:
44: namespace ContosoUniversity.Models
45: {
46: public class Department
47: {
48: public int DepartmentID { get; set; }
49:
50: [StringLength(50, MinimumLength = 3)]
51: public string Name { get; set; }
52:
53: [DataType(DataType.Currency)]
54: [Column(TypeName = "money")]
55: public decimal Budget { get; set; }
56:
57: [DataType(DataType.Date)]
58: [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
59: [Display(Name = "Start Date")]
60: public DateTime StartDate { get; set; }
61:
62: public int? InstructorID { get; set; }
63:
64: [Timestamp]
65: public byte[] RowVersion { get; set; }
66:
67: public Instructor Administrator { get; set; }
68: public ICollection<Course> Courses { get; set; }
69: }
70: }
71: #endregion
72: #endif
The Column attribute
Earlier you used the Column attribute to change column name mapping. In the code for the Department entity, the Column attribute is being used to change SQL data type mapping so that the column will be defined using the SQL Server money type in the database:
Column mapping is generally not required, because the Entity Framework chooses the appropriate SQL Server data type based on the CLR type that you define for the property. The CLR decimal type maps to a SQL Server decimal type. But in this case you know that the column will be holding currency amounts, and the money data type is more appropriate for that.
Foreign key and navigation properties
The foreign key and navigation properties reflect the following relationships:
A department may or may not have an administrator, and an administrator is always an instructor. Therefore the InstructorID property is included as the foreign key to the Instructor entity, and a question mark is added after the int type designation to mark the property as nullable. The navigation property is named Administrator but holds an Instructor entity:
A department may have many courses, so there’s a Courses navigation property:
[!NOTE] By convention, the Entity Framework enables cascade delete for non-nullable foreign keys and for many-to-many relationships. This can result in circular cascade delete rules, which will cause an exception when you try to add a migration. For example, if you didn’t define the Department.InstructorID property as nullable, EF would configure a cascade delete rule to delete the instructor when you delete the department, which is not what you want to have happen. If your business rules required the
InstructorIDproperty to be non-nullable, you would have to use the following fluent API statement to disable cascade delete on the relationship:
Modify the Enrollment entity
In Models/Enrollment.cs, replace the code you added earlier with the following code:
[!code-csharpMain]
1: #define Final // or Intro
2:
3: #if Intro
4: #region snippet_Intro
5: namespace ContosoUniversity.Models
6: {
7: public enum Grade
8: {
9: A, B, C, D, F
10: }
11:
12: public class Enrollment
13: {
14: public int EnrollmentID { get; set; }
15: public int CourseID { get; set; }
16: public int StudentID { get; set; }
17: public Grade? Grade { get; set; }
18:
19: public Course Course { get; set; }
20: public Student Student { get; set; }
21: }
22: }
23: #endregion
24:
25: #elif Final
26: #region snippet_Final
27: using System.ComponentModel.DataAnnotations;
28: using System.ComponentModel.DataAnnotations.Schema;
29:
30: namespace ContosoUniversity.Models
31: {
32: public enum Grade
33: {
34: A, B, C, D, F
35: }
36:
37: public class Enrollment
38: {
39: public int EnrollmentID { get; set; }
40: public int CourseID { get; set; }
41: public int StudentID { get; set; }
42: [DisplayFormat(NullDisplayText = "No grade")]
43: public Grade? Grade { get; set; }
44:
45: public Course Course { get; set; }
46: public Student Student { get; set; }
47: }
48: }
49: #endregion
50: #endif
Foreign key and navigation properties
The foreign key properties and navigation properties reflect the following relationships:
An enrollment record is for a single course, so there’s a CourseID foreign key property and a Course navigation property:
An enrollment record is for a single student, so there’s a StudentID foreign key property and a Student navigation property:
Many-to-Many Relationships
There’s a many-to-many relationship between the Student and Course entities, and the Enrollment entity functions as a many-to-many join table with payload in the database. “With payload” means that the Enrollment table contains additional data besides foreign keys for the joined tables (in this case, a primary key and a Grade property).
The following illustration shows what these relationships look like in an entity diagram. (This diagram was generated using the Entity Framework Power Tools for EF 6.x; creating the diagram isn’t part of the tutorial, it’s just being used here as an illustration.)
Each relationship line has a 1 at one end and an asterisk (*) at the other, indicating a one-to-many relationship.
If the Enrollment table didn’t include grade information, it would only need to contain the two foreign keys CourseID and StudentID. In that case, it would be a many-to-many join table without payload (or a pure join table) in the database. The Instructor and Course entities have that kind of many-to-many relationship, and your next step is to create an entity class to function as a join table without payload.
(EF 6.x supports implicit join tables for many-to-many relationships, but EF Core does not. For more information, see the discussion in the EF Core GitHub repository.)
The CourseAssignment entity
Create Models/CourseAssignment.cs with the following code:
[!code-csharpMain]
1: using System;
2: using System.Collections.Generic;
3: using System.ComponentModel.DataAnnotations;
4: using System.ComponentModel.DataAnnotations.Schema;
5:
6: namespace ContosoUniversity.Models
7: {
8: public class CourseAssignment
9: {
10: public int InstructorID { get; set; }
11: public int CourseID { get; set; }
12: public Instructor Instructor { get; set; }
13: public Course Course { get; set; }
14: }
15: }
Join entity names
A join table is required in the database for the Instructor-to-Courses many-to-many relationship, and it has to be represented by an entity set. It’s common to name a join entity EntityName1EntityName2, which in this case would be CourseInstructor. However, we recommend that you choose a name that describes the relationship. Data models start out simple and grow, with no-payload joins frequently getting payloads later. If you start with a descriptive entity name, you won’t have to change the name later. Ideally, the join entity would have its own natural (possibly single word) name in the business domain. For example, Books and Customers could be linked through Ratings. For this relationship, CourseAssignment is a better choice than CourseInstructor.
Composite key
Since the foreign keys are not nullable and together uniquely identify each row of the table, there is no need for a separate primary key. The InstructorID and CourseID properties should function as a composite primary key. The only way to identify composite primary keys to EF is by using the fluent API (it can’t be done by using attributes). You’ll see how to configure the composite primary key in the next section.
The composite key ensures that while you can have multiple rows for one course, and multiple rows for one instructor, you can’t have multiple rows for the same instructor and course. The Enrollment join entity defines its own primary key, so duplicates of this sort are possible. To prevent such duplicates, you could add a unique index on the foreign key fields, or configure Enrollment with a primary composite key similar to CourseAssignment. For more information, see Indexes.
Update the database context
Add the following highlighted code to the Data/SchoolContext.cs file:
[!code-csharpMain]
1: #define AfterInheritance // or Intro or TableNames or BeforeInheritance
2:
3: #if Intro
4: #region snippet_Intro
5: using ContosoUniversity.Models;
6: using Microsoft.EntityFrameworkCore;
7:
8: namespace ContosoUniversity.Data
9: {
10: public class SchoolContext : DbContext
11: {
12: public SchoolContext(DbContextOptions<SchoolContext> options) : base(options)
13: {
14: }
15:
16: public DbSet<Course> Courses { get; set; }
17: public DbSet<Enrollment> Enrollments { get; set; }
18: public DbSet<Student> Students { get; set; }
19: }
20: }
21: #endregion
22:
23: #elif TableNames
24: #region snippet_TableNames
25: using ContosoUniversity.Models;
26: using Microsoft.EntityFrameworkCore;
27:
28: namespace ContosoUniversity.Data
29: {
30: public class SchoolContext : DbContext
31: {
32: public SchoolContext(DbContextOptions<SchoolContext> options) : base(options)
33: {
34: }
35:
36: public DbSet<Course> Courses { get; set; }
37: public DbSet<Enrollment> Enrollments { get; set; }
38: public DbSet<Student> Students { get; set; }
39:
40: protected override void OnModelCreating(ModelBuilder modelBuilder)
41: {
42: modelBuilder.Entity<Course>().ToTable("Course");
43: modelBuilder.Entity<Enrollment>().ToTable("Enrollment");
44: modelBuilder.Entity<Student>().ToTable("Student");
45: }
46: }
47: }
48: #endregion
49:
50: #elif BeforeInheritance
51: #region snippet_BeforeInheritance
52: using ContosoUniversity.Models;
53: using Microsoft.EntityFrameworkCore;
54:
55: namespace ContosoUniversity.Data
56: {
57: public class SchoolContext : DbContext
58: {
59: public SchoolContext(DbContextOptions<SchoolContext> options) : base(options)
60: {
61: }
62:
63: public DbSet<Course> Courses { get; set; }
64: public DbSet<Enrollment> Enrollments { get; set; }
65: public DbSet<Student> Students { get; set; }
66: public DbSet<Department> Departments { get; set; }
67: public DbSet<Instructor> Instructors { get; set; }
68: public DbSet<OfficeAssignment> OfficeAssignments { get; set; }
69: public DbSet<CourseAssignment> CourseAssignments { get; set; }
70:
71: protected override void OnModelCreating(ModelBuilder modelBuilder)
72: {
73: modelBuilder.Entity<Course>().ToTable("Course");
74: modelBuilder.Entity<Enrollment>().ToTable("Enrollment");
75: modelBuilder.Entity<Student>().ToTable("Student");
76: modelBuilder.Entity<Department>().ToTable("Department");
77: modelBuilder.Entity<Instructor>().ToTable("Instructor");
78: modelBuilder.Entity<OfficeAssignment>().ToTable("OfficeAssignment");
79: modelBuilder.Entity<CourseAssignment>().ToTable("CourseAssignment");
80:
81: modelBuilder.Entity<CourseAssignment>()
82: .HasKey(c => new { c.CourseID, c.InstructorID });
83: }
84: }
85: }
86: #endregion
87: #elif AfterInheritance
88: #region snippet_AfterInheritance
89: using ContosoUniversity.Models;
90: using Microsoft.EntityFrameworkCore;
91:
92: namespace ContosoUniversity.Data
93: {
94: public class SchoolContext : DbContext
95: {
96: public SchoolContext(DbContextOptions<SchoolContext> options) : base(options)
97: {
98: }
99:
100: public DbSet<Course> Courses { get; set; }
101: public DbSet<Enrollment> Enrollments { get; set; }
102: public DbSet<Student> Students { get; set; }
103: public DbSet<Department> Departments { get; set; }
104: public DbSet<Instructor> Instructors { get; set; }
105: public DbSet<OfficeAssignment> OfficeAssignments { get; set; }
106: public DbSet<CourseAssignment> CourseAssignments { get; set; }
107: public DbSet<Person> People { get; set; }
108:
109: protected override void OnModelCreating(ModelBuilder modelBuilder)
110: {
111: modelBuilder.Entity<Course>().ToTable("Course");
112: modelBuilder.Entity<Enrollment>().ToTable("Enrollment");
113: modelBuilder.Entity<Student>().ToTable("Student");
114: modelBuilder.Entity<Department>().ToTable("Department");
115: modelBuilder.Entity<Instructor>().ToTable("Instructor");
116: modelBuilder.Entity<OfficeAssignment>().ToTable("OfficeAssignment");
117: modelBuilder.Entity<CourseAssignment>().ToTable("CourseAssignment");
118: modelBuilder.Entity<Person>().ToTable("Person");
119:
120: modelBuilder.Entity<CourseAssignment>()
121: .HasKey(c => new { c.CourseID, c.InstructorID });
122: }
123: }
124: }
125: #endregion
126: #endif
This code adds the new entities and configures the CourseAssignment entity’s composite primary key.
Fluent API alternative to attributes
The code in the OnModelCreating method of the DbContext class uses the fluent API to configure EF behavior. The API is called “fluent” because it’s often used by stringing a series of method calls together into a single statement, as in this example from the EF Core documentation:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Blog>()
.Property(b => b.Url)
.IsRequired();
}In this tutorial, you’re using the fluent API only for database mapping that you can’t do with attributes. However, you can also use the fluent API to specify most of the formatting, validation, and mapping rules that you can do by using attributes. Some attributes such as MinimumLength can’t be applied with the fluent API. As mentioned previously, MinimumLength doesn’t change the schema, it only applies a client and server side validation rule.
Some developers prefer to use the fluent API exclusively so that they can keep their entity classes “clean.” You can mix attributes and fluent API if you want, and there are a few customizations that can only be done by using fluent API, but in general the recommended practice is to choose one of these two approaches and use that consistently as much as possible. If you do use both, note that wherever there is a conflict, Fluent API overrides attributes.
For more information about attributes vs. fluent API, see Methods of configuration.
Entity Diagram Showing Relationships
The following illustration shows the diagram that the Entity Framework Power Tools create for the completed School model.
Besides the one-to-many relationship lines (1 to *), you can see here the one-to-zero-or-one relationship line (1 to 0..1) between the Instructor and OfficeAssignment entities and the zero-or-one-to-many relationship line (0..1 to *) between the Instructor and Department entities.
Seed the Database with Test Data
Replace the code in the Data/DbInitializer.cs file with the following code in order to provide seed data for the new entities you’ve created.
[!code-csharpMain]
1: #define Final // or Intro
2:
3: #if Intro
4: #region snippet_Intro
5: using ContosoUniversity.Models;
6: using System;
7: using System.Linq;
8:
9: namespace ContosoUniversity.Data
10: {
11: public static class DbInitializer
12: {
13: public static void Initialize(SchoolContext context)
14: {
15: context.Database.EnsureCreated();
16:
17: // Look for any students.
18: if (context.Students.Any())
19: {
20: return; // DB has been seeded
21: }
22:
23: var students = new Student[]
24: {
25: new Student{FirstMidName="Carson",LastName="Alexander",EnrollmentDate=DateTime.Parse("2005-09-01")},
26: new Student{FirstMidName="Meredith",LastName="Alonso",EnrollmentDate=DateTime.Parse("2002-09-01")},
27: new Student{FirstMidName="Arturo",LastName="Anand",EnrollmentDate=DateTime.Parse("2003-09-01")},
28: new Student{FirstMidName="Gytis",LastName="Barzdukas",EnrollmentDate=DateTime.Parse("2002-09-01")},
29: new Student{FirstMidName="Yan",LastName="Li",EnrollmentDate=DateTime.Parse("2002-09-01")},
30: new Student{FirstMidName="Peggy",LastName="Justice",EnrollmentDate=DateTime.Parse("2001-09-01")},
31: new Student{FirstMidName="Laura",LastName="Norman",EnrollmentDate=DateTime.Parse("2003-09-01")},
32: new Student{FirstMidName="Nino",LastName="Olivetto",EnrollmentDate=DateTime.Parse("2005-09-01")}
33: };
34: foreach (Student s in students)
35: {
36: context.Students.Add(s);
37: }
38: context.SaveChanges();
39:
40: var courses = new Course[]
41: {
42: new Course{CourseID=1050,Title="Chemistry",Credits=3},
43: new Course{CourseID=4022,Title="Microeconomics",Credits=3},
44: new Course{CourseID=4041,Title="Macroeconomics",Credits=3},
45: new Course{CourseID=1045,Title="Calculus",Credits=4},
46: new Course{CourseID=3141,Title="Trigonometry",Credits=4},
47: new Course{CourseID=2021,Title="Composition",Credits=3},
48: new Course{CourseID=2042,Title="Literature",Credits=4}
49: };
50: foreach (Course c in courses)
51: {
52: context.Courses.Add(c);
53: }
54: context.SaveChanges();
55:
56: var enrollments = new Enrollment[]
57: {
58: new Enrollment{StudentID=1,CourseID=1050,Grade=Grade.A},
59: new Enrollment{StudentID=1,CourseID=4022,Grade=Grade.C},
60: new Enrollment{StudentID=1,CourseID=4041,Grade=Grade.B},
61: new Enrollment{StudentID=2,CourseID=1045,Grade=Grade.B},
62: new Enrollment{StudentID=2,CourseID=3141,Grade=Grade.F},
63: new Enrollment{StudentID=2,CourseID=2021,Grade=Grade.F},
64: new Enrollment{StudentID=3,CourseID=1050},
65: new Enrollment{StudentID=4,CourseID=1050},
66: new Enrollment{StudentID=4,CourseID=4022,Grade=Grade.F},
67: new Enrollment{StudentID=5,CourseID=4041,Grade=Grade.C},
68: new Enrollment{StudentID=6,CourseID=1045},
69: new Enrollment{StudentID=7,CourseID=3141,Grade=Grade.A},
70: };
71: foreach (Enrollment e in enrollments)
72: {
73: context.Enrollments.Add(e);
74: }
75: context.SaveChanges();
76: }
77: }
78: }
79: #endregion
80:
81: #elif Final
82: #region snippet_Final
83: using System;
84: using System.Linq;
85: using Microsoft.EntityFrameworkCore;
86: using Microsoft.Extensions.DependencyInjection;
87: using ContosoUniversity.Models;
88:
89: namespace ContosoUniversity.Data
90: {
91: public static class DbInitializer
92: {
93: public static void Initialize(SchoolContext context)
94: {
95: //context.Database.EnsureCreated();
96:
97: // Look for any students.
98: if (context.Students.Any())
99: {
100: return; // DB has been seeded
101: }
102:
103: var students = new Student[]
104: {
105: new Student { FirstMidName = "Carson", LastName = "Alexander",
106: EnrollmentDate = DateTime.Parse("2010-09-01") },
107: new Student { FirstMidName = "Meredith", LastName = "Alonso",
108: EnrollmentDate = DateTime.Parse("2012-09-01") },
109: new Student { FirstMidName = "Arturo", LastName = "Anand",
110: EnrollmentDate = DateTime.Parse("2013-09-01") },
111: new Student { FirstMidName = "Gytis", LastName = "Barzdukas",
112: EnrollmentDate = DateTime.Parse("2012-09-01") },
113: new Student { FirstMidName = "Yan", LastName = "Li",
114: EnrollmentDate = DateTime.Parse("2012-09-01") },
115: new Student { FirstMidName = "Peggy", LastName = "Justice",
116: EnrollmentDate = DateTime.Parse("2011-09-01") },
117: new Student { FirstMidName = "Laura", LastName = "Norman",
118: EnrollmentDate = DateTime.Parse("2013-09-01") },
119: new Student { FirstMidName = "Nino", LastName = "Olivetto",
120: EnrollmentDate = DateTime.Parse("2005-09-01") }
121: };
122:
123: foreach (Student s in students)
124: {
125: context.Students.Add(s);
126: }
127: context.SaveChanges();
128:
129: var instructors = new Instructor[]
130: {
131: new Instructor { FirstMidName = "Kim", LastName = "Abercrombie",
132: HireDate = DateTime.Parse("1995-03-11") },
133: new Instructor { FirstMidName = "Fadi", LastName = "Fakhouri",
134: HireDate = DateTime.Parse("2002-07-06") },
135: new Instructor { FirstMidName = "Roger", LastName = "Harui",
136: HireDate = DateTime.Parse("1998-07-01") },
137: new Instructor { FirstMidName = "Candace", LastName = "Kapoor",
138: HireDate = DateTime.Parse("2001-01-15") },
139: new Instructor { FirstMidName = "Roger", LastName = "Zheng",
140: HireDate = DateTime.Parse("2004-02-12") }
141: };
142:
143: foreach (Instructor i in instructors)
144: {
145: context.Instructors.Add(i);
146: }
147: context.SaveChanges();
148:
149: var departments = new Department[]
150: {
151: new Department { Name = "English", Budget = 350000,
152: StartDate = DateTime.Parse("2007-09-01"),
153: InstructorID = instructors.Single( i => i.LastName == "Abercrombie").ID },
154: new Department { Name = "Mathematics", Budget = 100000,
155: StartDate = DateTime.Parse("2007-09-01"),
156: InstructorID = instructors.Single( i => i.LastName == "Fakhouri").ID },
157: new Department { Name = "Engineering", Budget = 350000,
158: StartDate = DateTime.Parse("2007-09-01"),
159: InstructorID = instructors.Single( i => i.LastName == "Harui").ID },
160: new Department { Name = "Economics", Budget = 100000,
161: StartDate = DateTime.Parse("2007-09-01"),
162: InstructorID = instructors.Single( i => i.LastName == "Kapoor").ID }
163: };
164:
165: foreach (Department d in departments)
166: {
167: context.Departments.Add(d);
168: }
169: context.SaveChanges();
170:
171: var courses = new Course[]
172: {
173: new Course {CourseID = 1050, Title = "Chemistry", Credits = 3,
174: DepartmentID = departments.Single( s => s.Name == "Engineering").DepartmentID
175: },
176: new Course {CourseID = 4022, Title = "Microeconomics", Credits = 3,
177: DepartmentID = departments.Single( s => s.Name == "Economics").DepartmentID
178: },
179: new Course {CourseID = 4041, Title = "Macroeconomics", Credits = 3,
180: DepartmentID = departments.Single( s => s.Name == "Economics").DepartmentID
181: },
182: new Course {CourseID = 1045, Title = "Calculus", Credits = 4,
183: DepartmentID = departments.Single( s => s.Name == "Mathematics").DepartmentID
184: },
185: new Course {CourseID = 3141, Title = "Trigonometry", Credits = 4,
186: DepartmentID = departments.Single( s => s.Name == "Mathematics").DepartmentID
187: },
188: new Course {CourseID = 2021, Title = "Composition", Credits = 3,
189: DepartmentID = departments.Single( s => s.Name == "English").DepartmentID
190: },
191: new Course {CourseID = 2042, Title = "Literature", Credits = 4,
192: DepartmentID = departments.Single( s => s.Name == "English").DepartmentID
193: },
194: };
195:
196: foreach (Course c in courses)
197: {
198: context.Courses.Add(c);
199: }
200: context.SaveChanges();
201:
202: var officeAssignments = new OfficeAssignment[]
203: {
204: new OfficeAssignment {
205: InstructorID = instructors.Single( i => i.LastName == "Fakhouri").ID,
206: Location = "Smith 17" },
207: new OfficeAssignment {
208: InstructorID = instructors.Single( i => i.LastName == "Harui").ID,
209: Location = "Gowan 27" },
210: new OfficeAssignment {
211: InstructorID = instructors.Single( i => i.LastName == "Kapoor").ID,
212: Location = "Thompson 304" },
213: };
214:
215: foreach (OfficeAssignment o in officeAssignments)
216: {
217: context.OfficeAssignments.Add(o);
218: }
219: context.SaveChanges();
220:
221: var courseInstructors = new CourseAssignment[]
222: {
223: new CourseAssignment {
224: CourseID = courses.Single(c => c.Title == "Chemistry" ).CourseID,
225: InstructorID = instructors.Single(i => i.LastName == "Kapoor").ID
226: },
227: new CourseAssignment {
228: CourseID = courses.Single(c => c.Title == "Chemistry" ).CourseID,
229: InstructorID = instructors.Single(i => i.LastName == "Harui").ID
230: },
231: new CourseAssignment {
232: CourseID = courses.Single(c => c.Title == "Microeconomics" ).CourseID,
233: InstructorID = instructors.Single(i => i.LastName == "Zheng").ID
234: },
235: new CourseAssignment {
236: CourseID = courses.Single(c => c.Title == "Macroeconomics" ).CourseID,
237: InstructorID = instructors.Single(i => i.LastName == "Zheng").ID
238: },
239: new CourseAssignment {
240: CourseID = courses.Single(c => c.Title == "Calculus" ).CourseID,
241: InstructorID = instructors.Single(i => i.LastName == "Fakhouri").ID
242: },
243: new CourseAssignment {
244: CourseID = courses.Single(c => c.Title == "Trigonometry" ).CourseID,
245: InstructorID = instructors.Single(i => i.LastName == "Harui").ID
246: },
247: new CourseAssignment {
248: CourseID = courses.Single(c => c.Title == "Composition" ).CourseID,
249: InstructorID = instructors.Single(i => i.LastName == "Abercrombie").ID
250: },
251: new CourseAssignment {
252: CourseID = courses.Single(c => c.Title == "Literature" ).CourseID,
253: InstructorID = instructors.Single(i => i.LastName == "Abercrombie").ID
254: },
255: };
256:
257: foreach (CourseAssignment ci in courseInstructors)
258: {
259: context.CourseAssignments.Add(ci);
260: }
261: context.SaveChanges();
262:
263: var enrollments = new Enrollment[]
264: {
265: new Enrollment {
266: StudentID = students.Single(s => s.LastName == "Alexander").ID,
267: CourseID = courses.Single(c => c.Title == "Chemistry" ).CourseID,
268: Grade = Grade.A
269: },
270: new Enrollment {
271: StudentID = students.Single(s => s.LastName == "Alexander").ID,
272: CourseID = courses.Single(c => c.Title == "Microeconomics" ).CourseID,
273: Grade = Grade.C
274: },
275: new Enrollment {
276: StudentID = students.Single(s => s.LastName == "Alexander").ID,
277: CourseID = courses.Single(c => c.Title == "Macroeconomics" ).CourseID,
278: Grade = Grade.B
279: },
280: new Enrollment {
281: StudentID = students.Single(s => s.LastName == "Alonso").ID,
282: CourseID = courses.Single(c => c.Title == "Calculus" ).CourseID,
283: Grade = Grade.B
284: },
285: new Enrollment {
286: StudentID = students.Single(s => s.LastName == "Alonso").ID,
287: CourseID = courses.Single(c => c.Title == "Trigonometry" ).CourseID,
288: Grade = Grade.B
289: },
290: new Enrollment {
291: StudentID = students.Single(s => s.LastName == "Alonso").ID,
292: CourseID = courses.Single(c => c.Title == "Composition" ).CourseID,
293: Grade = Grade.B
294: },
295: new Enrollment {
296: StudentID = students.Single(s => s.LastName == "Anand").ID,
297: CourseID = courses.Single(c => c.Title == "Chemistry" ).CourseID
298: },
299: new Enrollment {
300: StudentID = students.Single(s => s.LastName == "Anand").ID,
301: CourseID = courses.Single(c => c.Title == "Microeconomics").CourseID,
302: Grade = Grade.B
303: },
304: new Enrollment {
305: StudentID = students.Single(s => s.LastName == "Barzdukas").ID,
306: CourseID = courses.Single(c => c.Title == "Chemistry").CourseID,
307: Grade = Grade.B
308: },
309: new Enrollment {
310: StudentID = students.Single(s => s.LastName == "Li").ID,
311: CourseID = courses.Single(c => c.Title == "Composition").CourseID,
312: Grade = Grade.B
313: },
314: new Enrollment {
315: StudentID = students.Single(s => s.LastName == "Justice").ID,
316: CourseID = courses.Single(c => c.Title == "Literature").CourseID,
317: Grade = Grade.B
318: }
319: };
320:
321: foreach (Enrollment e in enrollments)
322: {
323: var enrollmentInDataBase = context.Enrollments.Where(
324: s =>
325: s.Student.ID == e.StudentID &&
326: s.Course.CourseID == e.CourseID).SingleOrDefault();
327: if (enrollmentInDataBase == null)
328: {
329: context.Enrollments.Add(e);
330: }
331: }
332: context.SaveChanges();
333: }
334: }
335: }
336: #endregion
337: #endif
As you saw in the first tutorial, most of this code simply creates new entity objects and loads sample data into properties as required for testing. Notice how the many-to-many relationships are handled: the code creates relationships by creating entities in the Enrollments and CourseAssignment join entity sets.
Add a migration
Save your changes and build the project. Then open the command window in the project folder and enter the migrations add command (don’t do the update-database command yet):
dotnet ef migrations add ComplexDataModelYou get a warning about possible data loss.
An operation was scaffolded that may result in the loss of data. Please review the migration for accuracy.
Done. To undo this action, use 'ef migrations remove'If you tried to run the database update command at this point (don’t do it yet), you would get the following error:
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’.
Sometimes when you execute migrations with existing data, you need to insert stub data into the database to satisfy foreign key constraints. The generated code in the Up method adds a non-nullable DepartmentID foreign key to the Course table. If there are already rows in the Course table when the code runs, the AddColumn operation fails because SQL Server doesn’t know what value to put in the column that can’t be null. For this tutorial you’ll run the migration on a new database, but in a production application you’d have to make the migration handle existing data, so the following directions show an example of how to do that.
To make this migration work with existing data you have to change the code to give the new column a default value, and create a stub department named “Temp” to act as the default department. As a result, existing Course rows will all be related to the “Temp” department after the Up method runs.
Open the *{timestamp}_ComplexDataModel.cs* file.
Comment out the line of code that adds the DepartmentID column to the Course table.
[!code-csharpMain]
1: using System;
2: using System.Collections.Generic;
3: using Microsoft.EntityFrameworkCore.Migrations;
4: using Microsoft.EntityFrameworkCore.Metadata;
5:6: namespace ContosoUniversity.Migrations
7: {8: public partial class ComplexDataModel : Migration
9: {10: protected override void Up(MigrationBuilder migrationBuilder)
11: {12: migrationBuilder.AlterColumn<string>(
13: name: "LastName",
14: table: "Student",
15: maxLength: 50,16: nullable: false,
17: oldClrType: typeof(string),
18: oldMaxLength: 50,19: oldNullable: true);
20:21: migrationBuilder.AlterColumn<string>(
22: name: "FirstName",
23: table: "Student",
24: maxLength: 50,25: nullable: false,
26: oldClrType: typeof(string),
27: oldMaxLength: 50,28: oldNullable: true);
29:30: #region snippet_CommentOut
31: migrationBuilder.AlterColumn<string>(
32: name: "Title",
33: table: "Course",
34: maxLength: 50,35: nullable: true,
36: oldClrType: typeof(string),
37: oldNullable: true);
38:39: //migrationBuilder.AddColumn<int>(
40: // name: "DepartmentID",
41: // table: "Course",
42: // nullable: false,
43: // defaultValue: 0);
44: #endregion
45:46: migrationBuilder.CreateTable(47: name: "Instructor",
48: columns: table => new
49: {50: ID = table.Column<int>(nullable: false)
51: .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn),
52: FirstName = table.Column<string>(maxLength: 50, nullable: false),
53: HireDate = table.Column<DateTime>(nullable: false),
54: LastName = table.Column<string>(maxLength: 50, nullable: false)
55: },56: constraints: table =>57: {58: table.PrimaryKey("PK_Instructor", x => x.ID);
59: });60:61: migrationBuilder.CreateTable(62: name: "CourseAssignment",
63: columns: table => new
64: {65: CourseID = table.Column<int>(nullable: false),
66: InstructorID = table.Column<int>(nullable: false)
67: },68: constraints: table =>69: {70: table.PrimaryKey("PK_CourseAssignment", x => new { x.CourseID, x.InstructorID });
71: table.ForeignKey(72: name: "FK_CourseAssignment_Course_CourseID",
73: column: x => x.CourseID,74: principalTable: "Course",
75: principalColumn: "CourseID",
76: onDelete: ReferentialAction.Cascade);77: table.ForeignKey(78: name: "FK_CourseAssignment_Instructor_InstructorID",
79: column: x => x.InstructorID,80: principalTable: "Instructor",
81: principalColumn: "ID",
82: onDelete: ReferentialAction.Cascade);83: });84:85: #region snippet_CreateDefaultValue
86: migrationBuilder.CreateTable(87: name: "Department",
88: columns: table => new
89: {90: DepartmentID = table.Column<int>(nullable: false)
91: .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn),
92: Budget = table.Column<decimal>(type: "money", nullable: false),
93: InstructorID = table.Column<int>(nullable: true),
94: Name = table.Column<string>(maxLength: 50, nullable: true),
95: StartDate = table.Column<DateTime>(nullable: false)
96: },97: constraints: table =>98: {99: table.PrimaryKey("PK_Department", x => x.DepartmentID);
100: table.ForeignKey(101: name: "FK_Department_Instructor_InstructorID",
102: column: x => x.InstructorID,103: principalTable: "Instructor",
104: principalColumn: "ID",
105: onDelete: ReferentialAction.Restrict);106: });107:108: migrationBuilder.Sql("INSERT INTO dbo.Department (Name, Budget, StartDate) VALUES ('Temp', 0.00, GETDATE())");
109: // Default value for FK points to department created above, with
110: // defaultValue changed to 1 in following AddColumn statement.
111:112: migrationBuilder.AddColumn<int>(
113: name: "DepartmentID",
114: table: "Course",
115: nullable: false,
116: defaultValue: 1);117: #endregion
118:119: migrationBuilder.CreateTable(120: name: "OfficeAssignment",
121: columns: table => new
122: {123: InstructorID = table.Column<int>(nullable: false),
124: Location = table.Column<string>(maxLength: 50, nullable: true)
125: },126: constraints: table =>127: {128: table.PrimaryKey("PK_OfficeAssignment", x => x.InstructorID);
129: table.ForeignKey(130: name: "FK_OfficeAssignment_Instructor_InstructorID",
131: column: x => x.InstructorID,132: principalTable: "Instructor",
133: principalColumn: "ID",
134: onDelete: ReferentialAction.Cascade);135: });136:137: migrationBuilder.CreateIndex(138: name: "IX_Course_DepartmentID",
139: table: "Course",
140: column: "DepartmentID");
141:142: migrationBuilder.CreateIndex(143: name: "IX_CourseAssignment_InstructorID",
144: table: "CourseAssignment",
145: column: "InstructorID");
146:147: migrationBuilder.CreateIndex(148: name: "IX_Department_InstructorID",
149: table: "Department",
150: column: "InstructorID");
151:152: migrationBuilder.AddForeignKey(153: name: "FK_Course_Department_DepartmentID",
154: table: "Course",
155: column: "DepartmentID",
156: principalTable: "Department",
157: principalColumn: "DepartmentID",
158: onDelete: ReferentialAction.Cascade);159: }160:161: protected override void Down(MigrationBuilder migrationBuilder)
162: {163: migrationBuilder.DropForeignKey(164: name: "FK_Course_Department_DepartmentID",
165: table: "Course");
166:167: migrationBuilder.DropTable(168: name: "CourseAssignment");
169:170: migrationBuilder.DropTable(171: name: "Department");
172:173: migrationBuilder.DropTable(174: name: "OfficeAssignment");
175:176: migrationBuilder.DropTable(177: name: "Instructor");
178:179: migrationBuilder.DropIndex(180: name: "IX_Course_DepartmentID",
181: table: "Course");
182:183: migrationBuilder.DropColumn(184: name: "DepartmentID",
185: table: "Course");
186:187: migrationBuilder.AlterColumn<string>(
188: name: "LastName",
189: table: "Student",
190: maxLength: 50,191: nullable: true,
192: oldClrType: typeof(string),
193: oldMaxLength: 50);194:195: migrationBuilder.AlterColumn<string>(
196: name: "FirstName",
197: table: "Student",
198: maxLength: 50,199: nullable: true,
200: oldClrType: typeof(string),
201: oldMaxLength: 50);202:203: migrationBuilder.AlterColumn<string>(
204: name: "Title",
205: table: "Course",
206: nullable: true,
207: oldClrType: typeof(string),
208: oldMaxLength: 50,209: oldNullable: true);
210: }211: }212: }Add the following highlighted code after the code that creates the Department table:
[!code-csharpMain]
1: using System;
2: using System.Collections.Generic;
3: using Microsoft.EntityFrameworkCore.Migrations;
4: using Microsoft.EntityFrameworkCore.Metadata;
5:6: namespace ContosoUniversity.Migrations
7: {8: public partial class ComplexDataModel : Migration
9: {10: protected override void Up(MigrationBuilder migrationBuilder)
11: {12: migrationBuilder.AlterColumn<string>(
13: name: "LastName",
14: table: "Student",
15: maxLength: 50,16: nullable: false,
17: oldClrType: typeof(string),
18: oldMaxLength: 50,19: oldNullable: true);
20:21: migrationBuilder.AlterColumn<string>(
22: name: "FirstName",
23: table: "Student",
24: maxLength: 50,25: nullable: false,
26: oldClrType: typeof(string),
27: oldMaxLength: 50,28: oldNullable: true);
29:30: #region snippet_CommentOut
31: migrationBuilder.AlterColumn<string>(
32: name: "Title",
33: table: "Course",
34: maxLength: 50,35: nullable: true,
36: oldClrType: typeof(string),
37: oldNullable: true);
38:39: //migrationBuilder.AddColumn<int>(
40: // name: "DepartmentID",
41: // table: "Course",
42: // nullable: false,
43: // defaultValue: 0);
44: #endregion
45:46: migrationBuilder.CreateTable(47: name: "Instructor",
48: columns: table => new
49: {50: ID = table.Column<int>(nullable: false)
51: .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn),
52: FirstName = table.Column<string>(maxLength: 50, nullable: false),
53: HireDate = table.Column<DateTime>(nullable: false),
54: LastName = table.Column<string>(maxLength: 50, nullable: false)
55: },56: constraints: table =>57: {58: table.PrimaryKey("PK_Instructor", x => x.ID);
59: });60:61: migrationBuilder.CreateTable(62: name: "CourseAssignment",
63: columns: table => new
64: {65: CourseID = table.Column<int>(nullable: false),
66: InstructorID = table.Column<int>(nullable: false)
67: },68: constraints: table =>69: {70: table.PrimaryKey("PK_CourseAssignment", x => new { x.CourseID, x.InstructorID });
71: table.ForeignKey(72: name: "FK_CourseAssignment_Course_CourseID",
73: column: x => x.CourseID,74: principalTable: "Course",
75: principalColumn: "CourseID",
76: onDelete: ReferentialAction.Cascade);77: table.ForeignKey(78: name: "FK_CourseAssignment_Instructor_InstructorID",
79: column: x => x.InstructorID,80: principalTable: "Instructor",
81: principalColumn: "ID",
82: onDelete: ReferentialAction.Cascade);83: });84:85: #region snippet_CreateDefaultValue
86: migrationBuilder.CreateTable(87: name: "Department",
88: columns: table => new
89: {90: DepartmentID = table.Column<int>(nullable: false)
91: .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn),
92: Budget = table.Column<decimal>(type: "money", nullable: false),
93: InstructorID = table.Column<int>(nullable: true),
94: Name = table.Column<string>(maxLength: 50, nullable: true),
95: StartDate = table.Column<DateTime>(nullable: false)
96: },97: constraints: table =>98: {99: table.PrimaryKey("PK_Department", x => x.DepartmentID);
100: table.ForeignKey(101: name: "FK_Department_Instructor_InstructorID",
102: column: x => x.InstructorID,103: principalTable: "Instructor",
104: principalColumn: "ID",
105: onDelete: ReferentialAction.Restrict);106: });107:108: migrationBuilder.Sql("INSERT INTO dbo.Department (Name, Budget, StartDate) VALUES ('Temp', 0.00, GETDATE())");
109: // Default value for FK points to department created above, with
110: // defaultValue changed to 1 in following AddColumn statement.
111:112: migrationBuilder.AddColumn<int>(
113: name: "DepartmentID",
114: table: "Course",
115: nullable: false,
116: defaultValue: 1);117: #endregion
118:119: migrationBuilder.CreateTable(120: name: "OfficeAssignment",
121: columns: table => new
122: {123: InstructorID = table.Column<int>(nullable: false),
124: Location = table.Column<string>(maxLength: 50, nullable: true)
125: },126: constraints: table =>127: {128: table.PrimaryKey("PK_OfficeAssignment", x => x.InstructorID);
129: table.ForeignKey(130: name: "FK_OfficeAssignment_Instructor_InstructorID",
131: column: x => x.InstructorID,132: principalTable: "Instructor",
133: principalColumn: "ID",
134: onDelete: ReferentialAction.Cascade);135: });136:137: migrationBuilder.CreateIndex(138: name: "IX_Course_DepartmentID",
139: table: "Course",
140: column: "DepartmentID");
141:142: migrationBuilder.CreateIndex(143: name: "IX_CourseAssignment_InstructorID",
144: table: "CourseAssignment",
145: column: "InstructorID");
146:147: migrationBuilder.CreateIndex(148: name: "IX_Department_InstructorID",
149: table: "Department",
150: column: "InstructorID");
151:152: migrationBuilder.AddForeignKey(153: name: "FK_Course_Department_DepartmentID",
154: table: "Course",
155: column: "DepartmentID",
156: principalTable: "Department",
157: principalColumn: "DepartmentID",
158: onDelete: ReferentialAction.Cascade);159: }160:161: protected override void Down(MigrationBuilder migrationBuilder)
162: {163: migrationBuilder.DropForeignKey(164: name: "FK_Course_Department_DepartmentID",
165: table: "Course");
166:167: migrationBuilder.DropTable(168: name: "CourseAssignment");
169:170: migrationBuilder.DropTable(171: name: "Department");
172:173: migrationBuilder.DropTable(174: name: "OfficeAssignment");
175:176: migrationBuilder.DropTable(177: name: "Instructor");
178:179: migrationBuilder.DropIndex(180: name: "IX_Course_DepartmentID",
181: table: "Course");
182:183: migrationBuilder.DropColumn(184: name: "DepartmentID",
185: table: "Course");
186:187: migrationBuilder.AlterColumn<string>(
188: name: "LastName",
189: table: "Student",
190: maxLength: 50,191: nullable: true,
192: oldClrType: typeof(string),
193: oldMaxLength: 50);194:195: migrationBuilder.AlterColumn<string>(
196: name: "FirstName",
197: table: "Student",
198: maxLength: 50,199: nullable: true,
200: oldClrType: typeof(string),
201: oldMaxLength: 50);202:203: migrationBuilder.AlterColumn<string>(
204: name: "Title",
205: table: "Course",
206: nullable: true,
207: oldClrType: typeof(string),
208: oldMaxLength: 50,209: oldNullable: true);
210: }211: }212: }
In a production application, you would write code or scripts to add Department rows and relate Course rows to the new Department rows. You would then no longer need the “Temp” department or the default value on the Course.DepartmentID column.
Save your changes and build the project.
Change the connection string and update the database
You now have new code in the DbInitializer class that adds seed data for the new entities to an empty database. To make EF create a new empty database, change the name of the database in the connection string in appsettings.json to ContosoUniversity3 or some other name that you haven’t used on the computer you’re using.
{
"ConnectionStrings": {
"DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=ContosoUniversity3;Trusted_Connection=True;MultipleActiveResultSets=true"
},Save your change to appsettings.json.
[!NOTE] As an alternative to changing the database name, you can delete the database. Use SQL Server Object Explorer (SSOX) or the
database dropCLI command:dotnet ef database drop
After you have changed the database name or deleted the database, run the database update command in the command window to execute the migrations.
dotnet ef database updateRun the app to cause the DbInitializer.Initialize method to run and populate the new database.
Open the database in SSOX as you did earlier, and expand the Tables node to see that all of the tables have been created. (If you still have SSOX open from the earlier time, click the Refresh button.)
Run the app to trigger the initializer code that seeds the database.
Right-click the CourseAssignment table and select View Data to verify that it has data in it.
Summary
You now have a more complex data model and corresponding database. In the following tutorial, you’ll learn more about how to access related data.
)
|
|