1 6_Creating a More Complex Data Model for an ASP.NET MVC Application Mon Jul 06, 2015 8:09 am
Admin
Admin
The Contoso University sample web application demonstrates how to create ASP.NET MVC 5 applications using the Entity Framework 6 Code First and Visual Studio 2013. 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. You'll see two ways to customize the data model: by adding attributes to entity classes and by adding code to the database context class.
When you're finished, the entity classes will make up the completed data model that's shown in the following illustration:
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
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
In Models\Student.cs, add a
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
namespace ContosoUniversity.Models
{
public class Student
{
public int ID { get; set; }
public string LastName { get; set; }
public string FirstMidName { get; set; }
[DataType(DataType.Date)]
[DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
public DateTime EnrollmentDate { get; set; }
public virtual ICollection Enrollments { get; set; }
}
}
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
The
[DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
The
You can use the DisplayFormat attribute by itself, but it's generally a good idea to use the DataType attribute also. The
If you use the
For more information about how to handle other date formats in MVC, go to MVC 5 Introduction: Examining the Edit Methods and Edit View and search in the page for "internationalization".
Run the Student Index page again and notice that times are no longer displayed for the enrollment dates. The same will be true for any view that uses the
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
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
namespace ContosoUniversity.Models
{
public class Student
{
public int ID { get; set; }
[StringLength(50)]
public string LastName { get; set; }
[StringLength(50, ErrorMessage = "First name cannot be longer than 50 characters.")]
public string FirstMidName { get; set; }
[DataType(DataType.Date)]
[DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
public DateTime EnrollmentDate { get; set; }
public virtual ICollection Enrollments { get; set; }
}
}
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 similar functionality to the StringLength attribute but doesn't provide client side validation.
Run the application and click the Students tab. You get the following error:
The model backing the 'SchoolContext' context has changed since the database was created. Consider using Code First Migrations to update the database (http://go.microsoft.com/fwlink/?LinkId=238269).
The database model has changed in a way that requires a change in the database schema, and Entity Framework detected that. You'll use migrations to update the schema without losing any data that you added to the database by using the UI. If you changed data that was created by the
In the Package Manager Console (PMC), enter the following commands:
a
The
_MaxLengthOnNames.cs . This file contains code in the
The timestamp prepended to the migrations file name is used by Entity Framework to order the migrations. You can create multiple migrations before running the
Run the Create page, and enter either name longer than 50 characters. When you click Create, client side validation shows an error message.
You can also use attributes to control how your classes and properties are mapped to the database. Suppose you had used the name
The
In the Student.cs file, add a
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace ContosoUniversity.Models
{
public class Student
{
public int ID { get; set; }
[StringLength(50)]
public string LastName { get; set; }
[StringLength(50, ErrorMessage = "First name cannot be longer than 50 characters.")]
[Column("FirstName")]
public string FirstMidName { get; set; }
[DataType(DataType.Date)]
[DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
public DateTime EnrollmentDate { get; set; }
public virtual ICollection Enrollments { get; set; }
}
}
The addition of the Column attribute changes the model backing the SchoolContext, so it won't match the database. Enter the following commands in the PMC to create another migration:
In Server Explorer, open the Student table designer by double-clicking the Student table.
The following image shows the original column name as it was before you applied the first two migrations. In addition to the column name changing from
You can also make database mapping changes using the Fluent API, as you'll see later in this tutorial.
Note If you try to compile before you finish creating all of the entity classes in the following sections, you might get compiler errors.
In Models\Student.cs, replace the code you added earlier with the following code. The changes are highlighted.
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace ContosoUniversity.Models
{
public class Student
{
public int ID { get; set; }
[Required]
[StringLength(50)]
[Display(Name = "Last Name")]
public string LastName { get; set; }
[Required]
[StringLength(50, ErrorMessage = "First name cannot be longer than 50 characters.")]
[Column("FirstName")]
[Display(Name = "First Name")]
public string FirstMidName { get; set; }
[DataType(DataType.Date)]
[DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
[Display(Name = "Enrollment Date")]
public DateTime EnrollmentDate { get; set; }
[Display(Name = "Full Name")]
public string FullName
{
get
{
return LastName + ", " + FirstMidName;
}
}
public virtual ICollection Enrollments { get; set; }
}
}
The Required attribute makes the name properties required fields. The
[Display(Name = "Last Name")]
[StringLength(50, MinimumLength=1)]
public string LastName { get; set; }
The
Create Models\Instructor.cs, replacing the template code with the following code:
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace ContosoUniversity.Models
{
public class Instructor
{
public int ID { get; set; }
[Required]
[Display(Name = "Last Name")]
[StringLength(50)]
public string LastName { get; set; }
[Required]
[Column("FirstName")]
[Display(Name = "First Name")]
[StringLength(50)]
public string FirstMidName { get; set; }
[DataType(DataType.Date)]
[DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
[Display(Name = "Hire Date")]
public DateTime HireDate { get; set; }
[Display(Name = "Full Name")]
public string FullName
{
get { return LastName + ", " + FirstMidName; }
}
public virtual ICollection Courses { get; set; }
public virtual OfficeAssignment OfficeAssignment { get; set; }
}
}
Notice that several properties are the same in the
You can put multiple attributes on one line, so you could also write the instructor class as follows:
public class Instructor
{
public int ID { get; set; }
[Display(Name = "Last Name"),StringLength(50, MinimumLength=1)]
public string LastName { get; set; }
[Column("FirstName"),Display(Name = "First Name"),StringLength(50, MinimumLength=1)]
public string FirstMidName { get; set; }
[DataType(DataType.Date),Display(Name = "Hire Date"),DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
public DateTime HireDate { get; set; }
[Display(Name = "Full Name")]
public string FullName
{
get { return LastName + ", " + FirstMidName; }
}
public virtual ICollection Courses { get; set; }
public virtual OfficeAssignment OfficeAssignment { get; set; }
}
The
Interface. For example IList qualifies but not IEnumerable because
An instructor can teach any number of courses, so
public virtual ICollection Courses { get; set; }
Our business rules state an instructor can only have at most one office, so
public virtual OfficeAssignment OfficeAssignment { get; set; }
Create Models\OfficeAssignment.cs with the following code:
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace ContosoUniversity.Models
{
public class OfficeAssignment
{
[Key]
[ForeignKey("Instructor")]
public int InstructorID { get; set; }
[StringLength(50)]
[Display(Name = "Office Location")]
public string Location { get; set; }
public virtual Instructor Instructor { get; set; }
}
}
Build the project, which saves your changes and verifies you haven't made any copy and paste errors the compiler can catch.
There's a one-to-zero-or-one relationship between the
[Key]
[ForeignKey("Instructor")]
public int InstructorID { get; set; }
You can also use the
When there is a one-to-zero-or-one relationship or a one-to-one relationship between two entities (such as between
Unable to determine the principal end of an association between the types 'ContosoUniversity.Models.OfficeAssignment' and 'ContosoUniversity.Models.Instructor'. The principal end of this association must be explicitly configured using either the relationship fluent API or data annotations.
Later in the tutorial you'll see how to configure this relationship with the fluent API.
The
You could put a
In Models\Course.cs, replace the code you added earlier with the following code:
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace ContosoUniversity.Models
{
public class Course
{
[DatabaseGenerated(DatabaseGeneratedOption.None)]
[Display(Name = "Number")]
public int CourseID { get; set; }
[StringLength(50, MinimumLength = 3)]
public string Title { get; set; }
[Range(0, 5)]
public int Credits { get; set; }
public int DepartmentID { get; set; }
public virtual Department Department { get; set; }
public virtual ICollection Enrollments { get; set; }
public virtual ICollection Instructors { get; set; }
}
}
The course entity has a foreign key property
The DatabaseGenerated attribute with the None parameter on the
[DatabaseGenerated(DatabaseGeneratedOption.None)]
[Display(Name = "Number")]
public int CourseID { get; set; }
By default, the Entity Framework assumes that primary key values are generated by the database. That's what you want in most scenarios. However, for
The foreign key properties and navigation properties in the
Create Models\Department.cs with the following code:
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace ContosoUniversity.Models
{
public class Department
{
public int DepartmentID { get; set; }
[StringLength(50, MinimumLength=3)]
public string Name { get; set; }
[DataType(DataType.Currency)]
[Column(TypeName = "money")]
public decimal Budget { get; set; }
[DataType(DataType.Date)]
[DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
[Display(Name = "Start Date")]
public DateTime StartDate { get; set; }
public int? InstructorID { get; set; }
public virtual Instructor Administrator { get; set; }
public virtual ICollection Courses { get; set; }
}
}
Earlier you used the Column attribute to change column name mapping. In the code for the
[Column(TypeName="money")]
public decimal Budget { get; set; }
Column mapping is generally not required, because the Entity Framework usually chooses the appropriate SQL Server data type based on the CLR type that you define for the property. The CLR
The foreign key and navigation properties reflect the following relationships:
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
modelBuilder.Entity().HasRequired(d => d.Administrator).WithMany().WillCascadeOnDelete(false);
In Models\Enrollment.cs, replace the code you added earlier with the following code
using System.ComponentModel.DataAnnotations;
namespace ContosoUniversity.Models
{
public enum Grade
{
A, B, C, D, F
}
public class Enrollment
{
public int EnrollmentID { get; set; }
public int CourseID { get; set; }
public int StudentID { get; set; }
[DisplayFormat(NullDisplayText = "No grade")]
public Grade? Grade { get; set; }
public virtual Course Course { get; set; }
public virtual Student Student { get; set; }
}
}
The foreign key properties and navigation properties reflect the following relationships:
There's a many-to-many relationship between the
The following illustration shows what these relationships look like in an entity diagram. (This diagram was generated using the Entity Framework Power Tools; 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
A join table is required in the database, however, as shown in the following database diagram:
The Entity Framework automatically creates the
The following illustration shows the diagram that the Entity Framework Power Tools create for the completed School model.
Besides the many-to-many relationship lines (* to *) and 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
Next you'll add the new entities to the
modelBuilder.Entity()
.HasMany(c => c.Instructors).WithMany(i => i.Courses)
.Map(t => t.MapLeftKey("CourseID")
.MapRightKey("InstructorID")
.ToTable("CourseInstructor"));
In this tutorial you'll use 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
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.
To add the new entities to the data model and perform database mapping that you didn't do by using attributes, replace the code in DAL\SchoolContext.cs with the following code:
using ContosoUniversity.Models;
using System.Data.Entity;
using System.Data.Entity.ModelConfiguration.Conventions;
namespace ContosoUniversity.DAL
{
public class SchoolContext : DbContext
{
public DbSet Courses { get; set; }
public DbSet Departments { get; set; }
public DbSet Enrollments { get; set; }
public DbSet Instructors { get; set; }
public DbSet Students { get; set; }
public DbSet OfficeAssignments { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Conventions.Remove();
modelBuilder.Entity()
.HasMany(c => c.Instructors).WithMany(i => i.Courses)
.Map(t => t.MapLeftKey("CourseID")
.MapRightKey("InstructorID")
.ToTable("CourseInstructor"));
}
}
}
The new statement in the OnModelCreating method configures the many-to-many join table:
The following code provides an example of how you could have used fluent API instead of attributes to specify the relationship between the
modelBuilder.Entity()
.HasOptional(p => p.OfficeAssignment).WithRequired(p => p.Instructor);
For information about what "fluent API" statements are doing behind the scenes, see the Fluent API blog post.
Replace the code in the Migrations\Configuration.cs file with the following code in order to provide seed data for the new entities you've created.
namespace ContosoUniversity.Migrations
{
using ContosoUniversity.Models;
using ContosoUniversity.DAL;
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.Entity.Migrations;
using System.Linq;
internal sealed class Configuration : DbMigrationsConfiguration
{
public Configuration()
{
AutomaticMigrationsEnabled = false;
}
protected override void Seed(SchoolContext context)
{
var students = new List
{
new Student { FirstMidName = "Carson", LastName = "Alexander",
EnrollmentDate = DateTime.Parse("2010-09-01") },
new Student { FirstMidName = "Meredith", LastName = "Alonso",
EnrollmentDate = DateTime.Parse("2012-09-01") },
new Student { FirstMidName = "Arturo", LastName = "Anand",
EnrollmentDate = DateTime.Parse("2013-09-01") },
new Student { FirstMidName = "Gytis", LastName = "Barzdukas",
EnrollmentDate = DateTime.Parse("2012-09-01") },
new Student { FirstMidName = "Yan", LastName = "Li",
EnrollmentDate = DateTime.Parse("2012-09-01") },
new Student { FirstMidName = "Peggy", LastName = "Justice",
EnrollmentDate = DateTime.Parse("2011-09-01") },
new Student { FirstMidName = "Laura", LastName = "Norman",
EnrollmentDate = DateTime.Parse("2013-09-01") },
new Student { FirstMidName = "Nino", LastName = "Olivetto",
EnrollmentDate = DateTime.Parse("2005-09-01") }
};
students.ForEach(s => context.Students.AddOrUpdate(p => p.LastName, s));
context.SaveChanges();
var instructors = new List
{
new Instructor { FirstMidName = "Kim", LastName = "Abercrombie",
HireDate = DateTime.Parse("1995-03-11") },
new Instructor { FirstMidName = "Fadi", LastName = "Fakhouri",
HireDate = DateTime.Parse("2002-07-06") },
new Instructor { FirstMidName = "Roger", LastName = "Harui",
HireDate = DateTime.Parse("1998-07-01") },
new Instructor { FirstMidName = "Candace", LastName = "Kapoor",
HireDate = DateTime.Parse("2001-01-15") },
new Instructor { FirstMidName = "Roger", LastName = "Zheng",
HireDate = DateTime.Parse("2004-02-12") }
};
instructors.ForEach(s => context.Instructors.AddOrUpdate(p => p.LastName, s));
context.SaveChanges();
var departments = new List
{
new Department { Name = "English", Budget = 350000,
StartDate = DateTime.Parse("2007-09-01"),
InstructorID = instructors.Single( i => i.LastName == "Abercrombie").ID },
new Department { Name = "Mathematics", Budget = 100000,
StartDate = DateTime.Parse("2007-09-01"),
InstructorID = instructors.Single( i => i.LastName == "Fakhouri").ID },
new Department { Name = "Engineering", Budget = 350000,
StartDate = DateTime.Parse("2007-09-01"),
InstructorID = instructors.Single( i => i.LastName == "Harui").ID },
new Department { Name = "Economics", Budget = 100000,
StartDate = DateTime.Parse("2007-09-01"),
InstructorID = instructors.Single( i => i.LastName == "Kapoor").ID }
};
departments.ForEach(s => context.Departments.AddOrUpdate(p => p.Name, s));
context.SaveChanges();
var courses = new List
{
new Course {CourseID = 1050, Title = "Chemistry", Credits = 3,
DepartmentID = departments.Single( s => s.Name == "Engineering").DepartmentID,
Instructors = new List()
},
new Course {CourseID = 4022, Title = "Microeconomics", Credits = 3,
DepartmentID = departments.Single( s => s.Name == "Economics").DepartmentID,
Instructors = new List()
},
new Course {CourseID = 4041, Title = "Macroeconomics", Credits = 3,
DepartmentID = departments.Single( s => s.Name == "Economics").DepartmentID,
Instructors = new List()
},
new Course {CourseID = 1045, Title = "Calculus", Credits = 4,
DepartmentID = departments.Single( s => s.Name == "Mathematics").DepartmentID,
Instructors = new List()
},
new Course {CourseID = 3141, Title = "Trigonometry", Credits = 4,
DepartmentID = departments.Single( s => s.Name == "Mathematics").DepartmentID,
Instructors = new List()
},
new Course {CourseID = 2021, Title = "Composition", Credits = 3,
DepartmentID = departments.Single( s => s.Name == "English").DepartmentID,
Instructors = new List()
},
new Course {CourseID = 2042, Title = "Literature", Credits = 4,
DepartmentID = departments.Single( s => s.Name == "English").DepartmentID,
Instructors = new List()
},
};
courses.ForEach(s => context.Courses.AddOrUpdate(p => p.CourseID, s));
context.SaveChanges();
var officeAssignments = new List
{
new OfficeAssignment {
InstructorID = instructors.Single( i => i.LastName == "Fakhouri").ID,
Location = "Smith 17" },
new OfficeAssignment {
InstructorID = instructors.Single( i => i.LastName == "Harui").ID,
Location = "Gowan 27" },
new OfficeAssignment {
InstructorID = instructors.Single( i => i.LastName == "Kapoor").ID,
Location = "Thompson 304" },
};
officeAssignments.ForEach(s => context.OfficeAssignments.AddOrUpdate(p => p.InstructorID, s));
context.SaveChanges();
AddOrUpdateInstructor(context, "Chemistry", "Kapoor");
AddOrUpdateInstructor(context, "Chemistry", "Harui");
AddOrUpdateInstructor(context, "Microeconomics", "Zheng");
AddOrUpdateInstructor(context, "Macroeconomics", "Zheng");
AddOrUpdateInstructor(context, "Calculus", "Fakhouri");
AddOrUpdateInstructor(context, "Trigonometry", "Harui");
AddOrUpdateInstructor(context, "Composition", "Abercrombie");
AddOrUpdateInstructor(context, "Literature", "Abercrombie");
context.SaveChanges();
var enrollments = new List
{
new Enrollment {
StudentID = students.Single(s => s.LastName == "Alexander").ID,
CourseID = courses.Single(c => c.Title == "Chemistry" ).CourseID,
Grade = Grade.A
},
new Enrollment {
StudentID = students.Single(s => s.LastName == "Alexander").ID,
CourseID = courses.Single(c => c.Title == "Microeconomics" ).CourseID,
Grade = Grade.C
},
new Enrollment {
StudentID = students.Single(s => s.LastName == "Alexander").ID,
CourseID = courses.Single(c => c.Title == "Macroeconomics" ).CourseID,
Grade = Grade.B
},
new Enrollment {
StudentID = students.Single(s => s.LastName == "Alonso").ID,
CourseID = courses.Single(c => c.Title == "Calculus" ).CourseID,
Grade = Grade.B
},
new Enrollment {
StudentID = students.Single(s => s.LastName == "Alonso").ID,
CourseID = courses.Single(c => c.Title == "Trigonometry" ).CourseID,
Grade = Grade.B
},
new Enrollment {
StudentID = students.Single(s => s.LastName == "Alonso").ID,
CourseID = courses.Single(c => c.Title == "Composition" ).CourseID,
Grade = Grade.B
},
new Enrollment {
StudentID = students.Single(s => s.LastName == "Anand").ID,
CourseID = courses.Single(c => c.Title == "Chemistry" ).CourseID
},
new Enrollment {
StudentID = students.Single(s => s.LastName == "Anand").ID,
CourseID = courses.Single(c => c.Title == "Microeconomics").CourseID,
Grade = Grade.B
},
new Enrollment {
StudentID = students.Single(s => s.LastName == "Barzdukas").ID,
CourseID = courses.Single(c => c.Title == "Chemistry").CourseID,
Grade = Grade.B
},
new Enrollment {
StudentID = students.Single(s => s.LastName == "Li").ID,
CourseID = courses.Single(c => c.Title == "Composition").CourseID,
Grade = Grade.B
},
new Enrollment {
StudentID = students.Single(s => s.LastName == "Justice").ID,
CourseID = courses.Single(c => c.Title == "Literature").CourseID,
Grade = Grade.B
}
};
foreach (Enrollment e in enrollments)
{
var enrollmentInDataBase = context.Enrollments.Where(
s =>
s.Student.ID == e.StudentID &&
s.Course.CourseID == e.CourseID).SingleOrDefault();
if (enrollmentInDataBase == null)
{
context.Enrollments.Add(e);
}
}
context.SaveChanges();
}
void AddOrUpdateInstructor(SchoolContext context, string courseTitle, string instructorName)
{
var crs = context.Courses.SingleOrDefault(c => c.Title == courseTitle);
var inst = crs.Instructors.SingleOrDefault(i => i.LastName == instructorName);
if (inst == null)
crs.Instructors.Add(context.Instructors.Single(i => i.LastName == instructorName));
}
}
}
As you saw in the first tutorial, most of this code simply updates or creates new entity objects and loads sample data into properties as required for testing. However, notice how the
var courses = new List
{
new Course {CourseID = 1050, Title = "Chemistry", Credits = 3,
DepartmentID = departments.Single( s => s.Name == "Engineering").DepartmentID,
Instructors = new List()
},
...
};
courses.ForEach(s => context.Courses.AddOrUpdate(p => p.CourseID, s));
context.SaveChanges();
When you create a
From the PMC, enter the
If you tried to run the
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, and that's what you have to do now. The generated code in the ComplexDataModel
Edit the <timestamp>_ComplexDataModel.cs file, comment out the line of code that adds the DepartmentID column to the Course table, and add the following highlighted code (the commented line is also highlighted):
CreateTable(
"dbo.CourseInstructor",
c => new
{
CourseID = c.Int(nullable: false),
InstructorID = c.Int(nullable: false),
})
.PrimaryKey(t => new { t.CourseID, t.InstructorID })
.ForeignKey("dbo.Course", t => t.CourseID, cascadeDelete: true)
.ForeignKey("dbo.Instructor", t => t.InstructorID, cascadeDelete: true)
.Index(t => t.CourseID)
.Index(t => t.InstructorID);
// Create a department for course to point to.
Sql("INSERT INTO dbo.Department (Name, Budget, StartDate) VALUES ('Temp', 0.00, GETDATE())");
// default value for FK points to department created above.
AddColumn("dbo.Course", "DepartmentID", c => c.Int(nullable: false, defaultValue: 1));
//AddColumn("dbo.Course", "DepartmentID", c => c.Int(nullable: false));
AlterColumn("dbo.Course", "Title", c => c.String(maxLength: 50));
When the
After you have finished editing the <timestamp>_ComplexDataModel.cs file, enter the
update-database
Note: It's possible to get other errors when migrating data and making schema changes. If you get migration errors you can't resolve, you can either change the database name in the connection string or delete the database. The simplest approach is to rename the database in Web.config file. The following example shows the name changed to CU_Test:
providerName="System.Data.SqlClient" />
With a new database, there is no data to migrate, and the
If that fails, another thing you can try is re-initialize the database by entering the following command in the PMC:
Open the database in Server Explorer as you did earlier, and expand the Tables node to see that all of the tables have been created. (If you still have Server Explorer open from the earlier time, click the Refresh button.)
You didn't create a model class for the
Right-click the
You now have a more complex data model and corresponding database. In the following tutorial you'll learn more about different ways to access related data.
Please leave feedback on how you liked this tutorial and what we could improve. You can also request new topics at Show Me How With Code.
Links to other Entity Framework resources can be found in the ASP.NET Data Access - Recommended Resources.
This article was originally created on February 14, 2014
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. You'll see two ways to customize the data model: by adding attributes to entity classes and by adding code to the database context class.
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
- Code:
School
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
- Code:
EnrollmentDate
- Code:
Student
In Models\Student.cs, add a
- Code:
using
- Code:
System.ComponentModel.DataAnnotations
- Code:
DataType
- Code:
DisplayFormat
- Code:
EnrollmentDate
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
namespace ContosoUniversity.Models
{
public class Student
{
public int ID { get; set; }
public string LastName { get; set; }
public string FirstMidName { get; set; }
[DataType(DataType.Date)]
[DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
public DateTime EnrollmentDate { get; set; }
public virtual ICollection
}
}
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
- Code:
DataType
- Code:
mailto:
- Code:
DataType.Date
The
- Code:
DisplayFormat
[DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
The
- Code:
ApplyFormatInEditMode
You can use the DisplayFormat attribute by itself, but it's generally a good idea to use the DataType attribute also. The
- Code:
DataType
- Code:
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.
- The DataType attribute can enable MVC to choose the right field template to render the data (the DisplayFormat uses the string template). For more information, see Brad Wilson's ASP.NET MVC 2 Templates. (Though written for MVC 2, this article still applies to the current version of ASP.NET MVC.)
If you use the
- Code:
DataType
- Code:
DisplayFormat
For more information about how to handle other date formats in MVC, go to MVC 5 Introduction: Examining the Edit Methods and Edit View and search in the page for "internationalization".
Run the Student Index page again and notice that times are no longer displayed for the enrollment dates. The same will be true for any view that uses the
- Code:
Student
The StringLengthAttribute
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
- Code:
LastName
- Code:
FirstMidName
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
namespace ContosoUniversity.Models
{
public class Student
{
public int ID { get; set; }
[StringLength(50)]
public string LastName { get; set; }
[StringLength(50, ErrorMessage = "First name cannot be longer than 50 characters.")]
public string FirstMidName { get; set; }
[DataType(DataType.Date)]
[DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
public DateTime EnrollmentDate { get; set; }
public virtual ICollection
}
}
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:
- Code:
[RegularExpression(@"^[A-Z]+[a-zA-Z''-'\s]*$")]
The MaxLength attribute provides similar functionality to the StringLength attribute but doesn't provide client side validation.
Run the application and click the Students tab. You get the following error:
The model backing the 'SchoolContext' context has changed since the database was created. Consider using Code First Migrations to update the database (http://go.microsoft.com/fwlink/?LinkId=238269).
The database model has changed in a way that requires a change in the database schema, and Entity Framework detected that. You'll use migrations to update the schema without losing any data that you added to the database by using the UI. If you changed data that was created by the
- Code:
Seed
- Code:
Seed
In the Package Manager Console (PMC), enter the following commands:
a
- Code:
dd-migration MaxLengthOnNames
update-database
The
- Code:
add-migration
- Code:
Up
- Code:
update-database
The timestamp prepended to the migrations file name is used by Entity Framework to order the migrations. You can create multiple migrations before running the
- Code:
update-database
Run the Create page, 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
- Code:
FirstMidName
- Code:
FirstName
- Code:
Column
The
- Code:
Column
- Code:
Student
- Code:
FirstMidName
- Code:
FirstName
- Code:
Student.FirstMidName
- Code:
FirstName
- Code:
Student
In the Student.cs file, add a
- Code:
using
- Code:
FirstMidName
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace ContosoUniversity.Models
{
public class Student
{
public int ID { get; set; }
[StringLength(50)]
public string LastName { get; set; }
[StringLength(50, ErrorMessage = "First name cannot be longer than 50 characters.")]
[Column("FirstName")]
public string FirstMidName { get; set; }
[DataType(DataType.Date)]
[DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
public DateTime EnrollmentDate { get; set; }
public virtual ICollection
}
}
The addition of the Column attribute changes the model backing the SchoolContext, so it won't match the database. Enter the following commands in the PMC to create another migration:
- Code:
add-migration ColumnFirstName
update-database
In Server Explorer, open the Student table designer by double-clicking the Student table.
The following image shows the original column name as it was before you applied the first two migrations. In addition to the column name changing from
- Code:
FirstMidName
- Code:
FirstName
- Code:
MAX
You can also make database mapping changes using the Fluent API, as you'll see later in this tutorial.
Note If you try to compile before you finish creating all of the entity classes in the following sections, you might get compiler errors.
Complete Changes to the Student Entity
In Models\Student.cs, replace the code you added earlier with the following code. The changes are highlighted.
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace ContosoUniversity.Models
{
public class Student
{
public int ID { get; set; }
[Required]
[StringLength(50)]
[Display(Name = "Last Name")]
public string LastName { get; set; }
[Required]
[StringLength(50, ErrorMessage = "First name cannot be longer than 50 characters.")]
[Column("FirstName")]
[Display(Name = "First Name")]
public string FirstMidName { get; set; }
[DataType(DataType.Date)]
[DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
[Display(Name = "Enrollment Date")]
public DateTime EnrollmentDate { get; set; }
[Display(Name = "Full Name")]
public string FullName
{
get
{
return LastName + ", " + FirstMidName;
}
}
public virtual ICollection
}
}
The Required Attribute
The Required attribute makes the name properties required fields. The
- Code:
Required attribute
- Code:
StringLength
[Display(Name = "Last Name")]
[StringLength(50, MinimumLength=1)]
public string LastName { get; set; }
The Display Attribute
The
- Code:
Display
The FullName Calculated Property
- Code:
FullName
- Code:
get
- Code:
FullName
Create the Instructor Entity
Create Models\Instructor.cs, replacing the template code with the following code:
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace ContosoUniversity.Models
{
public class Instructor
{
public int ID { get; set; }
[Required]
[Display(Name = "Last Name")]
[StringLength(50)]
public string LastName { get; set; }
[Required]
[Column("FirstName")]
[Display(Name = "First Name")]
[StringLength(50)]
public string FirstMidName { get; set; }
[DataType(DataType.Date)]
[DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
[Display(Name = "Hire Date")]
public DateTime HireDate { get; set; }
[Display(Name = "Full Name")]
public string FullName
{
get { return LastName + ", " + FirstMidName; }
}
public virtual ICollection
public virtual OfficeAssignment OfficeAssignment { get; set; }
}
}
Notice that several properties are the same in the
- Code:
Student
- Code:
Instructor
You can put multiple attributes on one line, so you could also write the instructor class as follows:
public class Instructor
{
public int ID { get; set; }
[Display(Name = "Last Name"),StringLength(50, MinimumLength=1)]
public string LastName { get; set; }
[Column("FirstName"),Display(Name = "First Name"),StringLength(50, MinimumLength=1)]
public string FirstMidName { get; set; }
[DataType(DataType.Date),Display(Name = "Hire Date"),DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
public DateTime HireDate { get; set; }
[Display(Name = "Full Name")]
public string FullName
{
get { return LastName + ", " + FirstMidName; }
}
public virtual ICollection
public virtual OfficeAssignment OfficeAssignment { get; set; }
}
The Courses and OfficeAssignment Navigation Properties
The
- Code:
Courses
- Code:
OfficeAssignment
- Code:
IEnumerable<T>
An instructor can teach any number of courses, so
- Code:
Courses
- Code:
Course
public virtual ICollection
Our business rules state an instructor can only have at most one office, so
- Code:
OfficeAssignment
- Code:
OfficeAssignment
- Code:
null
public virtual OfficeAssignment OfficeAssignment { get; set; }
Create the OfficeAssignment Entity
Create Models\OfficeAssignment.cs with the following code:
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace ContosoUniversity.Models
{
public class OfficeAssignment
{
[Key]
[ForeignKey("Instructor")]
public int InstructorID { get; set; }
[StringLength(50)]
[Display(Name = "Office Location")]
public string Location { get; set; }
public virtual Instructor Instructor { get; set; }
}
}
Build the project, which saves your changes and verifies you haven't made any copy and paste errors the compiler can catch.
The Key Attribute
There's a one-to-zero-or-one relationship between the
- Code:
Instructor
- Code:
OfficeAssignment
- Code:
Instructor
- Code:
InstructorID
- Code:
ID
- Code:
ID
- Code:
Key
[Key]
[ForeignKey("Instructor")]
public int InstructorID { get; set; }
You can also use the
- Code:
Key
- Code:
classnameID
- Code:
ID
The ForeignKey Attribute
When there is a one-to-zero-or-one relationship or a one-to-one relationship between two entities (such as between
- Code:
OfficeAssignment
- Code:
Instructor
Unable to determine the principal end of an association between the types 'ContosoUniversity.Models.OfficeAssignment' and 'ContosoUniversity.Models.Instructor'. The principal end of this association must be explicitly configured using either the relationship fluent API or data annotations.
Later in the tutorial you'll see how to configure this relationship with the fluent API.
The Instructor Navigation Property
The
- Code:
Instructor
- Code:
OfficeAssignment
- Code:
OfficeAssignment
- Code:
Instructor
- Code:
InstructorID
- Code:
Instructor
- Code:
OfficeAssignment
You could put a
- Code:
[Required]
Modify the Course Entity
In Models\Course.cs, replace the code you added earlier with the following code:
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace ContosoUniversity.Models
{
public class Course
{
[DatabaseGenerated(DatabaseGeneratedOption.None)]
[Display(Name = "Number")]
public int CourseID { get; set; }
[StringLength(50, MinimumLength = 3)]
public string Title { get; set; }
[Range(0, 5)]
public int Credits { get; set; }
public int DepartmentID { get; set; }
public virtual Department Department { get; set; }
public virtual ICollection
public virtual ICollection
}
}
The course entity has a foreign key property
- Code:
DepartmentID
- Code:
Department
- Code:
Department
- Code:
Department
- Code:
Department
- Code:
DepartmentID
- Code:
Department
The DatabaseGenerated Attribute
The DatabaseGenerated attribute with the None parameter on the
- Code:
CourseID
[DatabaseGenerated(DatabaseGeneratedOption.None)]
[Display(Name = "Number")]
public int CourseID { get; set; }
By default, the Entity Framework assumes that primary key values are generated by the database. That's what you want in most scenarios. However, for
- Code:
Course
Foreign Key and Navigation Properties
The foreign key properties and navigation properties in the
- Code:
Course
- A course is assigned to one department, so there's a
- Code:
DepartmentID
- Code:
Department
public int DepartmentID { get; set; }
public virtual Department Department { get; set; } - A course can have any number of students enrolled in it, so the
- Code:
Enrollments
public virtual ICollectionEnrollments { get; set; } - A course may be taught by multiple instructors, so the
- Code:
Instructors
public virtual ICollectionInstructors { get; set; }
Create the Department Entity
Create Models\Department.cs with the following code:
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace ContosoUniversity.Models
{
public class Department
{
public int DepartmentID { get; set; }
[StringLength(50, MinimumLength=3)]
public string Name { get; set; }
[DataType(DataType.Currency)]
[Column(TypeName = "money")]
public decimal Budget { get; set; }
[DataType(DataType.Date)]
[DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
[Display(Name = "Start Date")]
public DateTime StartDate { get; set; }
public int? InstructorID { get; set; }
public virtual Instructor Administrator { get; set; }
public virtual ICollection
}
}
The Column Attribute
Earlier you used the Column attribute to change column name mapping. In the code for the
- Code:
Department
- Code:
Column
[Column(TypeName="money")]
public decimal Budget { get; set; }
Column mapping is generally not required, because the Entity Framework usually chooses the appropriate SQL Server data type based on the CLR type that you define for the property. The CLR
- Code:
decimal
- Code:
decimal
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
- Code:
InstructorID
- Code:
Instructor
- Code:
int
- Code:
Administrator
- Code:
Instructor
public int? InstructorID { get; set; }
public virtual Instructor Administrator { get; set; } - A department may have many courses, so there's a
- Code:
Courses
public virtual ICollectionCourses { get; set; }
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
- Code:
Department.InstructorID
- Code:
InstructorID
modelBuilder.Entity().HasRequired(d => d.Administrator).WithMany().WillCascadeOnDelete(false);
Modify the Enrollment Entity
In Models\Enrollment.cs, replace the code you added earlier with the following code
using System.ComponentModel.DataAnnotations;
namespace ContosoUniversity.Models
{
public enum Grade
{
A, B, C, D, F
}
public class Enrollment
{
public int EnrollmentID { get; set; }
public int CourseID { get; set; }
public int StudentID { get; set; }
[DisplayFormat(NullDisplayText = "No grade")]
public Grade? Grade { get; set; }
public virtual Course Course { get; set; }
public virtual Student Student { get; set; }
}
}
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
- Code:
CourseID
- Code:
Course
public int CourseID { get; set; }
public virtual Course Course { get; set; } - An enrollment record is for a single student, so there's a
- Code:
StudentID
- Code:
Student
public int StudentID { get; set; }
public virtual Student Student { get; set; }
Many-to-Many Relationships
There's a many-to-many relationship between the
- Code:
Student
- Code:
Course
- Code:
Enrollment
- Code:
Enrollment
- Code:
Grade
The following illustration shows what these relationships look like in an entity diagram. (This diagram was generated using the Entity Framework Power Tools; 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
- Code:
Enrollment
- Code:
CourseID
- Code:
StudentID
- Code:
Instructor
- Code:
Course
A join table is required in the database, however, as shown in the following database diagram:
The Entity Framework automatically creates the
- Code:
CourseInstructor
- Code:
Instructor.Courses
- Code:
Course.Instructors
Entity Diagram Showing Relationships
The following illustration shows the diagram that the Entity Framework Power Tools create for the completed School model.
Besides the many-to-many relationship lines (* to *) and 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
- Code:
Instructor
- Code:
OfficeAssignment
Customize the Data Model by adding Code to the Database Context
Next you'll add the new entities to the
- Code:
SchoolContext
modelBuilder.Entity
.HasMany(c => c.Instructors).WithMany(i => i.Courses)
.Map(t => t.MapLeftKey("CourseID")
.MapRightKey("InstructorID")
.ToTable("CourseInstructor"));
In this tutorial you'll use 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
- Code:
MinimumLength
- Code:
MinimumLength
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.
To add the new entities to the data model and perform database mapping that you didn't do by using attributes, replace the code in DAL\SchoolContext.cs with the following code:
using ContosoUniversity.Models;
using System.Data.Entity;
using System.Data.Entity.ModelConfiguration.Conventions;
namespace ContosoUniversity.DAL
{
public class SchoolContext : DbContext
{
public DbSet
public DbSet
public DbSet
public DbSet
public DbSet
public DbSet
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Conventions.Remove
modelBuilder.Entity
.HasMany(c => c.Instructors).WithMany(i => i.Courses)
.Map(t => t.MapLeftKey("CourseID")
.MapRightKey("InstructorID")
.ToTable("CourseInstructor"));
}
}
}
The new statement in the OnModelCreating method configures the many-to-many join table:
- For the many-to-many relationship between the
- Code:
Instructor
- Code:
Course
- Code:
InstructorInstructorID
- Code:
InstructorID
modelBuilder.Entity()
.HasMany(c => c.Instructors).WithMany(i => i.Courses)
.Map(t => t.MapLeftKey("CourseID")
.MapRightKey("InstructorID")
.ToTable("CourseInstructor"));
The following code provides an example of how you could have used fluent API instead of attributes to specify the relationship between the
- Code:
Instructor
- Code:
OfficeAssignment
modelBuilder.Entity
.HasOptional(p => p.OfficeAssignment).WithRequired(p => p.Instructor);
For information about what "fluent API" statements are doing behind the scenes, see the Fluent API blog post.
Seed the Database with Test Data
Replace the code in the Migrations\Configuration.cs file with the following code in order to provide seed data for the new entities you've created.
namespace ContosoUniversity.Migrations
{
using ContosoUniversity.Models;
using ContosoUniversity.DAL;
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.Entity.Migrations;
using System.Linq;
internal sealed class Configuration : DbMigrationsConfiguration
{
public Configuration()
{
AutomaticMigrationsEnabled = false;
}
protected override void Seed(SchoolContext context)
{
var students = new List
{
new Student { FirstMidName = "Carson", LastName = "Alexander",
EnrollmentDate = DateTime.Parse("2010-09-01") },
new Student { FirstMidName = "Meredith", LastName = "Alonso",
EnrollmentDate = DateTime.Parse("2012-09-01") },
new Student { FirstMidName = "Arturo", LastName = "Anand",
EnrollmentDate = DateTime.Parse("2013-09-01") },
new Student { FirstMidName = "Gytis", LastName = "Barzdukas",
EnrollmentDate = DateTime.Parse("2012-09-01") },
new Student { FirstMidName = "Yan", LastName = "Li",
EnrollmentDate = DateTime.Parse("2012-09-01") },
new Student { FirstMidName = "Peggy", LastName = "Justice",
EnrollmentDate = DateTime.Parse("2011-09-01") },
new Student { FirstMidName = "Laura", LastName = "Norman",
EnrollmentDate = DateTime.Parse("2013-09-01") },
new Student { FirstMidName = "Nino", LastName = "Olivetto",
EnrollmentDate = DateTime.Parse("2005-09-01") }
};
students.ForEach(s => context.Students.AddOrUpdate(p => p.LastName, s));
context.SaveChanges();
var instructors = new List
{
new Instructor { FirstMidName = "Kim", LastName = "Abercrombie",
HireDate = DateTime.Parse("1995-03-11") },
new Instructor { FirstMidName = "Fadi", LastName = "Fakhouri",
HireDate = DateTime.Parse("2002-07-06") },
new Instructor { FirstMidName = "Roger", LastName = "Harui",
HireDate = DateTime.Parse("1998-07-01") },
new Instructor { FirstMidName = "Candace", LastName = "Kapoor",
HireDate = DateTime.Parse("2001-01-15") },
new Instructor { FirstMidName = "Roger", LastName = "Zheng",
HireDate = DateTime.Parse("2004-02-12") }
};
instructors.ForEach(s => context.Instructors.AddOrUpdate(p => p.LastName, s));
context.SaveChanges();
var departments = new List
{
new Department { Name = "English", Budget = 350000,
StartDate = DateTime.Parse("2007-09-01"),
InstructorID = instructors.Single( i => i.LastName == "Abercrombie").ID },
new Department { Name = "Mathematics", Budget = 100000,
StartDate = DateTime.Parse("2007-09-01"),
InstructorID = instructors.Single( i => i.LastName == "Fakhouri").ID },
new Department { Name = "Engineering", Budget = 350000,
StartDate = DateTime.Parse("2007-09-01"),
InstructorID = instructors.Single( i => i.LastName == "Harui").ID },
new Department { Name = "Economics", Budget = 100000,
StartDate = DateTime.Parse("2007-09-01"),
InstructorID = instructors.Single( i => i.LastName == "Kapoor").ID }
};
departments.ForEach(s => context.Departments.AddOrUpdate(p => p.Name, s));
context.SaveChanges();
var courses = new List
{
new Course {CourseID = 1050, Title = "Chemistry", Credits = 3,
DepartmentID = departments.Single( s => s.Name == "Engineering").DepartmentID,
Instructors = new List
},
new Course {CourseID = 4022, Title = "Microeconomics", Credits = 3,
DepartmentID = departments.Single( s => s.Name == "Economics").DepartmentID,
Instructors = new List
},
new Course {CourseID = 4041, Title = "Macroeconomics", Credits = 3,
DepartmentID = departments.Single( s => s.Name == "Economics").DepartmentID,
Instructors = new List
},
new Course {CourseID = 1045, Title = "Calculus", Credits = 4,
DepartmentID = departments.Single( s => s.Name == "Mathematics").DepartmentID,
Instructors = new List
},
new Course {CourseID = 3141, Title = "Trigonometry", Credits = 4,
DepartmentID = departments.Single( s => s.Name == "Mathematics").DepartmentID,
Instructors = new List
},
new Course {CourseID = 2021, Title = "Composition", Credits = 3,
DepartmentID = departments.Single( s => s.Name == "English").DepartmentID,
Instructors = new List
},
new Course {CourseID = 2042, Title = "Literature", Credits = 4,
DepartmentID = departments.Single( s => s.Name == "English").DepartmentID,
Instructors = new List
},
};
courses.ForEach(s => context.Courses.AddOrUpdate(p => p.CourseID, s));
context.SaveChanges();
var officeAssignments = new List
{
new OfficeAssignment {
InstructorID = instructors.Single( i => i.LastName == "Fakhouri").ID,
Location = "Smith 17" },
new OfficeAssignment {
InstructorID = instructors.Single( i => i.LastName == "Harui").ID,
Location = "Gowan 27" },
new OfficeAssignment {
InstructorID = instructors.Single( i => i.LastName == "Kapoor").ID,
Location = "Thompson 304" },
};
officeAssignments.ForEach(s => context.OfficeAssignments.AddOrUpdate(p => p.InstructorID, s));
context.SaveChanges();
AddOrUpdateInstructor(context, "Chemistry", "Kapoor");
AddOrUpdateInstructor(context, "Chemistry", "Harui");
AddOrUpdateInstructor(context, "Microeconomics", "Zheng");
AddOrUpdateInstructor(context, "Macroeconomics", "Zheng");
AddOrUpdateInstructor(context, "Calculus", "Fakhouri");
AddOrUpdateInstructor(context, "Trigonometry", "Harui");
AddOrUpdateInstructor(context, "Composition", "Abercrombie");
AddOrUpdateInstructor(context, "Literature", "Abercrombie");
context.SaveChanges();
var enrollments = new List
{
new Enrollment {
StudentID = students.Single(s => s.LastName == "Alexander").ID,
CourseID = courses.Single(c => c.Title == "Chemistry" ).CourseID,
Grade = Grade.A
},
new Enrollment {
StudentID = students.Single(s => s.LastName == "Alexander").ID,
CourseID = courses.Single(c => c.Title == "Microeconomics" ).CourseID,
Grade = Grade.C
},
new Enrollment {
StudentID = students.Single(s => s.LastName == "Alexander").ID,
CourseID = courses.Single(c => c.Title == "Macroeconomics" ).CourseID,
Grade = Grade.B
},
new Enrollment {
StudentID = students.Single(s => s.LastName == "Alonso").ID,
CourseID = courses.Single(c => c.Title == "Calculus" ).CourseID,
Grade = Grade.B
},
new Enrollment {
StudentID = students.Single(s => s.LastName == "Alonso").ID,
CourseID = courses.Single(c => c.Title == "Trigonometry" ).CourseID,
Grade = Grade.B
},
new Enrollment {
StudentID = students.Single(s => s.LastName == "Alonso").ID,
CourseID = courses.Single(c => c.Title == "Composition" ).CourseID,
Grade = Grade.B
},
new Enrollment {
StudentID = students.Single(s => s.LastName == "Anand").ID,
CourseID = courses.Single(c => c.Title == "Chemistry" ).CourseID
},
new Enrollment {
StudentID = students.Single(s => s.LastName == "Anand").ID,
CourseID = courses.Single(c => c.Title == "Microeconomics").CourseID,
Grade = Grade.B
},
new Enrollment {
StudentID = students.Single(s => s.LastName == "Barzdukas").ID,
CourseID = courses.Single(c => c.Title == "Chemistry").CourseID,
Grade = Grade.B
},
new Enrollment {
StudentID = students.Single(s => s.LastName == "Li").ID,
CourseID = courses.Single(c => c.Title == "Composition").CourseID,
Grade = Grade.B
},
new Enrollment {
StudentID = students.Single(s => s.LastName == "Justice").ID,
CourseID = courses.Single(c => c.Title == "Literature").CourseID,
Grade = Grade.B
}
};
foreach (Enrollment e in enrollments)
{
var enrollmentInDataBase = context.Enrollments.Where(
s =>
s.Student.ID == e.StudentID &&
s.Course.CourseID == e.CourseID).SingleOrDefault();
if (enrollmentInDataBase == null)
{
context.Enrollments.Add(e);
}
}
context.SaveChanges();
}
void AddOrUpdateInstructor(SchoolContext context, string courseTitle, string instructorName)
{
var crs = context.Courses.SingleOrDefault(c => c.Title == courseTitle);
var inst = crs.Instructors.SingleOrDefault(i => i.LastName == instructorName);
if (inst == null)
crs.Instructors.Add(context.Instructors.Single(i => i.LastName == instructorName));
}
}
}
As you saw in the first tutorial, most of this code simply updates or creates new entity objects and loads sample data into properties as required for testing. However, notice how the
- Code:
Course
- Code:
Instructor
var courses = new List
{
new Course {CourseID = 1050, Title = "Chemistry", Credits = 3,
DepartmentID = departments.Single( s => s.Name == "Engineering").DepartmentID,
Instructors = new List
},
...
};
courses.ForEach(s => context.Courses.AddOrUpdate(p => p.CourseID, s));
context.SaveChanges();
When you create a
- Code:
Course
- Code:
Instructors
- Code:
Instructors = new List<Instructor>()
- Code:
Instructor
- Code:
Course
- Code:
Instructors.Add
- Code:
Instructors
- Code:
Add
Add a Migration and Update the Database
From the PMC, enter the
- Code:
add-migration
- Code:
update-database
- Code:
add-Migration ComplexDataModel
If you tried to run the
- Code:
update-database
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, and that's what you have to do now. The generated code in the ComplexDataModel
- Code:
Up
- Code:
DepartmentID
- Code:
Course
- Code:
Course
- Code:
AddColumn
- Code:
Course
- Code:
Up
- Code:
Seed
Edit the <timestamp>_ComplexDataModel.cs file, comment out the line of code that adds the DepartmentID column to the Course table, and add the following highlighted code (the commented line is also highlighted):
CreateTable(
"dbo.CourseInstructor",
c => new
{
CourseID = c.Int(nullable: false),
InstructorID = c.Int(nullable: false),
})
.PrimaryKey(t => new { t.CourseID, t.InstructorID })
.ForeignKey("dbo.Course", t => t.CourseID, cascadeDelete: true)
.ForeignKey("dbo.Instructor", t => t.InstructorID, cascadeDelete: true)
.Index(t => t.CourseID)
.Index(t => t.InstructorID);
// Create a department for course to point to.
Sql("INSERT INTO dbo.Department (Name, Budget, StartDate) VALUES ('Temp', 0.00, GETDATE())");
// default value for FK points to department created above.
AddColumn("dbo.Course", "DepartmentID", c => c.Int(nullable: false, defaultValue: 1));
//AddColumn("dbo.Course", "DepartmentID", c => c.Int(nullable: false));
AlterColumn("dbo.Course", "Title", c => c.String(maxLength: 50));
When the
- Code:
Seed
- Code:
Department
- Code:
Course
- Code:
Department
- Code:
Course.DepartmentID
- Code:
Seed
- Code:
Course
- Code:
Seed
- Code:
DepartmentID
After you have finished editing the <timestamp>_ComplexDataModel.cs file, enter the
- Code:
update-database
update-database
Note: It's possible to get other errors when migrating data and making schema changes. If you get migration errors you can't resolve, you can either change the database name in the connection string or delete the database. The simplest approach is to rename the database in Web.config file. The following example shows the name changed to CU_Test:
With a new database, there is no data to migrate, and the
- Code:
update-database
If that fails, another thing you can try is re-initialize the database by entering the following command in the PMC:
- Code:
update-database -TargetMigration:0
Open the database in Server Explorer as you did earlier, and expand the Tables node to see that all of the tables have been created. (If you still have Server Explorer open from the earlier time, click the Refresh button.)
You didn't create a model class for the
- Code:
CourseInstructor
- Code:
Instructor
- Code:
Course
Right-click the
- Code:
CourseInstructor
- Code:
Instructor
- Code:
Course.Instructors
Summary
You now have a more complex data model and corresponding database. In the following tutorial you'll learn more about different ways to access related data.
Please leave feedback on how you liked this tutorial and what we could improve. You can also request new topics at Show Me How With Code.
Links to other Entity Framework resources can be found in the ASP.NET Data Access - Recommended Resources.
This article was originally created on February 14, 2014