Partager via

EF Code First Fluent API with VB.NET

  • Article

 

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:

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

  1. Open Visual Studio 2010.
  2. On the File menu, point to New, and then click Project.
  3. In the left pane, click Other Languages -> Visual Basic, and then select the Console Application template.
  4. Enter CodeFirstVBSample as the project and solution name.
  5. 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

  1. Add a new class to the CodeFirstVBSample project. Enter SchoolModel for the class name.
  2. 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

  1. Add a new class to the CodeFirstVBSample project. Enter SchoolContext for the class name.
  2. 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.

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

 

  1. Add a new class to the CodeFirstVBSample project. Enter SchoolContextInitializer for the class name.
  2. 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.

  1. Open the Module1.vb file where the Main function is defined.
  2. 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.

 

  1. 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.
  2. 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? Thanks

  • Anonymous
    November 28, 2011
    I am struggling with mapping relationship to child objects, please help? forums.asp.net/.../1

  • Anonymous
    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 application

  • Anonymous
    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 Class

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