Since most my .NET projects for the past several years have been for clinical systems, I’ve been working from Database-First Entity Framework models. The software needed to be designed around whatever database schemas and stored procedures already existed, and data models needed to be generated from them. So, it’s only quite recently that I’ve looked at Code-First models, which seem more appropriate to situations in which we’d want a database schema to evolve as an application is being developed.
With the Code-First method, the data model is defined in the application’s code, and we sync the changes to the database.
To try the following example, you’ll need to create a new ASP.NET MVC project in Visual Studio, and ensure it’s created with the ‘Individual User Accounts‘ option (click the ‘Change Authentication‘ button when choosing the template), as I’m using classes within a file called ‘IdentityModels.cs‘ to set up the initial model and schema. When the project is loaded, right-click on the project and select ‘Manage NuGet Packages…‘. If Entity Framework isn’t already installed, install it.
We should be set up for the first step. It might be worth hanging onto the project if you’re following this example, because a future post will be going into developing the controllers to read and write data using this.
Generating the First Migration Script and Database From IdentityModel
First let’s take a look at the IdentityModels.cs file, as the two classes in here are important for understanding the Code-First method. The first class is ApplicationUser. I’m still not entirely sure how it works, as the implementations are hidden, but it uses IdentityUser as its base class, and I want to later extend an instance of this with a property that defines the application user name.
The second is the DbContext. Currently it references a connection string in Web.config named ‘DefaultConnection.
In my project, these classes are within the namespace ‘CodeFirstExample.Models‘.
Now it’s possible to generate a database table and its schema from this by using the Migrations tool, which is run in the Package Manager Console – find this in ‘Tools‘ – ‘NuGet Package Manager‘ – ‘Package Manager Console‘. Basically this is a PowerShell command line interface, and we can do more than simply fetch and remove packages.
PM> add-migration InitialMigration
The console should contain:
Checking if the context targets an existing database...
Code First Migrations enabled for project CodeFirstExample.
PM> add-migration InitialMigration
Scaffolding migration 'InitialMigration'.
The Designer Code for this migration file includes a snapshot of your current Code First model. This snapshot is used to calculate the changes to your model when you scaffold the next migration. If you make additional changes to your model that you want to include in this migration, then you can re-scaffold it by running 'Add-Migration InitialMigration' again.
We should also see a Migrations folder appear in Solution Explorer, containing a [migration name].cs file. This will have C# methods containing code that looks very much like SQL – these should be easily translatable to actual SQL scripts.
To execute the migrations script, run the following command:
Now refresh the SQL Server Object Explorer window. If there is no configuration string included in Web.config or the connection string name isn’t specified in the DbContext, Entity Framework will create a database locally. Since I’ve already got SQL Server Express installed, the new schema appears under that connection name.
The new database will be called something like ‘aspnet-[project name]-timestamp’, and it’ll have tables for the ApplicationUser identity. The table it uses for local accounts is dbo.AspNetUsers.
If the application was run, the login and register features should be functioning, and the [Authorize] attribute can be set on any controller action. A user could register and modify an account, and the login details will appear in the database table.
Creating Our Own Model
Next I’ve defined my own model for a calendar application, by adding two classes: MyCalendar and EventType. In the Models folder, I’ve created another class file called ‘MyCalendarModel.cs‘, and added the following code into it:
I want to also add the following import statements, in addition to the defaults:
Also, what I typically do with the model is add validation attributes. I think it’s safer and better to sort out the validation here rather than implement it solely in the view layer. I did also assume that these attributes would also give us more precise control of the data types in the database tables, but that doesn’t seem the case.
Adding the Model to DbContext
Now we have two extra models that wouldn’t do anything unless they’re added to the DbContext class, so I’ve added them here.
I then cleaned and rebuilt the project, just to make sure there were no obvious errors before executing another migration.
Generating the Migration Script and Applying the Changes
Similar to what I did previously, but the migration is given a different name so I’d know later which script does what, and I’ll need to force the update-database command to overwrite the existing schema:
PM> add-migration AppendMyModels
PM> update-database -Force
In Server Explorer there’ll be two additional tables, EventTypes and MyCalendars, that were created by running the above on the migration script. The only thing I needed to do after that is populate the EventTypes table, as it’s there for populating a drop-down list.
Also, if we look in the table definition, we can see the data types have been loosely applied from the attributes in the model class, and primary and foreign keys were set.
Final Thing: Populating a Database Table for an MVC Drop-Down Menu
We could populate the EventTypes table by entering the values into it using Server Explorer, but doing it from a migration script enables us to replicate the changes with other databases much faster. For this, I’d need a black migration script template, which can be generated by running ‘
add migration PopulateEventTypes‘
I’ve populated the Up() method with a series of SQL INSERT statements. I’m assuming the other method, Down(), fetches or pulls things from the database.
Now run the migration script again, using ‘update-database‘ to populate the database table. Viewing the EventTypes table in the SQL Server Object Explorer, we should see it populated.