EF Core Part 3: Working with Database-First
Creating and Updating Code with a Database
Welcome to part three of my Setting Up EF Core series. I promise to not be as long-winded in the future. If you’ve read the preceding section, a lot of this is going to sound familiar, in a bizarro sort of way. We’re on the other side of the mirror now, doing Database-First. This methodology enables you, or a DBA, to build the database prior to even creating a solution. All you’ll need to do after that is point EF Core at the database and it’ll do the entity setup for you!
I’m going to start at a point in my project where I have a simple one table database set up already.
As you can see, I only have a single table in my database at this point, Store. I’ll be updating this database to increase the complexity later, but for now, I’m going to keep this nice and simple. The command to create my context and model is the
Scaffold-DbContext command. The Package Manager Console requires a couple parameters to run this: a connection string, and an output folder like so:
Scaffold-DbContext "Server=(localdb)\mssqllocaldb;Database=DatabaseFirstExample;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models
EF Core will connect to the database and create a DbContext and entities in the folder specified by the
OutputDir parameter. Technically, you don’t need to supply an
OutputDir, but then EF Core will just dump everything in your root folder. Nobody likes a messy root.
I didn’t even have to create the Models folder, it wasn’t there so EF Core was considerate enough to make it for me. It created a DatabaseFirstExampleContext.cs and a Store.cs. DatabaseFirstExampleContext.cs is my context that will contain the DbSets and details for tables in the database.
If you read through Part 2 on creating the database through code, you’ll note that there is not model snapshot anywhere to be found. This is because EF Core doesn’t need to track the changes made through code anymore, when you update using Database-First, it’ll blow away all the models and context and just create them from scratch again. “But what if I need to make changes?” you ask, just hold your horses. I’ll cover that later.
An interesting note is that when the DbContext is created, it hardcodes the connection string directly into it. This is typically a big no-no, for the scope of this post I’m going to leave it. If this were production code, I’d move it to appsettings.json, a config file, or the user secrets as suggested by the kindly inserted warning generated along with the code. This warning will continue to haunt you in your Error Logs window until you delete this message. You’ve been warned, however this warning of the warning will only exist on this post.
You’ll see an
OnModelCreating function at the bottom, this contains code related to the Fluent API which I’ll cover in the next section, as we can expand on the code here.
Next, we’ll take a look at the Store.cs model that was created in the process even though there’s not much to see. I’ve just got a class loaded up with my columns and their C# equivalent types.
One of numerous handy things that EF Core did for me is make both the DbContext and Models partial classes. Remember before when you asked about making changes? This is how you do it.
If I wanted to add some additional features, functions, or fields that I don’t want to keep in the database, or can’t, I can create my own partial class. By creating another partial class that has the same name, the compiler will mesh the two files together into the full model that I need in my project.
If I made any changes to the either the DbContext or the Model, those changes will vanish with an update. Any custom stuff I might want to do would need to be in a separate partial class.
So with that in mind, let’s update the database and see what happens! You can see below that I’ve added a Product table to my database, with a foreign key to Store.
Now to update the data I just need to type the same
Scaffold-DbContext command that I used the first time, only now I add the
Scaffold-DbContext "Server=(localdb)\mssqllocaldb;Database=DatabaseFirstExample;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Force
This will remove the previously generated files and recreate them in the Models folder. Here is where the partial class becomes helpful. If I had added to, or changed anything on the context or models, I would have lost it after running this command.
The context has changed a bit after this update,
OnModelCreating has all the details on how the tables are related in addition to each table’s schema.
The new Product model is fairly straight forward, no surprises here. All the fields that I named are there, and a navigation property that refers to the store this product belongs to.
The Store model has a little more to it. There’s an ICollection that contains all the Product records that belong to the store, and the other columns from the database as well. In a way, I miss the pluralization of this property like EF6 had built in, but then I remember all the weird attempts at plurals that EF6 would attempt to do…
From here, I could continue to build on top of EF Core, make database changes and re-scaffold them all I want, if this were an actual application that is. It’s more likely to rot in my project folder until I remember to clean it up, or revisit it and put together a post on migrating from EF Core 1.1 to EF Core 2.0.
In part 4 I’ll cover the Fluent API which is how I can wrangle the context is a Code-First environment. It will let me specify complex keys, more complicated relationships, and other finer database points.