Building an Entity Framework 7 MVC Web App using Visual Studio 2013 – Part 3

In this post we will make more use of the Fluent API which provides a way to configure our data model.

So far we have used Entity Framework 7 (EF7) to apply an initial migration creating our database, then made database changes by adding another EF7 migration. We have been using both convention and configuration in working with EF7. By convention, EF7 created tables using the same name as our model classes. Also, EF7 used the Id properties as a primary key for the objects by convention and created clustered indexes for them. By convention, string properties were created as nullable nvarchar(max) in the database and the boolean property as a non-null bit.

Continuing from Part 2, we have been asked to include data from the government classification of animals database. It’s been decided to add a new model class and that the class must be implemented this way.

public class ZPRBAnimalClassificationList
    {
        public Guid Identifier { get; set; }
        public string Name { get; set; }
        public bool? IsIndigenous { get; set; }
        public decimal? MilesToNearestZPRBOffice { get; set; }
        public bool? MeetsAllZPRBPoliciesSetForth { get; set; }
        public byte[] RowVersion { get; set; }
    }

You have been told that the database type for decimal needs to be a decimal(8, 1) and that the RowVersion property will serve as a concurrency token to support optimistic concurrency. Also the Guid (globally unique identifier) needs to be the primary key. The Name property is required in the database and has a maximum size of 500 characters. We also need the database to generate the GUIDs for us on an INSERT. You are allowed to change the names of the table or columns in the database that represent this model class.

Add the class under the Models folder by placing in the ZooEntities.cs file. Build the project and make sure there are no errors.

First thing we will name the table that gets created a little more meaningful to us. Lets name the table AnimalClassification which is somewhat shorter than the class name ZPRBAnimalClassificationListing. We will also put this table in its’ own schema named ZPRB. We will also indicate that the Guid property is the primary key. Then we will rename the Identifier column to Id matching the other tables. Finally we instruct the database that we need GUIDs generated on INSERT.

Add the following to the top of the ZooSightingDemoDb class
public DbSet<ZPRBAnimalClassificationList> AnimalClassifications { get; set; }

Add the following to the OnModelCreating method in our class ZooSightingDemoDb. Note this could be cleaner but shown this way here for learning purposes.

            modelBuilder.Entity()
                .ForRelational()
                .Table("AnimalClassification");

            modelBuilder.Entity()
                 .Key(z => z.Identifier);

            modelBuilder.Entity()
                .Property(z => z.Identifier)
                .ForSqlServer()
                .Column("Id")
                .UseIdentity();

 

What are those interesting method calls ForSqlServer() and ForRelational().
This is how the Code First Fluent API is extended by providers.
Here is the list from the EF7 Design Notes:

Relational providers will have the following extension methods:

EntityType:
 Table (2 overloads, one including schema)
Property:
 Column
 ColumnType
 DefaultExpression
 DefaultValue
Key:
 Name
ForeignKey:
 Name
Index:
 Name

SQL Server will have some additional methods:

Key:
 Clustered
Index:
 Clustered
Model:
 UseSequence (3 overloads: empty; name; name, block size, start)
 UseIdentity
Property
 UseSequence (3 overloads)
 UseIdentity

Compile your program and make sure there are no errors.
Let’s create a migration named ZPRBList_1 and apply it.
Using Package Manager Console
Add-Migration -Verbose -Project SightingsWebApp -Name ZPRBList_1
Compile project making sure there are no errors.
Then apply
Apply-Migration -Verbose -Project SightingsWebApp

Let’s look at our new table.
newtable

 

The table is named AnimalClassification thanks to this method.

builder.Entity<ZPRBAnimalClassificationList>()
                .ForRelational()
                .Table("AnimalClassification");

The primary key is the GUID property and is named Id in the table thanks to these methods.

builder.Entity<ZPRBAnimalClassificationList>()
                 .Key(z => z.Identifier);

            builder.Entity<ZPRBAnimalClassificationList>()
                .Property(z => z.Identifier)
                .ForSqlServer()
                .Column("Id")

What about the last method, we asked the database to generate value on add for the GUID property.

            builder.Entity<ZPRBAnimalClassificationList>()
                .Property(z => z.Identifier)
                .GenerateValueOnAdd();

I did a small test and inserted two rows.

ZPRBAnimalClassificationList classification = new ZPRBAnimalClassificationList
            {
                Name = "Papio hamadryas",
                IsIndigenous = true,
                MilesToNearestZPRBOffice = 2.3m,
                MeetsAllZPRBPoliciesSetForth = true
            };

            ZPRBAnimalClassificationList classification2 = new ZPRBAnimalClassificationList
            {
                Name = "Papio papio",
                IsIndigenous = false,
                MilesToNearestZPRBOffice = 123.3m,
                MeetsAllZPRBPoliciesSetForth = true
            };
            
            _db.AnimalClassifications.Add(classification);
            _db.AnimalClassifications.Add(classification2);
            _db.SaveChanges();

Lets check the database table.

animalclassificationrows

We gave no values for the Id column so it looks like EF7 and the database are playing nicely together and populating the column which is how we configured with the Fluent API.

All of our configuration with the Fluent API has worked so far.

We will complete our configuration next post.