EF Code First Fluent API with VB.NET
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)
Next
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.
<connectionStrings>
<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
Comments
Anonymous
September 19, 2011
Could you please post the code for this - with the sql script to create the DB? ThanksAnonymous
November 28, 2011
I am struggling with mapping relationship to child objects, please help? forums.asp.net/.../1Anonymous
March 14, 2012
Nice presentation. It will be much better if you would include dhe unit of work and repository pattern to this presentation and seperated the projects the way they should be, and add a winform insert update delete sample, in order to be much easier for others and of course to me to lern this in a real world applicationAnonymous
July 06, 2012
Why there are two properties 'Private AND Public' navigation Course? Public Class Instructor . . . ' Navigation properties Private privateCourses As ICollection(Of Course) Public Overridable Property Courses() As ICollection(Of Course) Public Overridable Property OfficeAssignment() As OfficeAssignment End ClassAnonymous
July 08, 2012
@isNIL - This appears to be a typo in the post, just having a public property is fine.Anonymous
September 28, 2012
Thank you. Finding a VB example helps clarify things a lot over the C# examples.