EF Code First Fluent API with VB.NET

Article
09/13/2011

The information in this post is out of date.

Visit msdn.com/data/ef for the latest information on current and past releases of EF.

For Fluent API with VB.NET see https://msdn.com/data/jj631642

Entity Framework Code First is a development methodology available beginning with the Entity Framework 4.1. When using Code First development you usually begin by writing C# or Visual Basic .NET classes that define your conceptual (domain) model. Your model can then be used to generate a database schema or be mapped to an existing database. You can then load data from and persist data to database using objects that are instances of the types defined in the conceptual model.

By default, Code First conventions are used to automatically configure mapping between the conceptual model and database schema. Code First conventions will work in most mapping scenarios, however, you can override these conventions with data annotations or fluent API. For more information about conventions supported by Code First, see https://msdn.microsoft.com/en-us/library/hh161541(VS.103).aspx.

This post shows how to override Code First conventions using the fluent API with VB.NET. The post also demonstrates the following:

Configuring database creation and initialization strategy.
Retrieving and persisting data.
Overriding the default Code First conventions to set the database name.

Prerequisites

To complete this walkthrough you must have the following installed:

Visual Studio 2010
Entity Framework 4.1

Defining the Model and overriding Code First conventions

In this step you will define VB.NET POCO entity types that represent the conceptual model. The classes do not need to derive from any base classes or implement any interfaces. To interact with data as objects that are instances of entity types for data access you would define a class that derives from DbContext and exposes System.Data.Entity.DbSet properties for the entity types defined in the model. System.Data.Entity.DbSet represents an entity set that is used to perform create, read, update, and delete operations.

When you run your application, DbContext checks to see whether a model for the type of the derived context and the given connection string is already cached in the application domain. If it is, then that model is used. If not, DbContext works out what classes to include in the model based on what DbSet properties were defined. It then uses the default Code First conventions and additional configuration that you specified through annotations or fluent API to build the model (as shown later in this post, the protected OnModelCreating(DbModelBuilder) method on DbContext is overridden to modify the model using fluent API.) The System.Data.Entity.DbSet properties are automatically initialized when the instance of the derived DbContext class is created.

To create a class library project

Open Visual Studio 2010.
On the File menu, point to New, and then click Project.
In the left pane, click Other Languages -> Visual Basic, and then select the Console Application template.
Enter CodeFirstVBSample as the project and solution name.
Add references for the EntityFramework.dll and System.ComponentModel.DataAnnotations.dll assemblies. These assemblies contain types that will be used in this walkthrough.

To define the School model using Code First development

Add a new class to the CodeFirstVBSample project. Enter SchoolModel for the class name.
Replace the code generated by default with the following code that defines the model.

Public Class Department

Public Sub New()

Me.Courses = New List(Of Course)()

End Sub

' Primary key

Public Property DepartmentID() As Integer

Public Property Name() As String

Public Property Budget() As Decimal

Public Property StartDate() As Date

Public Property Administrator() As Integer?

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

End Class

Public Class Course

Public Sub New()

Me.Instructors = New HashSet(Of Instructor)()

End Sub

' Primary key

Public Property CourseID() As Integer

Public Property Title() As String

Public Property Credits() As Integer

' Foreign key that does not follow the Code First convention.

' The fluent API will be used to configure DepartmentID_FK to be the foreign key for this entity.

Public Property DepartmentID_FK() As Integer

' Navigation properties

Public Overridable Property Department() As Department

Public Overridable Property Instructors() As ICollection(Of Instructor)

End Class

Public Class OnlineCourse

Inherits Course

Public Property URL() As String

End Class

Partial Public Class OnsiteCourse

Inherits Course

Public Sub New()

Details = New OnsiteCourseDetails()

End Sub

Public Property Details() As OnsiteCourseDetails

End Class

' Complex type

Public Class OnsiteCourseDetails

Public Property Time() As Date

Public Property Location() As String

Public Property Days() As String

End Class

Public Class Person

' Primary key

Public Property PersonID() As Integer

Public Property LastName() As String

Public Property FirstName() As String

End Class

Public Class Instructor

Inherits Person

Public Sub New()

Me.Courses = New List(Of Course)()

End Sub

Public Property HireDate() As Date

' Navigation properties

Private privateCourses As ICollection(Of Course)

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

Public Overridable Property OfficeAssignment() As OfficeAssignment

End Class

Public Class OfficeAssignment

' Primary key that does not follow the Code First convention.

' The HasKey method is used later to configure the primary key for the entity.

Public Property InstructorID() As Integer

Public Property Location() As String

Public Property Timestamp() As Byte()

' Navigation property

Public Overridable Property Instructor() As Instructor

End Class

To define a derived DbContext class that supports the School model

Add a new class to the CodeFirstVBSample project. Enter SchoolContext for the class name.
Copy the following code that defines the context class and replace the SchoolContext class definition.

Imports System.Data.Entity

Imports System.Data.Entity.Infrastructure

Imports System.Data.Entity.ModelConfiguration.Conventions

' add a reference to System.ComponentModel.DataAnnotations DLL

Imports System.ComponentModel.DataAnnotations

Public Class SchoolContext

Inherits DbContext

Public Property OfficeAssignments() As DbSet(Of OfficeAssignment)

Public Property Instructors() As DbSet(Of Instructor)

Public Property Courses() As DbSet(Of Course)

Public Property Departments() As DbSet(Of Department)

Protected Overrides Sub OnModelCreating(ByVal modelBuilder As DbModelBuilder)

End Sub

End Class

To override the default Code First conventions using the fluent API

This section demonstrates how to use the fluent APIs to configure types to tables mapping, properties to columns mapping, and relationships between tables\type in your model. The fluent API is exposed through the DbModelBuilder type and is most commonly accessed by overriding the OnModelCreating method on DbContext.

Copy the following code and add it to the OnModelCreating method defined on the SchoolContext class.

Comments explain what each mapping does.

' Configure Code First to ignore PluralizingTableName convention

' If you keep this convention then the generated tables

' will have pluralized names.

modelBuilder.Conventions.Remove(Of PluralizingTableNameConvention)()

' Specifying that a Class is a Complex Type

' The model defined in this topic defines a type OnsiteCourseDetails.

' By convention, a type that has no primary key specified

' is treated as a complex type.

' There are some scenarios where Code First will not

' detect a complex type (for example, if you do have a property

' called ID, but you do not mean for it to be a primary key).

' In such cases, you would use the fluent API to

' explicitly specify that a type is a complex type.

modelBuilder.ComplexType(Of OnsiteCourseDetails)()

' Mapping a CLR Entity Type to a Specific Table in the Database.

' All properties of OfficeAssignment will be mapped

' to columns in a table called t_OfficeAssignment.

modelBuilder.Entity(Of OfficeAssignment)().ToTable("t_OfficeAssignment")

' Mapping the Table-Per-Hierarchy (TPH) Inheritance

' In the TPH mapping scenario, all types in an inheritance hierarchy

' are mapped to a single table.

' A discriminator column is used to identify the type of each row.

' When creating your model with Code First,

' TPH is the default strategy for the types that

' participate in the inheritance hierarchy.

' By default, the discriminator column is added

' to the table with the name “Discriminator”

' and the CLR type name of each type in the hierarchy

' is used for the discriminator values.

' You can modify the default behavior by using the fluent API.

modelBuilder.Entity(Of Person)().

Map(Of Person)(Function(t) t.Requires("Type").

HasValue("Person")).

Map(Of Instructor)(Function(t) t.Requires("Type").

HasValue("Instructor"))

' Mapping the Table-Per-Type (TPT) Inheritance

' In the TPT mapping scenario, all types are mapped to individual tables.

' Properties that belong solely to a base type or derived type are stored

' in a table that maps to that type. Tables that map to derived types

' also store a foreign key that joins the derived table with the base table.

modelBuilder.Entity(Of Course)().ToTable("Course")

modelBuilder.Entity(Of OnsiteCourse)().ToTable("OnsiteCourse")

modelBuilder.Entity(Of OnlineCourse)().ToTable("OnlineCourse")

' Configuring a Primary Key

' If your class defines a property whose name is “ID” or “Id”,

' or a class name followed by “ID” or “Id”,

' the Entity Framework treats this property as a primary key by convention.

' If your property name does not follow this pattern, use the HasKey method

' to configure the primary key for the entity.

modelBuilder.Entity(Of OfficeAssignment)().

HasKey(Function(t) t.InstructorID)

' Specifying the Maximum Length on a Property

' In the following example, the Name property

' should be no longer than 50 characters.

' If you make the value longer than 50 characters,

' you will get a DbEntityValidationException exception.

modelBuilder.Entity(Of Department)().Property(Function(t) t.Name).

HasMaxLength(60)

' Configuring the Property to be Required

' In the following example, the Name property is required.

' If you do not specify the Name,

' you will get a DbEntityValidationException exception.

' The database column used to store this property will be non-nullable.

modelBuilder.Entity(Of Department)().Property(Function(t) t.Name).

IsRequired()

' Switching off Identity for Numeric Primary Keys

' The following example sets the DepartmentID property to

' System.ComponentModel.DataAnnotations.DatabaseGeneratedOption.None to indicate that

' the value will not be generated by the database.

modelBuilder.Entity(Of Course)().Property(Function(t) t.CourseID).

HasDatabaseGeneratedOption(DatabaseGeneratedOption.None)

'Specifying NOT to Map a CLR Property to a Column in the Database

modelBuilder.Entity(Of Department)().

Ignore(Function(t) t.Administrator)

'Mapping a CLR Property to a Specific Column in the Database

modelBuilder.Entity(Of Department)().Property(Function(t) t.Budget).

HasColumnName("DepartmentBudget")

'Configuring the Data Type of a Database Column

modelBuilder.Entity(Of Department)().Property(Function(t) t.Name).

HasColumnType("varchar")

'Configuring Properties on a Complex Type

modelBuilder.Entity(Of OnsiteCourse)().Property(Function(t) t.Details.Days).

HasColumnName("Days")

modelBuilder.Entity(Of OnsiteCourse)().Property(Function(t) t.Details.Location).

HasColumnName("Location")

modelBuilder.Entity(Of OnsiteCourse)().Property(Function(t) t.Details.Time).

HasColumnName("Time")

' Map one-to-zero or one relationship

' The OfficeAssignment has the InstructorID

' property that is a primary key and a foreign key.

modelBuilder.Entity(Of OfficeAssignment)().

HasRequired(Function(t) t.Instructor).

WithOptional(Function(t) t.OfficeAssignment)

' Configuring a Many-to-Many Relationship

' The following code configures a many-to-many relationship

' between the Course and Instructor types.

' In the following example, the default Code First conventions

' are used to create a join table.

' As a result the CourseInstructor table is created with

' Course_CourseID and Instructor_InstructorID columns.

modelBuilder.Entity(Of Course)().

HasMany(Function(t) t.Instructors).

WithMany(Function(t) t.Courses)

' Configuring a Many-to-Many Relationship and specifying the names

' of the columns in the join table

' If you want to specify the join table name

' and the names of the columns in the table

' you need to do additional configuration by using the Map method.

' The following code generates the CourseInstructor

' table with CourseID and InstructorID columns.

modelBuilder.Entity(Of Course)().

HasMany(Function(t) t.Instructors).

WithMany(Function(t) t.Courses).

Map(Sub(m)

m.ToTable("CourseInstructor")

m.MapLeftKey("CourseID")

m.MapRightKey("InstructorID")

End Sub)

' Configuring a foreign key name that does not follow the Code First convention

' The foreign key property on the Course class is called DepartmentID_FK

' since that does not follow Code First conventions you need to explicitly specify

' that you want DepartmentID_FK to be the foreign key.

modelBuilder.Entity(Of Course)().

HasRequired(Function(t) t.Department).

WithMany(Function(t) t.Courses).

HasForeignKey(Function(t) t.DepartmentID_FK)

' Enabling Cascade Delete

' By default, if a foreign key on the dependent entity is not nullable,

' then Code First sets cascade delete on the relationship.

' If a foreign key on the dependent entity is nullable,

' Code First does not set cascade delete on the relationship,

' and when the principal is deleted the foreign key will be set to null.

' The following code configures cascade delete on the relationship.

' You can also remove the cascade delete conventions by using:

' modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>()

' and modelBuilder.Conventions.Remove<ManyToManyCascadeDeleteConvention>().

modelBuilder.Entity(Of Course)().

HasRequired(Function(t) t.Department).

WithMany(Function(t) t.Courses).

HasForeignKey(Function(d) d.DepartmentID_FK).

WillCascadeOnDelete(False)

To configure database creation and initialization strategy

In this step you will define a class (we will call it SchoolContextInitializer) that handles database creation and initialization strategy. The database initialization strategy is specified for the derived context type. This occurs only the first time the context is used in the application domain. To set the database initialization strategy call Database.SetInitializer with an IDatabaseinitializer instance.

The SchoolContextInitializer class derives from System.Data.Entity.DropCreateDatabaseIfModelChanges, an implementation of IDatabaseInitializer(Of TContext). When using this strategy, the database will be re-created and re-seeded with data only when the model changes (for example, if a property is added to an entity, or a fluent API is used to change configuration). Override the Seed method to add data to the context and then persist this data to the database during the initialization.

To instantiate this class in your program use: Database.SetInitializer<SchoolContext>(new SchoolContextInitializer())).

Other initialization strategies available in the Entity Framework 4.1 are CreateDatabaseIfNotExists and DropDatabaseAlways.

Add a new class to the CodeFirstVBSample project. Enter SchoolContextInitializer for the class name.
Copy and paste the following SchoolContextInitializer class definition.

Imports System.Data.Entity

Public Class SchoolContextInitializer

Inherits DropCreateDatabaseIfModelChanges(Of SchoolContext)

Protected Overrides Sub Seed(ByVal context As SchoolContext)

Dim english = New Department With {

.Name = "English",

.Budget = 120000D,

.StartDate = Date.Parse("2007-09-01")}

context.Departments.Add(english)

Dim onlineCourses = New OnlineCourse With {

.CourseID = 1,

.Title = "Composition",

.Credits = 4,

.Department = english,

.URL = "https://www.fineartschool.net/Composition"}

context.Courses.Add(onlineCourses)

Dim onsiteCourses = New OnsiteCourse With {

.CourseID = 3,

.Title = "Poetry",

.Credits = 4,

.Department = english,

.Details = New OnsiteCourseDetails With {

.Days = "MTW",

.Location = "123 Smith",

.Time = Date.Parse("11:30")}}

context.Courses.Add(onsiteCourses)

context.SaveChanges()

End Sub

End Class

To retrieve and persist data

In this section you will add the code that queries the School model, updates entities, and saves the data to the database.

The DbSet properties on the DbContext derived class represent a starting query that returns all entities of the specified type. This query can be further refined by using LINQ to Entities methods (for example, Dim query = From d In context.Departments Where d.Name = "English" Select d). A query is executed when: it is enumerated by a For Each statement; it is enumerated by a collection operation such as ToArray, ToDictionary, or ToList; LINQ operators such as First or Any are specified in the outermost part of the query; the Load extension method is called on DbSet.

Open the Module1.vb file where the Main function is defined.
Copy and paste the following Module1 definition.

Imports System.Data.Entity

Module Module1

Sub Main()

' Configure School database creation and initialization Strategy.

' The initialization strategy is called the first time

' the context is used in the application domain.

Database.SetInitializer(Of SchoolContext)(New SchoolContextInitializer())

Using context As New SchoolContext()

' Add instructors to courses.

Dim englishInstructor = New Instructor With {

.PersonID = 1,

.LastName = "Abercrombie",

.FirstName = "Kim",

.HireDate = Date.Parse("1995-03-11")}

Dim englishDepartment = (

From d In context.Departments

Where d.Name = "English"

Select d).FirstOrDefault()

For Each c In englishDepartment.Courses()

c.Instructors.Add(englishInstructor)

Next c

' Save new entities.

Dim recordsAffected As Integer = context.SaveChanges()

'Get the courses where the instructor is Abercrombie Kim

Dim instructor = (

From i In context.Instructors

Where i.FirstName = "Kim" AndAlso i.LastName = "Abercrombie"

Select i).FirstOrDefault()

For Each c In instructor.Courses()

Console.WriteLine(c.Title)

End Using

End Sub

End Module

Compile and run the application.

When you ran the application, the CodeFirstVBSample.SchoolContext database was created and you saw the data in the console window because SchoolContextInitializer seeded the database with data.

The Entity Framework used the default conventions to create the database on the localhost\SQLEXPRESS instance and names it after the fully qualified type name of the derived context (CodeFirstVBSample.SchoolContext). When the application is run subsequent times, unless the model changes, the existing database will be used.

If the model changes and you do not set the initializer, you will get the following exception: “The model backing the 'ContextName’ context has changed since the database was created. Either manually delete/update the database, or call Database.SetInitializer with an IDatabaseInitializer instance. For example, the DropCreateDatabaseIfModelChanges strategy will automatically delete and recreate the database, and optionally seed it with new data.”

To override the default Code First conventions to set the database name

One way to specify the database name for a new or existing database is to add an App.config or Web.config file that contains the connection string with the same name as your context type. The .config file should be added to the project that contains the executable assembly.

Add a new app.config file to the CodeFirstVBSample project. To do this: right-click the project, click Add, and then click New Item; select General and then Application Configuration File.
Copy and paste the following connectionStrings element as a child of configuration element in the app.config file that you just added.

<add name="SchoolContext" providerName="System.Data.SqlClient"

connectionString="Server=.\SQLEXPRESS;Database=School; Trusted_Connection=true;

Integrated Security=True;MultipleActiveResultSets=True"/>

</connectionStrings>

Compile and run the application.

When you ran the application, the School database was created. Unless you change your model the subsequent execution of this application will be mapping to the existing School database.

Summary

In this post we demonstrated how to create a model using Code First development using VB.NET. It also shows how to configure mapping between the conceptual model and database schema.

Julia Kornich

Programming Writer

EF Code First Fluent API with VB.NET

Additional resources