MVC 5 with EF 6 in Visual Basic - Creating a More Complex Data Model

The original tutorial series, produced by Tom Dykstra and Rick Anderson ( @RickAndMSFT ) was written using the C# language. My versions keep as close to the originals as possible, changing only the coding language. The narrative text is largely unchanged from the original and is used with permission from Microsoft.

This tutorial series teaches you how to create ASP.NET MVC 5 applications using the Entity Framework 6 and Visual Studio 2013 Express for Web. This series uses the Code First workflow. For information about how to choose between Code First, Database First, and Model First, see Entity Framework Development Workflows.

The tutorial series comprises 12 sections in total. They are intended to be followed sequentially as each section builds on the knowledge imparted in the previous sections. Progress through the sections is reflected in a Visual Studio Express for Web project download that accompanies each section which features the web application that you build through the series.

Download the code

The code for this section is available here. Save the .zip file to a convenient location and then extract the contents. Make sure you have an edition of Visual Studio 2013 installed (Express for Web, Professional, Premium or Ultimate) and double click the .sln file. Once the project is opened in your IDE, press Shift+Ctrl+B to build the solution. This will ensure that all packages are restored from Nuget and may take a while depending on your Internet connection speed.

The navigation path through the series is as follows:

1. Creating an Entity Framework Data Model
2. Implementing Basic CRUD Functionality
3. Sorting, Filtering and Paging
4. Connection Resiliency and Command Interception
5. Code First Migrations and Deployment
6. Creating a More Complex Data Model
7. Reading Related Data
8. Updating Related Data
9. Async and Stored Procedures
10. Handling Concurrency
11. Implementing-Inheritance
12. Advanced Entity Framework Scenarios

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. When you're finished, the entity classes will make up the completed data model that's shown in the following illustration:

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.vb, add an Imports statement for the System.ComponentModel.DataAnnotations namespace and add DataType and DisplayFormat attributes to the EnrollmentDate property, as shown in the following example:

Imports System.ComponentModel.DataAnnotations

Namespace Models
    Public Class Student
        Public Property ID As Integer
        Public Property LastName As String
        Public Property FirstMidName As String
        <DataType(DataType.Date)>
        <DisplayFormat(DataFormatString:="{0:yyyy-MM-dd}", ApplyFormatInEditMode:=True)>
        Public Property EnrollmentDate As DateTime

        Public Overridable Property Enrollments As ICollection(Of Enrollment)
    End Class
End Namespace

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 attributes 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:

<DisplayFormat(DataFormatString:="{0:yyyy-MM-dd}", ApplyFormatInEditMode:=True)>

The ApplyFormatInEditMode setting specifies that the specified 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.
• 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 DataType attribute with a date field, you have to specify the DisplayFormat attribute also in order to ensure that the field renders correctly in Chrome browsers. For more information, see this StackOverflow thread.

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 Student model.

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 LastName and FirstMidName properties, as shown in the following example:

Imports System.ComponentModel.DataAnnotations

Namespace Models
    Public Class Student
        Public Property ID As Integer
        <StringLength(50)>
        Public Property LastName As String
        <StringLength(50, ErrorMessage:="First name cannot be longer than 50 characters.")>
        Public Property FirstMidName As String
        <DataType(DataType.Date)>
        <DisplayFormat(DataFormatString:="{0:yyyy-MM-dd}", ApplyFormatInEditMode:=True)>
        Public Property EnrollmentDate As DateTime

        Public Overridable Property Enrollments As ICollection(Of Enrollment)
    End Class
End Namespace

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:

[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 Seed method, that will be changed back to its original state because of the AddOrUpdate method that you're using in the Seed method. (AddOrUpdate is equivalent to an "upsert" operation from database terminology.)

In the Package Manager Console (PMC), enter the following commands:

add-migration MaxLengthOnNames
update-database

The add-migration command creates a file named <timeStamp>_MaxLengthOnNames.vb. This file contains code in the Up method that will update the database to match the current data model. The update-database command ran that code.

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 update-database command, and then all of the migrations are applied in the order in which they were created.

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 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.vb file, add an Imports statement for System.ComponentModel.DataAnnotations.Schema and add the column name attribute to the FirstMidName property, as shown in the following highlighted code:

Imports System.ComponentModel.DataAnnotations
Imports System.ComponentModel.DataAnnotations.Schema

Namespace Models
    Public Class Student
        Public Property ID As Integer
        <StringLength(50)>
        Public Property LastName As String
        <StringLength(50, ErrorMessage:="First name cannot be longer than 50 characters.")>
        <Column("FirstName")>
        Public Property FirstMidName As String
        <DataType(DataType.Date)>
        <DisplayFormat(DataFormatString:="{0:yyyy-MM-dd}", ApplyFormatInEditMode:=True)>
        Public Property EnrollmentDate As DateTime

        Public Overridable Property Enrollments As ICollection(Of Enrollment)
    End Class
End Namespace

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:

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 FirstMidName to FirstName, the two name columns have changed from MAX length to 50 characters.

You can also make database mapping changes using the Fluent API, as you'll see later in this tutorial.

Complete Changes to the Student Entity

In Models\Student.vb, replace the code you added earlier with the following code. The changes are highlighted.

Imports System.ComponentModel.DataAnnotations
Imports System.ComponentModel.DataAnnotations.Schema

Namespace Models
    Public Class Student
        Public Property ID As Integer
        <Required>
        <StringLength(50)>
        <Display(Name:="Last Name")>
        Public Property LastName As String
        <Required>
        <StringLength(50, ErrorMessage:="First name cannot be longer than 50 characters.")>
        <Column("FirstName")>
        <Display(Name:="First Name")>
        Public Property FirstMidName As String
        <DataType(DataType.Date)>
        <DisplayFormat(DataFormatString:="{0:yyyy-MM-dd}", ApplyFormatInEditMode:=True)>
        <Display(Name:="Enrollment Date")>
        Public Property EnrollmentDate As DateTime

        <Display(Name:="Full Name")>
        Public Property FullName() As String
            Get
                Return String.Format("{0}, {1}", LastName, FirstMidName)
            End Get
            Set(value As String)

            End Set
        End Property

        Public Overridable Property Enrollments As ICollection(Of Enrollment)
    End Class
End Namespace

The Required Attribute

The Required attribute makes the name properties required fields. The Required attribute is not needed for value types such as DateTime, int, double, and float. Value types cannot be assigned a null value, so they are inherently treated as required fields. You could remove the Required attribute and replace it with a minimum length parameter for the StringLength attribute:

<StringLength(50, MinimumLength:=1)>
<Display(Name:="Last Name")>
Public Property LastName As String

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.vb, replacing the template code with the following code:

Imports System.ComponentModel.DataAnnotations
Imports System.ComponentModel.DataAnnotations.Schema
Namespace Models
    Public Class Instructor
        Public Property ID As Integer

        <Required>
        <StringLength(50)>
        <Display(Name:="Last Name")>
        Public Property LastName As String

        <Required>
        <StringLength(50)>
        <Column("FirstName")>
        <Display(Name:="First Name")>
        Public Property FirstMidName As String

        <DataType(DataType.Date)>
        <DisplayFormat(DataFormatString:="{0:yyyy-MM-dd}", ApplyFormatInEditMode:=True)>
        <Display(Name:="Hire Date")>
        Public Property HireDate As DateTime

        <Display(Name:="Full Name")>
        Public Property FullName() As String
            Get
                Return String.Format("{0}, {1}", LastName, FirstMidName)
            End Get
            Set(value As String)

            End Set
        End Property

        Public Overridable Property Courses As ICollection(Of Course)
        Public Overridable Property OfficeAssignment As OfficeAssignment
    End Class
End Namespace

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 Instructor class as follows:

Imports System.ComponentModel.DataAnnotations
Imports System.ComponentModel.DataAnnotations.Schema
Namespace Models
    Public Class Instructor
        Public Property ID As Integer

        <Required, StringLength(50), Display(Name:="Last Name")>
        Public Property LastName As String

        <Required, StringLength(50), Column("FirstName"), Display(Name:="First Name")>
        Public Property FirstMidName As String

        <DataType(DataType.Date), DisplayFormat(DataFormatString:="{0:yyyy-MM-dd}", ApplyFormatInEditMode:=True), Display(Name:="Hire Date")>
        Public Property HireDate As DateTime

        <Display(Name:="Full Name")>
        Public Property FullName() As String
            Get
                Return String.Format("{0}, {1}", LastName, FirstMidName)
            End Get
            Set(value As String)

            End Set
        End Property

        Public Overridable Property Courses As ICollection(Of Course)
        Public Overridable Property OfficeAssignment As OfficeAssignment
    End Class
End Namespace

The Courses and OfficeAssignment Navigation Properties

The Courses and OfficeAssignment properties are navigation properties. As was explained earlier, they are typically defined as Overridable so that they can take advantage of an Entity Framework feature called lazy loading. In addition, if a navigation property can hold multiple entities, its type must implement the ICollection(Of T) Interface. For example IList(Of T) qualifies but not IEnumerable(Of T) because IEnumerable(Of T) doesn't implement Add.

An instructor can teach any number of courses, so Courses is defined as a collection of Course entities.

Public Overridable Property Courses As ICollection(Of Course)

Our business rules state an instructor can only have at most one office, so OfficeAssignment is defined as a single OfficeAssignment entity (which may be null if no office is assigned).

Public Overridable Property OfficeAssignment As OfficeAssignment

Create the OfficeAssignment Entity

Create Models\OfficeAssignment.vb with the following code:

Imports System.ComponentModel.DataAnnotations
Imports System.ComponentModel.DataAnnotations.Schema

Namespace Models
    Public Class OfficeAssignment
        <Key>
        <ForeignKey("Instructor")>
        Public Property InstructorID As Integer
        <StringLength(50)>
        <Display(Name:="Office Location")>
        Public Property Location As String

        Public Overridable Property Instructor As Instructor
    End Class
End Namespace

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 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 Keyattribute is used to identify it as the key:

<Key>
<ForeignKey("Instructor")> 
Public Property InstructorID As Integer

You can also use the Key attribute if the entity does have its own primary key but you want to name the property something different than classnameID or ID. By default EF treats the key as non-database-generated because the column is for an identifying relationship.

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 OfficeAssignment and Instructor), EF can't work out which end of the relationship is the principal and which end is dependent.  One-to-one relationships have a reference navigation property in each class to the other class. The ForeignKey Attribute can be applied to the dependent class to establish the relationship. If you omit theForeignKey Attribute, you get the following error when you try to create the migration:

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 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.vb, replace the code you added earlier with the following code: 

Imports System.ComponentModel.DataAnnotations
Imports System.ComponentModel.DataAnnotations.Schema

Namespace Models
    Public Class Course
        <DatabaseGenerated(DatabaseGeneratedOption.None)>
        <Display(Name:="Number")>
        Public Property CourseID As Integer

        <StringLength(50, MinimumLength:=3)>
        Public Property Title As String

        <Range(0, 5)>
        Public Property Credits As Integer

        Public Property DepartmentID As Integer

        Public Overridable Property Department As Department
        Public Overridable Property Enrollments As ICollection(Of Enrollment)
        Public Overridable Property Instructors As ICollection(Of Instructor)
    End Class
End Namespace

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. 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 Property CourseID As Integer

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 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.

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.

  Public Property DepartmentID As Integer
  Public Overridable Property Department As Department
  

• A course can have any number of students enrolled in it, so the Enrollments navigation property is a collection:
  

  Public Overridable Property Enrollments As ICollection(Of Enrollment)

• A course may be taught by multiple instructors, so the Instructors navigation property is a collection:
  

  Public Overridable Property Instructors As ICollection(Of Instructor)

Create the Department Entity

Create Models\Department.vb with the following code:

Imports System.ComponentModel.DataAnnotations
Imports System.ComponentModel.DataAnnotations.Schema

Namespace Models
    Public Class Department
        Public Property DepartmentID As Integer

        <StringLength(50, MinimumLength:=3)>
        Public Property Name As String

        <DataType(DataType.Currency)>
        <Column(TypeName:="money")>
        Public Property Budget As Decimal

        <DataType(DataType.Date)>
        <DisplayFormat(DataFormatString:="{0:yyyy-MM-dd}", ApplyFormatInEditMode:=True)>
        <Display(Name:="Start Date")>
        Public Property StartDate As DateTime

        Public Property InstructorID As Integer?

        Public Overridable Property Administrator As Instructor
        Public Overridable Property Courses As ICollection(Of Course)

    End Class
End Namespace

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(TypeName:="money")> 
Public Property Budget As Decimal 

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 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. For more information about CLR data types and how they match to SQL Server data types, see SqlClient for Entity FrameworkTypes.

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 Integer type designation to mark the property as nullable.The navigation property is named Administrator but holds an Instructor entity:

  Public Property InstructorID As Integer? 
  Public Overridable Property Administrator As Instructor

• A department may have many courses, so there's a Courses navigation property:

  Public Overridable Property Courses As ICollection(Of Course)

modelBuilder.Entity(Of Department)().HasRequired(Function(d) d.Administrator).WithMany().WillCascadeOnDelete(False)

Modify the Enrollment Entity

In Models\Enrollment.vb, replace the code you added earlier with the following code:

Imports System.ComponentModel.DataAnnotations

Namespace Models
    Public Enum Grade
        A
        B
        C
        D
        E
        F
    End Enum

    Public Class Enrollment
        Public Property EnrollmentID As Integer
        Public Property CourseID As Integer
        Public Property StudentID As Integer
        <DisplayFormat(NullDisplayText:="No grade")>
        Public Property Grade As Grade?

        Public Overridable Property Course As Course
        Public Overridable Property Student As Student
    End Class
End Namespace

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:

  Public Property CourseID As Integer
  Public Overridable Property Course As Course

• An enrollment record is for a single student, so there's a StudentID foreign key property and a Student navigation property:

  Public Property StudentID As Integer 
  Public Overridable Property Student As Student

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. This 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; 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 correspond to a many-to-many join table without payload (or a pure join table) in the database, and you wouldn't have to create a model class for it at all. The Instructor and Course entities have that kind of many-to-many relationship, and as you can see, there is no entity class between them:

 A join table is required in the database, however, as shown in the following database diagram:

The Entity Framework automatically creates the CourseInstructor table, and you read and update it indirectly by reading and updating the Instructor.Courses and Course.Instructors navigation properties.

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 Instructor and OfficeAssignment entities and the zero-or-one-to-many relationship line (0..1 to *) between the Instructor and Department entities.

Customize the Data Model by adding Code to the Database Context

Next you'll add the new entities to the SchoolContext class and customize some of the mapping using fluent API calls. The API is "fluent" because it's often used by stringing a series of method calls together into a single statement, as in the following example:

modelBuilder.Entity(Of Course)() _ 
    .HasMany(Function(c) c.Instructors).WithMany(Function(i) i.Courses) _ 
    .Map(Function(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 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.

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.vb with the following code:

Imports ContosoUniversity.Models
Imports System.Data.Entity
Imports System.Data.Entity.ModelConfiguration.Conventions
Namespace DAL
    Public Class SchoolContext
        Inherits DbContext

        Public Sub New()
            MyBase.New("SchoolContext")
        End Sub

        Public Property Students As DbSet(Of Student)
        Public Property Enrollments As DbSet(Of Enrollment)
        Public Property Courses As DbSet(Of Course)
        Public Property Departments As DbSet(Of Department)
        Public Property Instructors As DbSet(Of Instructor)
        Public Property OfficeAssignments As DbSet(Of OfficeAssignment)

        Protected Overrides Sub OnModelCreating(ByVal modelBuilder As DbModelBuilder)
            modelBuilder.Conventions.Remove(Of PluralizingTableNameConvention)()
            modelBuilder.Entity(Of Course)() _
                .HasMany(Function(c) c.Instructors).WithMany(Function(i) i.Courses) _
                .Map(Function(t) t.MapLeftKey("CourseID") _
                    .MapRightKey("InstructorID") _
                    .ToTable("CourseInstructor"))
        End Sub
    End Class
End Namespace

The new statement in the OnModelCreating method configures the many-to-many join table:

• For the many-to-many relationship between the Instructor and Course entities, the code specifies the table and column names for the join table. Code First can configure the many-to-many relationship for you without this code, but if you don't call it, you will get default names such as InstructorInstructorID for the InstructorID column.

  modelBuilder.Entity(Of Course)() _
      .HasMany(Function(c) c.Instructors).WithMany(Function(i) i.Courses) _
      .Map(Function(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 Instructor and OfficeAssignment entities: 

modelBuilder.Entity(Of Instructor)() _
    .HasOptional(Function(i) i.OfficeAssignment) _
    .WithRequired(Function(i) i.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.vb file with the following code in order to provide seed data for the new entities you've created.

Imports System
Imports System.Data.Entity
Imports System.Data.Entity.Migrations
Imports System.Linq
Imports ContosoUniversity.DAL
Imports ContosoUniversity.Models

Namespace Migrations

    Friend NotInheritable Class Configuration 
        Inherits DbMigrationsConfiguration(Of SchoolContext)

        Public Sub New()
            AutomaticMigrationsEnabled = False
        End Sub

        Protected Overrides Sub Seed(context As SchoolContext)
            Dim students = New List(Of Student)() From {
                New Student() With {
                    .FirstMidName = "Carson",
                    .LastName = "Alexander",
                    .EnrollmentDate = DateTime.Parse("2010-09-01")
                },
                New Student() With {
                    .FirstMidName = "Meredith",
                    .LastName = "Alonso",
                    .EnrollmentDate = DateTime.Parse("2012-09-01")
                },
                New Student() With {
                    .FirstMidName = "Arturo",
                    .LastName = "Anand",
                    .EnrollmentDate = DateTime.Parse("2013-09-01")
                },
                New Student() With {
                    .FirstMidName = "Gytis",
                    .LastName = "Barzdukas",
                    .EnrollmentDate = DateTime.Parse("2012-09-01")
                },
                New Student() With {
                    .FirstMidName = "Yan",
                    .LastName = "Li",
                    .EnrollmentDate = DateTime.Parse("2012-09-01")
                },
                New Student() With {
                    .FirstMidName = "Peggy",
                    .LastName = "Justice",
                    .EnrollmentDate = DateTime.Parse("2011-09-01")
                },
                New Student() With {
                    .FirstMidName = "Laura",
                    .LastName = "Norman",
                    .EnrollmentDate = DateTime.Parse("2013-09-01")
                },
                New Student() With {
                    .FirstMidName = "Nino",
                    .LastName = "Olivetto",
                    .EnrollmentDate = DateTime.Parse("2005-09-01")
                }
            }

            For Each s In students
                context.Students.AddOrUpdate(Function(p) p.LastName, s)
            Next
            context.SaveChanges()

            Dim instructors = New List(Of Instructor)() From {
                New Instructor() With {
                    .FirstMidName = "Kim",
                    .LastName = "Abercrombie",
                    .HireDate = DateTime.Parse("1995-03-11")
                },
                New Instructor() With {
                    .FirstMidName = "Fadi",
                    .LastName = "Fakhouri",
                    .HireDate = DateTime.Parse("2002-07-06")
                },
                New Instructor() With {
                    .FirstMidName = "Roger",
                    .LastName = "Harui",
                    .HireDate = DateTime.Parse("1998-07-01")
                },
                New Instructor() With {
                    .FirstMidName = "Candace",
                    .LastName = "Kapoor",
                    .HireDate = DateTime.Parse("2001-01-15")
                },
                New Instructor() With {
                    .FirstMidName = "Roger",
                    .LastName = "Zheng",
                    .HireDate = DateTime.Parse("2004-02-12")
                }
            }

            For Each i In instructors
                context.Instructors.AddOrUpdate(Function(p) p.LastName, i)
            Next
            context.SaveChanges()

            Dim departments = New List(Of Department)() From {
                New Department() With {
                    .Name = "English",
                    .Budget = 350000,
                    .StartDate = DateTime.Parse("2007-09-01"),
                    .InstructorID = instructors.[Single](Function(i) i.LastName = "Abercrombie").ID
                },
                New Department() With {
                    .Name = "Mathematics",
                    .Budget = 100000,
                    .StartDate = DateTime.Parse("2007-09-01"),
                    .InstructorID = instructors.[Single](Function(i) i.LastName = "Fakhouri").ID
                },
                New Department() With {
                    .Name = "Engineering",
                    .Budget = 350000,
                    .StartDate = DateTime.Parse("2007-09-01"),
                    .InstructorID = instructors.[Single](Function(i) i.LastName = "Harui").ID
                },
                New Department() With {
                    .Name = "Economics",
                    .Budget = 100000,
                    .StartDate = DateTime.Parse("2007-09-01"),
                    .InstructorID = instructors.[Single](Function(i) i.LastName = "Kapoor").ID
                }
            }

            For Each d In departments
                context.Departments.AddOrUpdate(Function(p) p.Name, d)
            Next
            context.SaveChanges()

            Dim courses = New List(Of Course)() From {
                New Course() With {
                    .CourseID = 1050,
                    .Title = "Chemistry",
                    .Credits = 3,
                    .DepartmentID = departments.[Single](Function(s) s.Name = "Engineering").DepartmentID,
                    .Instructors = New List(Of Instructor)()
                },
                New Course() With {
                    .CourseID = 4022,
                    .Title = "Microeconomics",
                    .Credits = 3,
                    .DepartmentID = departments.[Single](Function(s) s.Name = "Economics").DepartmentID,
                    .Instructors = New List(Of Instructor)()
                },
                New Course() With {
                    .CourseID = 4041,
                    .Title = "Macroeconomics",
                    .Credits = 3,
                    .DepartmentID = departments.[Single](Function(s) s.Name = "Economics").DepartmentID,
                    .Instructors = New List(Of Instructor)()
                },
                New Course() With {
                    .CourseID = 1045,
                    .Title = "Calculus",
                    .Credits = 4,
                    .DepartmentID = departments.[Single](Function(s) s.Name = "Mathematics").DepartmentID,
                    .Instructors = New List(Of Instructor)()
                },
                New Course() With {
                    .CourseID = 3141,
                    .Title = "Trigonometry",
                    .Credits = 4,
                    .DepartmentID = departments.[Single](Function(s) s.Name = "Mathematics").DepartmentID,
                    .Instructors = New List(Of Instructor)()
                },
                New Course() With {
                    .CourseID = 2021,
                    .Title = "Composition",
                    .Credits = 3,
                    .DepartmentID = departments.[Single](Function(s) s.Name = "English").DepartmentID,
                    .Instructors = New List(Of Instructor)()
                },
                New Course() With {
                    .CourseID = 2042,
                    .Title = "Literature",
                    .Credits = 4,
                    .DepartmentID = departments.[Single](Function(s) s.Name = "English").DepartmentID,
                    .Instructors = New List(Of Instructor)()
                }
            }

            For Each c In courses
                context.Courses.AddOrUpdate(Function(p) p.CourseID, c)
            Next
            context.SaveChanges()

            Dim officeAssignments = New List(Of OfficeAssignment)() From {
                New OfficeAssignment() With {
                    .InstructorID = instructors.[Single](Function(i) i.LastName = "Fakhouri").ID,
                    .Location = "Smith 17"
                },
                New OfficeAssignment() With {
                    .InstructorID = instructors.[Single](Function(i) i.LastName = "Harui").ID,
                    .Location = "Gowan 27"
                },
                New OfficeAssignment() With {
                    .InstructorID = instructors.[Single](Function(i) i.LastName = "Kapoor").ID,
                    .Location = "Thompson 304"
                }
            }

            For Each o In officeAssignments
                context.OfficeAssignments.AddOrUpdate(Function(p) p.InstructorID, o)
            Next
            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()

            Dim enrollments = New List(Of Enrollment)() From {
                New Enrollment() With {
                    .StudentID = students.[Single](Function(s) s.LastName = "Alexander").ID,
                    .CourseID = courses.[Single](Function(c) c.Title = "Chemistry").CourseID,
                    .Grade = Grade.A
                },
                New Enrollment() With {
                    .StudentID = students.[Single](Function(s) s.LastName = "Alexander").ID,
                    .CourseID = courses.[Single](Function(c) c.Title = "Microeconomics").CourseID,
                    .Grade = Grade.C
                },
                New Enrollment() With {
                    .StudentID = students.[Single](Function(s) s.LastName = "Alexander").ID,
                    .CourseID = courses.[Single](Function(c) c.Title = "Macroeconomics").CourseID,
                    .Grade = Grade.B
                },
                New Enrollment() With {
                    .StudentID = students.[Single](Function(s) s.LastName = "Alonso").ID,
                    .CourseID = courses.[Single](Function(c) c.Title = "Calculus").CourseID,
                    .Grade = Grade.B
                },
                New Enrollment() With {
                    .StudentID = students.[Single](Function(s) s.LastName = "Alonso").ID,
                    .CourseID = courses.[Single](Function(c) c.Title = "Trigonometry").CourseID,
                    .Grade = Grade.B
                },
                New Enrollment() With {

                    .StudentID = students.[Single](Function(s) s.LastName = "Alonso").ID,
                    .CourseID = courses.[Single](Function(c) c.Title = "Composition").CourseID,
                    .Grade = Grade.B
                },
                New Enrollment() With {
                    .StudentID = students.[Single](Function(s) s.LastName = "Anand").ID,
                    .CourseID = courses.[Single](Function(c) c.Title = "Chemistry").CourseID
                },
                New Enrollment() With {
                    .StudentID = students.[Single](Function(s) s.LastName = "Anand").ID,
                    .CourseID = courses.[Single](Function(c) c.Title = "Microeconomics").CourseID,
                    .Grade = Grade.B
                },
                New Enrollment() With {
                    .StudentID = students.[Single](Function(s) s.LastName = "Barzdukas").ID,
                    .CourseID = courses.[Single](Function(c) c.Title = "Chemistry").CourseID,
                    .Grade = Grade.B
                },
                New Enrollment() With {
                    .StudentID = students.[Single](Function(s) s.LastName = "Li").ID,
                    .CourseID = courses.[Single](Function(c) c.Title = "Composition").CourseID,
                    .Grade = Grade.B
                },
                New Enrollment() With {
                    .StudentID = students.[Single](Function(s) s.LastName = "Justice").ID,
                    .CourseID = courses.[Single](Function(c) c.Title = "Literature").CourseID,
                    .Grade = Grade.B
                }
            }

            For Each e As Enrollment In enrollments
                Dim enrollmentInDataBase = context.Enrollments.Where(Function(s) s.Student.ID = e.StudentID AndAlso s.Course.CourseID = e.CourseID).SingleOrDefault()
                If enrollmentInDataBase Is Nothing Then
                    context.Enrollments.Add(e)
                End If
            Next
            context.SaveChanges()
        End Sub

        Private Sub AddOrUpdateInstructor(context As SchoolContext, courseTitle As String, instructorName As String)
            Dim crs = context.Courses.SingleOrDefault(Function(c) c.Title = courseTitle)
            Dim inst = crs.Instructors.SingleOrDefault(Function(i) i.LastName = instructorName)
            If inst Is Nothing Then
                crs.Instructors.Add(context.Instructors.[Single](Function(i) i.LastName = instructorName))
            End If
        End Sub

    End Class

End Namespace

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 Course entity, which has a many-to-many relationship with the Instructor entity, is handled:

 Dim courses = New List(Of Course)() From { New Course() With {
                    .CourseID = 1050,
                    .Title = "Chemistry",
                    .Credits = 3,
                    .DepartmentID = departments.[Single](Function(s) s.Name = "Engineering").DepartmentID,
                    .Instructors = New List(Of Instructor)()
                }, 
                ....
            } 
For Each c In courses
    context.Courses.AddOrUpdate(Function(p) p.CourseID, c) 
Next 
context.SaveChanges()

When you create a Course object, you initialize the Instructors navigation property as an empty collection using the code Instructors = new List(Of Instructor)(). This makes it possible to add Instructor entities that are related to this Course by using the Instructors.Add method. If you didn't create an empty list, you wouldn't be able to add these relationships, because the Instructors property would be null and wouldn't have an Add method. You could also add the list initialization to the constructor.

Add a Migration and Update the Database

From the PMC, enter the add-migration command (don't do the update-database command yet):

add-Migration ComplexDataModel

If you tried to run the update-database 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, and that's what you have to do now. The generated code in the ComplexDataModel Up method adds a non-nullable DepartmentID foreign key to the Course table. Because there are already rows in the Course table when the code runs, the AddColumn operation will fail because SQL Server doesn't know what value to put in the column that can't be null. Therefore 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.  You can relate them to the correct departments in the Seed method.

Edit the <timestamp>_ComplexDataModel.vb 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",
    Function(c) New With
        {
            .CourseID = c.Int(nullable := False),
            .InstructorID = c.Int(nullable := False)
        }) _
    .PrimaryKey(Function(t) New With { t.CourseID, t.InstructorID }) _
    .ForeignKey("dbo.Course", Function(t) t.CourseID, cascadeDelete := True) _
    .ForeignKey("dbo.Instructor", Function(t) t.InstructorID, cascadeDelete := True) _
    .Index(Function(t) t.CourseID) _
    .Index(Function(t) t.InstructorID)
            
'AddColumn("dbo.Course", "DepartmentID", Function(c) c.Int(nullable:=False))

'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", Function(c) c.Int(nullable:=False, defaultValue:=1))

 AddColumn("dbo.Student", "FullName", Function(c) c.String())
 AlterColumn("dbo.Course", "Title", Function(c) c.String(maxLength := 50))

When the Seed method runs, it will insert rows in the Department table and it will relate existing Course rows to those new Department rows. If you haven't added any courses in the UI, you would then no longer need the "Temp" department or the default value on the Course.DepartmentID column. To allow for the possibility that someone might have added courses by using the application, you'd also want to update the Seed method code to ensure that all Course rows (not just the ones inserted by earlier runs of the Seed method) have valid DepartmentID values before you remove the default value from the column and delete the "Temp" department.

After you have finished editing the <timestamp>_ComplexDataModel.vb file, enter the update-database command in the PMC to execute the migration.

update-database

 <add name="SchoolContext" connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=CU_Test;Integrated Security=SSPI;"         providerName="System.Data.SqlClient" />

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 CourseInstructor table. As explained earlier, this is a join table for the many-to-many relationship between the Instructor and Course entities.

Right-click the CourseInstructor table and select Show Table Data to verify that it has data in it as a result of the Instructor entities you added to the Course.Instructors navigation property.

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.