Today we're going to take an even deeper look at Entity Framework Core 2.0. If you want to follow from the beginning, head on over to Part 1 of the series. If you want to just get rolling, you can download the solution we created in part 1 here:
TeamSamplePart1.zip (65.00 kb)
In this piece, we're going to explore implementing our EF Core data layer in our ASP .NET Core Web API. We're going to look at ensuring our database is created (and/or deleted), applying migrations at runtime, and seeding our database with initial metadata. I initially intended to demonstrate implementation into the API calls themselves and using the async capabilities of EF Core, but due to the sheer amount of content just around migrations I've decided to push that to a 3rd part of this series. With that in mind, let's get started.
Updating Our Data Model
First we need to put some thought into what we're going to do with our data and how we're going to use it. In this tutorial, I just want to cover some concepts without overloading with too much extra detail so we're just going to cover the metadata required by Players. In this case, it would be the list of Positions which are a type of metadata and shouldn't ever change.
With the MLB World Series in full swing and about to enter an exciting Game 7 tonight, we'll use baseball as our example. There are 9 primary positions, and we'll throw in an auxiliary position to round things out. We'll need the infield positions (1st, 2nd, Short Stop, 3rd, Catcher and Pitcher), the outfield (Left, Center and Right) and we'll throw in the Designated Hitter spot.
To do this, we want to support both abbreviations and full names for each of our positions, but our data model only contains a Name field. Let's remedy this. If you haven't already done so, open up the TeamSample solution and head over to the TeamSample.Entities project. If you use the provided download above, you'll notice that I've also separated my entites into separate files. This provides better organization and easier source control history and change tracking per entity.
Let's edit our Position entity. It currently contains Id, Name and a PlayerPositions navigation property. Let's add an Abbreviation property to this model. Our new Position model should now look like this:
public class Position
{
public int Id { get; set; }
[Required, MaxLength(25)]
public string Name { get; set; }
[Required, MaxLength(2)]
public string Abbreviation { get; set; }
public ICollection<PlayerPosition> PlayerPositions { get; set; }
}
We've chosen to make the Abbreviation field required. We've also added a MaxLength attribute to specify that it can't be longer than 2 characters. As an aside, it's always a good idea to consider using the MaxLength attribute with your code first models unless they are for large blobs of text. Without the attribute, the generated column will be of type nvarchar(max). We want to keep sensible data lengths.
Now we need to update our database schema to match. First let's go ahead and create a new migration. Open up a command prompt or your terminal window and navigate to the TeamSample.Data folder as we did in the first part of this series. If you're working from the solution download at the top of this post, make sure you issue a dotnet restore command prior to trying to add the migration or you'll receive an error stating "No executable found matching command "dotnet-ef". To add our new migration, we're going to use the now familiar "ef migrations add" command. Enter the following into your terminal window:
dotnet ef migrations add AddPositionAbbreviation -s "../TeamSample.Api/TeamSample.Api.csproj"
You should receive a "Done" message. If you expand the Migrations folder in your TeamSample.Data project you'll see two new files added in the form of [timestamp]_AddPositionAbbreviation. These are our new migrations. First, we're going to go ahead and update our database. If you've not followed along from Part 1 and you're starting with the downloaded solution, make sure you update the appsettings.json file to point to an accessible database server. If you're running Windows, you can leave it at localDb, otherwise point to an external server or setup a SQL Server Docker image. Note: This will be the final time I refer to Part 1, if you find things are missing you'll have to add them, or refer to Part 1 of this series to setup the needed dependencies.
dotnet ef database update -s "../TeamSample.Api/TeamSample.Api.csproj"
This will apply any migrations to our database that haven't already been applied. If you've started clean and haven't created the database or applied the initial migration, it will all be done for you, otherwise it will start with the last unapplied migration. Now let's go ahead and script our new migration for posterity, assuming you'll want to update a separate database server or store your scripts in source control.
We just want to script the new migration we created. If you recall, we previously ran a dotnet ef migrations script command. Now we're going to change this command up a bit. Issuing the command as is will result in all migrations being scripted. We want to specify the migration to start from. The script command allows us to specify two migration names. The first will be the migration to start from, and the second will be the migration to end on. It's important to note that the first parameter is exclusive while the second is inclusive. For more detailed information on scripting migrations, refer to my earlier post on Scripting Migrations in EF Core. Since we only care about the latest migration, we only need to supply the last applied migration name, in this case InitialMigration, in our command. Again, ensuring that you're in the TeamSample.Data directory, issue the following command from your terminal/console window:
dotnet ef migrations script InitialMigration -s "../TeamSample.Api/TeamSample.Api.csproj"
Now that we have multiple migrations, this is a good time for a quick aside. When working with Migrations, and especially constantly updating our development database, it's easy to make a mistake and get flustered. As an example, I'm writing the code for this post as I write the post itself. This helps ensure I don't miss steps and allows me to check for code correctness as I go.
When creating the migration above, I added the abbreviation property to my Position model but forgot to hit save. As a result, when I scripted my migration, I noticed that there was no change to the database. This is another good reason to script each migration. It allows you to look at the generated sql and do a quick sanity check on what is actually happening to the database. In my case I had a script that did nothing aside from inserting a record into the migration history table. So, let's take a quick look at how to remedy this.
The first thing you might think to do is remove the previous migration. The Entity Framework Tools provide a quick and easy method to do this via dotnet ef migrations remove which will remove the last generated migration. The command in this case looks like this:
dotnet ef migrations remove -s "../TeamSample.Api/TeamSample.Api.csproj"
This command, however, will result in an error because we've already applied our migration to the database. The first thing we need to do is rollback our database schema to the previous migration, which in our example is InitialMigration. To do this, we will use the dotnet ef database update command again, but specify which migration to update to. Also important to note that the database update command differs from the script command in that the migration parameter specified is inclusive, so updating to that specific migration will rollback all migrations that come after it and leave you in exactly that state.
To test, let's go ahead and rollback. We're going to revert to our initial migration, remove the migration we just added, and then re-add that migration so we can keep our migration history clean. Issue the following three commands, one at a time, in your terminal/console window:
dotnet ef database update InitialMigration -s "../TeamSample.Api/TeamSample.Api.csproj"
dotnet ef migrations remove -s "../TeamSample.Api/TeamSample.Api.csproj"
dotnet ef migrations add AddPositionAbbreviation -s "../TeamSample.Api/TeamSample.Api.csproj"
The database will be reverted to InitialMigration, the last added migration will be removed, and we re-add (after fixing whatever mistake we made), the AddPositionAbbreviation migration. Now we should be in a good state again, so update the database with the new migration and script it.
dotnet ef database update -s "../TeamSample.Api/TeamSample.Api.csproj"
dotnet ef migrations script InitialMigration -s "../TeamSample.Api/TeamSample.Api.csproj"
Also keep in mind that you don't have to update the database first. If you want to do a sanity check first to save yourself the extra steps when making a mistake (and it's a good idea), you can issue the script command first to preview what changes will be made to your database before you apply them. The result of our new script command now looks like this:
ALTER TABLE [Positions] ADD [Abbreviation] nvarchar(2) NOT NULL DEFAULT N'';
GO
INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
VALUES (N'20171101170815_AddPositionAbbreviation', N'2.0.0-rtm-26452');
GO
We've looked at modifying our data models and working with migrations to revert mistakes and reapply. Our data model looks like it's in pretty good shape, so now we need to get it ready for use. Let's move forward.
Initializing Our Database
Since we're working in development, we want to go ahead and make sure our database exists and pre-populate it with metadata. We're going to assume that our production database is created separately and populated with its own metadata through a separate CI / deployment process, so we're going to create a database initialization class and use compiler directives to ensure the metadata is only populated when running in Debug mode.
We're going to go ahead and create this class in our Api project, so head over to the TeamSample.Api project. Add a new static class called SampleDbInitializer. We're going to create a static void Initialize method that accepts our DbContext and we're going to use it to to initialize everything. We're also going to make it thread safe and ensure that it doesn't execute more than once. Start by creating the class as follows:
using TeamSample.Data;
namespace TeamSample.Api
{
internal static class TeamSampleDbInitializer
{
private static object _lock = new object();
private static bool _initialized = false;
internal static void Initialize(TeamSampleDbContext context)
{
if(!_initialized)
{
lock(_lock)
{
if (_initialized)
return;
//Initialize the database
}
}
}
}
}
This gives us a good starting point for our initializer. We've added a double check so we don't enter the thread lock if not necessary, and that we return from the method if another thread that had been waited on has already initialized it. Now we need make sure our database is created. If we're in debug mode, we're going to make sure the database is deleted first, resulting in a clean database each run, and we're going to add some initial Position data.
To make sure our Initializer code runs, we're going to make a quick update to our Program.cs file in the TeamSample.Api project. In the Main method, we're going to create a scope, resolve our database context and pass it to the initializer. Your entire Program.cs should look like the following:
using Microsoft.AspNetCore;
using Microsoft.AspNetCore.Hosting;
using TeamSample.Data;
using Microsoft.Extensions.DependencyInjection;
namespace TeamSample.Api
{
public class Program
{
public static void Main(string[] args)
{
var host = BuildWebHost(args);
using (var scope = host.Services.CreateScope())
{
var services = scope.ServiceProvider;
var context = services.GetRequiredService<TeamSampleDbContext>();
TeamSampleDbInitializer.Initialize(context);
}
host.Run();
}
public static IWebHost BuildWebHost(string[] args) =>
WebHost.CreateDefaultBuilder(args)
.UseStartup<Startup>()
.Build();
}
}
The key to removing and recreating the database is using the EnsureDeleted and EnsureCreated methods available on the Database object of our context. Each of them returns a boolean value that tells you whether any action was taken. For example, you can wrap the EnsureCreated method in an if block so your other operations against the database only execute if the database was created. Your database initialization class should now look like the following:
using TeamSample.Data;
namespace TeamSample.Api
{
internal static class TeamSampleDbInitializer
{
private static object _lock = new object();
private static bool _initialized = false;
internal static void Initialize(TeamSampleDbContext context)
{
#if DEBUG
if(!_initialized)
{
lock(_lock)
{
if (_initialized == true)
return;
_initialized = true;
InitializeDatabase(context);
}
}
#endif
}
private static void InitializeDatabase(TeamSampleDbContext context)
{
context.Database.EnsureDeleted();
if(context.Database.EnsureCreated())
{
//Create Initial Metadata
}
}
}
}
From here on out, we're going to concentrate on the private InitializeDatabase method. Take note of the //Create Initial Metadata comment. Here, we're going to go ahead and work directly with the context and add our initial metadata. This will be working directly with the database and the changes we make here will be persisted, so we can get as crazy as we want. Keep in mind though, since we are working with the database and this Initializer is run at startup, anything we do here will have an impact on startup performance. Let's go ahead and add our positions in place of our code comment.
private static void InitializeDatabase(TeamSampleDbContext context)
{
context.Database.EnsureDeleted();
if(context.Database.EnsureCreated())
{
context.Positions.Add(new Position { Abbreviation = "1B", Name = "1st Base" });
context.Positions.Add(new Position { Abbreviation = "2B", Name = "2nd Base" });
context.Positions.Add(new Position { Abbreviation = "3B", Name = "3rd Base" });
context.Positions.Add(new Position { Abbreviation = "SS", Name = "Shortstop" });
context.Positions.Add(new Position { Abbreviation = "P", Name = "Pitcher" });
context.Positions.Add(new Position { Abbreviation = "C", Name = "Catcher" });
context.Positions.Add(new Position { Abbreviation = "LF", Name = "Left Field" });
context.Positions.Add(new Position { Abbreviation = "CF", Name = "Center Field" });
context.Positions.Add(new Position { Abbreviation = "RF", Name = "Right Field" });
context.Positions.Add(new Position { Abbreviation = "DH", Name = "Designated Hitter" });
}
}
There are a couple of things to note here. If you plan to do extensive testing and want to retain data, you may not want to delete the database. Also, the EnsureCreated method we've just used will ensure that your database exists, however, it does not use migrations to create the database. In fact, your migration history table will be lost and you'll no longer be able to apply migrations. Let's look at how we can remedy this.
Previously I stated that the keys to standing everything up were the EnsureDeleted and EnsureCreated methods. Since we're using migrations, we're going to conveniently lose those keys and break out a trusty lockpick instead. Fortunately, Entity Framework Core provides us with a Migrate extension method. This method will apply any unapplied migrations rather than creating the database based on our entity models. This allows us to retain our migration history.
Let's take a look at leveraging Migrate to manage our database. For now, just remove the entire contents of the InitializeDatabase method. If you wanted to continue to start with an entirely fresh database every run, you could leave the EnsureDeleted method call, but we're going to get rid of it. Then, return to your terminal/console window. Ensure you're in the TeamSample.Data path, and issue the following command which will remove your database entirely:
dotnet ef database drop -s "../TeamSample.Api/TeamSample.Api.csproj"
When prompted, enter 'y' and press enter. The entire TeamSample database will be dropped. Now we have a truly clean slate as our database has been completely removed. If you're using Visual Studio 2017, now would be a good time to be able to see what's happening with our databases. Go ahead and click the View menu and choose Sql Server Object Explorer. If you expand the server node, you should see something like the following:
You may see more than one localDb node (for instance a \ProjectsV13 node). Expand the one that corresponds to the connection string we supplied in the appsettings.json file. If you're using a fully fledged SQL Server Instance, you can add a connection to it here. If you're using a different IDE, use the corresponding tools or an external GUI to take a look at your database list. You should notice that our TeamSample database doesn't exist.
Now let's update our InitializeDatabase method to use the Migrate method instead. Unlike the EnsureCreated method, the Migrate method does not return any value. Implementing this method, we won't know whether or not any changes were made, so we'll have to do a quick query to verify whether our metadata exists before inserting. The Migrate extension method is added by Entity Framework Core, so if it doesn't exist, add a using Microsoft.EntityFrameworkCore; directive to the top of your database initializer class file. Then, update the InitializeDatabase method to the following:
private static void InitializeDatabase(TeamSampleDbContext context)
{
context.Database.Migrate();
var positionsExist = context.Positions.Any();
if (!positionsExist)
{
context.Positions.Add(new Position { Abbreviation = "1B", Name = "1st Base" });
context.Positions.Add(new Position { Abbreviation = "2B", Name = "2nd Base" });
context.Positions.Add(new Position { Abbreviation = "3B", Name = "3rd Base" });
context.Positions.Add(new Position { Abbreviation = "SS", Name = "Shortstop" });
context.Positions.Add(new Position { Abbreviation = "P", Name = "Pitcher" });
context.Positions.Add(new Position { Abbreviation = "C", Name = "Catcher" });
context.Positions.Add(new Position { Abbreviation = "LF", Name = "Left Field" });
context.Positions.Add(new Position { Abbreviation = "CF", Name = "Center Field" });
context.Positions.Add(new Position { Abbreviation = "RF", Name = "Right Field" });
context.Positions.Add(new Position { Abbreviation = "DH", Name = "Designated Hitter" });
context.SaveChanges();
}
}
Run your application, making sure that the Debug configuration is selected. Once it starts, close it all down and return to your IDE or database administration tool. Refresh the database list and you should now see the TeamSample database. The tables will exist, and if you look at the data in the Positions table, you'll see that our metadata has been added. With the above changes, you no longer have to run the dotnet ef database update command from the terminal. You can simply script your migrations and verify them, then run in debug and allow the code to apply your migrations. I do recommend still running them from the command line as you can catch errors in your migrations earlier, however it's now a matter of personal preference.
Wrapping Up
With our data context working in our Web Api application, our migrations being applied and our initial metadata being added, we're ready to start using our context. In Part 3 of this series, we'll explore utilizing our data layer for basic CRUD operations from our API and we'll look at using the async methods of EF Core to make our API async front to back. I hope you've enjoyed the series so far and I look forward to further broadening our EF horizons in part 3. As always, comment below and following on Twitter: @dustinhorne.