Step 1: Setup and getting started

To begin, either open the starter files entitled Activity0301_EFCore_Starter.zip or your previous files from Chapter 2. Once the project is opened, validate that you have the correct database connection in the appsettings.json file(s). Once that is correct, validate that your project structure is similar to what is shown in Figure 3-1.

Once you’ve validated the project setup, run the project to verify that it is working correctly as per the EFCore activity from Chapter 2.

If we run the project as is, we get a console output of the list of the last 20 non-distinct users in the person table as shown in Figure 3-2.

This is essentially where we had left off using the existing database at the end of Chapter 2.

Step 2: Make sure EF is ready to scaffold migrations

Ensure that you have installed the Microsoft.EntityFrameworkCore.Design package on the starter project. To do this, right-click the Solution and select Manage NuGet Packages for Solution. Once the window opens, select the Installed tab and then make sure that Microsoft.EntityFrameworkCore.Design is installed with the latest version of EFCore.

If the design package is not installed, switch to browse and then find the package and install it to the starter project. The EF_Activity001 project will not need this library. You can see what this looks like as shown in Figure 3-3.

Step 3: Create the initial migration

Now let’s create our initial migration in order to begin working with the code-first approach in our application.

To begin, let’s try running a command in the PMC. The command to run is add-migration “Initial Migration.” Before running the command, make sure that you have selected the Entity Framework library project that contains the actual database context. If you fail to select the correct project, you will get an error. Figure 3-4 shows the incorrect project selected.

Running this command generates the following error as shown in Figure 3-5.

Change the Default project drop-down to point to the project with the actual AdventureWorks DBContext in it, and then run the command again. Do you think it will work? Figure 3-6 shows the outcome.

In this case, it didn’t work, because we have two database contexts in our project. If you remember back to Chapter 1, we created a context by default for our use. Then in Chapter 2 we generated a new context. Up to this point, we haven’t used migrations, so we directly instantiated the correct context when we needed it. Now, with two contexts, Visual Studio doesn’t know which one to use to scaffold the migration. Later in the book, we’ll cover how to work with multiple contexts. For now, let’s just delete the unused context from this project. Find the file ApplicationDbContext.cs that we created in the EF_Activity001 project, right-click it, and select Delete. This is shown in more detail in Figure 3-7.

When the warning pops up about permanent deletion, just select OK and allow the file to be destroyed.

It is important to remember that changes to the structure of the project require a rebuild before attempting to add the migration.  After deleting the second database context, remember to rebuild the solution before moving on.

If we fail to rebuild and try to apply the migration, we will get the exact same error as before, letting us know that more than one DbContext was found.

With the file now deleted and the project rebuilt, once again, let’s attempt to make our first migration. Do you think it will work this time? Review Figure 3-8 to see what happens when we run the command again.

Step 4: Review the migration

Now that we have a migration ready to go, we might be tempted to jump right in and run the migration. This would be a very big mistake, as the initial migration scaffolded has a lot of tables that it is planning to create which already exist in our database.

Remember our earlier discussion of the pros and cons of using the code-first approach? This is one of the cons. Right now, we have no migrations applied in the database, so the migration builder thinks we need to create all the tables. However, our database already has all the tables, so we need to make sure that they don’t get created for us. Review Figure 3-9 to see the table creation statements as generated in the migration.

So, what should we do next? There are actually a couple of approaches we can take. One approach would be to comment out everything in the “Up” method and apply the migration with the update-database command.

This approach should work, but it begs the question about what the next developer would do, and the developer after that, if they have a fresh start on an existing database on their machine. A potential solution to that problem could be to run the migration with the code commented out and then propagate the changes and run on other machines or simply modify other developer databases so that the first migration would appear to have already been applied. However, no matter what we do here, care would need to be exercised when running for the first time in production to avoid any potential problems.

Since our database is existing, there should not be a reason that we need to generate the tables as is. Therefore, let’s go ahead and comment out the code in the Up method. You could also remove it if you wanted, assuming everyone would have access to an existing sample of the database. Additionally, we will likely never want to delete these existing tables, so let’s also entirely delete the code in the Down method.

Step 5: Comment out the code in the “Up” method and delete Down method code

Comment out the code in the Up method, and delete the code in the Down method in this initial migration. Since the Up method is very large, collapse the method and then select it; then just hit the key chord ctrl+k and then ctrl+c to apply the block comment. Once that is complete, uncomment the method declaration and closing brace. Figure 3-10 shows how we can comment the code in the migration to avoid execution when the update-database command is run.

With the migration prepared to run with no effect on our data, let’s feel free to run the update-database command in the PMC and see what happens.

There is no need to fear this command right now. If this operation goes sideways, we can restore from backup.  Please do not do this against a production database until you are certain the results you want will be achieved.

In the PMC, run the command update-database as shown in Figure 3-11.

Step 6: Examine the database

Looking directly at the database, we can now see that a table was added, and we have an entry in it. Notice the command I ran earlier performed an insert into the database table __EFMigrationsHistory. Let’s look at this table in our database. Figure 3-12 shows the database structure after the migration is executed.

Now that we have the data reviewed, we can see how EF knows what migrations to apply in our database. If the migration exists by name in the table (MigrationId column), then the update-database command will not run that migration.

Having the name of a migration in the table prevents execution.  For other developers and production databases, we could simply script out and then add the __EFMigrations table to developer or production databases and then insert the first entry by Id so that the initial migration will never be executed on another database.

Here we see the name of the database migration as generated, which is nothing more than a datetime stamp with the name of the migration as named by us. It would be very easy to script the __EFMigrations table, insert it into any database that is going to work with this project, and insert the first record into the table to prevent the migration from ever being run.

Step 7: Add another migration to see what happens

What happens if we leave the code commented out? Let’s leave the migration as is and then add another migration to find out. Run the command add-migration “testing migrations” to see what is generated. Figure 3-13 shows the expected output when no changes exist, which is what we should see now.

As we can see, this migration is blank. Therefore, we do not need to uncomment our code (in fact, we could remove it) from the first initial migration. With that in mind, we should be ok to push to another machine with the existing database or even our production machine without fear of causing any problems in the future. That being said, it’s always wise to make a quick backup before doing something like this, just in case. If you can’t get a backup due to regulations, space, or other mitigating factors, then you could consider scheduling your initial deployment to run immediately following your next automated backup.

Step 8: Remove the blank migration

In the last step, we added a migration that has nothing in it. Therefore, we should just remove it from our migrations as it is not accomplishing anything. If you noticed, the last statement in the PMC when we ran update-database was “To undo this action, use Remove-Migration.” Let’s go ahead and run that remove-migration command now. The command is shown in Figure 3-14.

In this case, we had not applied the migration. If for some reason you had run the migration that was blank, you would first need to roll back your migration history to the previous migration and then run the remove-migration command. Although we are not covering this in our activity, the command to roll back a migration would be as follows: update-database -migration [name-of-your-migration] command; in this case, it is something like update-database -migration InitialMigration. Please also note the command is different in EF6, where we would specify -target instead of -migration in the command to roll back a migration [update-database -target [name-of-your-migration].

Final thoughts

In this activity, we saw what it takes to get our database set up to work with code-first migrations when the database already exists. We did not cover how to start modifying the database, but we are position to do so.

In the next activity, we’ll start fresh with a new project and a new database, and then we’ll see what it takes to start modifying data in our new database using code-first migrations. Do not fear, the ability to modify data as shown in the next activity would work in exactly the same manner in our existing database project from this point on.

Step 1: Set up and use a new project

To begin from scratch, create a new .Net Core Console project and name the project Activity0302_EFCoreNewDb. Start by opening Visual Studio and selecting Create a new project. Creating a new project is shown in Figure 3-15.

Create a new Console App (.Net Core) project. Selecting a console app is shown in Figure 3-16.

Configure your project to save to a location on your drive that is easy to find; name the project as stated above Activity0302_EFCoreNewDb. Figure 3-17 shows how we can name the project and select the storage location for our code.

The project should be created as expected. Once created, it should look similar to what is shown in Figure 3-18.

Next, we need to add a project to house our database operations. Right-click the Solution and select Add ➤ New Project (see Figure 3-19).

For this project, select Class Library (.Net Core), shown in Figure 3-20.

Name the new project InventoryDatabaseCore and set it to save in the same folder as your other project. Review Figure 3-21 for more information on configuring the project.

Add a reference to the new project in the Activity0302_EFCoreNewDb project by right-clicking the project and selecting Add ➤ Reference (as shown in Figure 3-22).

Select the DB project to reference it in the starter project (see Figure 3-23).

Right-click the Solution and select Manage NuGet Packages for Solution, as shown in Figure 3-24.

Installing NuGet packages is shown in Figure 3-25.

In the end, you should have the following entries as shown in Figure 3-26 in the starter project file.

And the DB project should have the entries as shown in Figure 3-27.

Next, rename the class in the InventoryDatabaseCore project from Class1.cs to InventoryDbContext.cs. When prompted, select Yes to allow renaming (as seen in Figure 3-28).

Figure 3-29 shows what an appsettings.json file should look like with an active connection string and also highlights the two locations to add the new file.

Make sure to set the appsettings.json files as Copy to Output Directory ➤ Copy if newer for the starter project EFCoreNewDb (as shown in Figure 3-30).

Run the program to validate that everything is set up and that your connection string is printed out as expected. Figure 3-31 shows the expected output of the connection string (your output may vary if your connection string is different than mine).

As a final reminder, when adding a lot of code such as we’ve done in this activity in the last two steps, if something goes wrong and you are getting a lot of errors, don’t forget to compare your code to the final version of the files.

As you are already sure your project is working as expected, skip to step 3.

Step 2: Make sure your project is set up correctly

As mentioned previously, the easiest approach for starting this activity is to get the starter files for the activity. Once you have the files, open the project, then make sure the connection string is set to match your local environment database, and finally run the program to make sure it works as expected.

When the program is working as expected, the output should be similar to what is shown in Figure 3-31 at the end of step 2.

Step 3: Add a reusable library for our database models – the “code” of code first

While it is entirely possible to put your code in the same location as the context, it is much more flexible for future use if we separate the models to their own class. Right-click the Solution, and then select Add ➤ New Project. Use the Class Library (.NET Core) project template. Name the new project InventoryModels and save the project in the same directory as your other projects in the solution.

After creation, the project should look as follows in Figure 3-32.

Next, reference the InventoryModels project in the InventoryDatabaseCore Library project. Right-click the InventoryDatabaseCore project, select Add ➤ Reference, and then select the InventoryModels project as a project reference as shown in Figure 3-33.

Step 4: Reference the library in an entity DbSet

This next step is one of the most critical steps in the process. If we forget to do this, our migration will scaffold successfully with nothing to update in the database, creating a blank migration. Therefore, if you run the add-migration command after creating a model, and the migration is blank, consider checking your DbContext to make sure you included a reference to the DBSet.

Adding this line of code will require a using statement to reference the Models project using InventoryModels. See Figure 3-34 for more clarity.

Step 5: Add a new migration

We are now ready to begin creating migrations for our new database. To do this, after ensuring that we have set everything up to this point as covered, we can run the command add-migration “Initial Migration” in the PMC.

Make certain the Default project drop-down is pointing to the InventoryDatabaseCore project. Once this is in place, run the command as shown earlier for the initial migration. Review Figure 3-35 for more clarity.

Note that this generated an error. The error is “Unable to create an object of type ‘yourdbcontext’…”. The error is not very helpful as to what went wrong.

This error is happening because we do not have a default constructor in the context file. Let’s add a default constructor and try again.

Review Figure 3-36 to see the code as placed into the file.

Now that this is fixed, try to add the migration again. Do you think it will work this time?

If you guessed “no,” you get a prize. The prize is yet another error (see Figure 3-37).

The text of this error is actually “No database provider has been configured for this DbContext. A provider can be configured by overriding the DbContext.OnConfiguring method or by using AddDbContext on the application service provider. If AddDbContext is used, then also ensure that your DbContext type accepts a DbContextOptions<TContext> object in its constructor and passes it to the base constructor for DbContext.”

So how do we fix this issue?

As it turns out, we need to do what it says in the error. The easiest solution is simply to override the OnConfiguring method. In fact, if we go back to activity one from this chapter and look at the context that was generated, we’ll see exactly the code that we need. Review Figure 3-38 to see the code from the OnConfiguring method in activity 0301.

A couple of important notes. This code from activity 0301 is exactly the same as the options builder that we are using in our startup project. In this case, however, the startup project was not run, so the context did not get an options builder injected (remember, we added a default constructor that takes no parameters in order to run migrations). Therefore, the context itself needs to know how to connect to the database.

Another important note is the fact that the context is literally begging us to change out the code so that we don’t use a hard-coded connection string. While we could get away with that for now, we really should update it to prevent having our connection string information in code (eventually we’ll need to connect to a production database with something other than the trusted windows login).

Next, let’s remove the direct reference to the connection string. The good news is that we already have an appsettings.json file in place. Now we just need to leverage that from our database context.

Just as we did in the startup, we’ll need to build out the connection string from the builder. If we were using an Asp.Net core application, we could leverage the services and just work through that, but from this console project, it’s a bit trickier.

Also, we can’t reference the startup project static builder that we built, because that would create a circular dependency. So, let’s just rehash the builder code directly.

You’ll also need to reference any missing using statements such as using Microsoft.Extensions.Configuration; and using System.IO; at the top of the file.

Additionally, you’ll need to add a class-level variable before the Main method: static IConfigurationRoot _configuration;

Let’s try that add-migration command one more time (see Figure 3-39).

Step 6: Updating the database

Now that we have an initial migration in place, we are ready to update our local database. Let’s see what happens if we just run the update-database command in the PMC. The successful command execution is shown in Figure 3-40.

There is a chance this will not work for you out of the box. Depending on your local instance and how things are configured, you may need to make sure that you can connect with local windows accounts and/or set to mixed mode if you want to use a SQL Server user id and password.

In some rare instances, you may need to create the database yourself and then run the update-database command . In the end, you should be able to get the update-database command to work as expected.

Examining the database using SQL Server Management Studio (SSMS) proves the database exists as we would expect (see Figure 3-41).

Step 7: Insert and retrieve a set of items

As a final part to this activity, let’s insert and retrieve some items from the Items table in our database. This part of the activity is optional, as we’ll be covering how to work with the database in more detail later in the book. However, I’m a big believer in taking things one step at a time, and this simple insert and retrieve is a good way to get our feet wet.

If you consider yourself to be well versed in EF and LINQ, perhaps step away from the activity and attempt to write the insert and read/write of about five items to and from the Items table.

By doing this, we’ll quickly discover how poor our initial DB design is, as there will be many things we want to enhance about the Items table.

Also note, we are not going to prevent duplicates for now. As stated, we’ll learn more in the future about how to do things correctly against the database.

Run the application. The results should be similar to what you see in Figure 3-42.

Final thoughts

Pre-activity setup

To get started with this activity, it will be easiest to just grab a copy of the starter files Activity0303_EF6_UpdateFromExisting_Starter. Extract the files and work along with me as we build out the solution using EF6 code first.

Step 1: Configure the connection if necessary and run the project

As with other projects, this code is pointed at our restored version of AdventureWorks and should use a similar connection string. Make sure the connection string is set up correctly and run the project. You should see output similar to what is shown in Figure 3-43.

Step 2: Create a new library and add the Entity Framework libraries

As with other projects, we’re going to create a new library and then add the Entity Framework libraries to this project. We’ll also need to add the Entity Framework libraries to the startup project. Begin by adding the new library project and naming it something like AWEFDataAccessLayer (see Figure 3-44).

Next, add the EntityFramework NuGet Package to both the new DAL project and the original startup project as shown in Figure 3-45.

Step 3: Delete the EFMigrations History table from the AdventureWorks database

If you completed the previous activities in this chapter, you should already have an EFMigrations table in the AdventureWorks database. You’ll want to make sure to delete this table before running the wizard in step 4. You can easily delete the table by right-clicking and selecting Delete in the SSMS. Another solution would be to script the table as delete and then execute the script. A third solution would be to restore the backup to a new instance of the AW database and then just point to that new instance in this activity. A final solution would be to just leave it but remove it from the generated context and code after running the wizard in the next step. Figure 3-46 shows how to delete the migrations table.

Step 4: Create the code-first implementation

Thinking back to our EF6 implementation against the existing database from Chapter 2, we had an auto-generated context that was built out for us. With this project, we will not end up with an EDMX file that will generate code for us. Instead, we’re going to have regular model files and a context which we’ll be able to leverage going forward.

To begin, right-click the project and select Add ➤ New Item (see Figure 3-47).

In the Add New Item dialog, on the left navigation pane, select Data. Then select ADO.Net Entity Data Model. Name the model AdventureWorks and select Add. Figure 3-48 shows the Add New Item dialog.

In the Entity Data Model Wizard, select Code First from database (see Figure 3-49).

If you’ve worked through other activities, you likely already have a connection to choose from. If not, you’ll need to create a new connection. If creating the new connection, review the EF6 activity against an existing database from Chapter 2 for more information. Once you have the connection, select it. Name your connection in the App.Config as the default, which is the name of your model. In my case, this is AdventureWorks. There are some slight differences that we’ll want in this connection string, so you can name this connection string anything but AdventureWorksDb, which is the configuration string name for the classic database instance in these activity files. Setting up the configuration string is shown in Figure 3-50.

Once the database connection is set, you’ll get the options for what tables and views to include. Since we’ll be doing an initial migration and we want to ultimately squelch all of this from being re-created, make sure to just select all of the tables and views (see Figure 3-51).

Leave the box to Pluralize or singularize checked, and then select Finish. In the end, you’ll have a DBContext file and a bunch of model files.

Figure 3-52 shows the AdventureWorks DBContext. Make note of the way the connection string is directly passed to this context by name (as opposed to builder options as in EFCore).

Also make note of how the views are referenced in the generated context (see Figure 3-53).

You should also note all of the other models that are defined as expected and the lack of an EDMX file.

Step 5: Enable migrations

Now that we have the database structure defined with models and the DBContext, it’s time to generate the initial migration.

Remember that any time our database models or context have changed, it’s important to rebuild the project and make sure there are no errors. You should have no errors at this point. However, make sure to build now, and then fix any errors if any exist. We cannot generate migrations if the project will not build.

Next, open the PMC, then point to the EFDataAccessLayer project as the default, and run the command add-migration “Initial Migration” to see what happens (review Figure 3-54).

Here, we see there is an error, telling us that no configuration type is found. To fix this, we need to run the command enable-migrations. However, before we do that, we need to consider what we are going to enable. Enabling migrations can be configured to run automatically (on project startup) or can be left as a manual operation (requires intervention). On a new instance, automatic migrations are what we likely want. In this case, however, we need to be extremely careful as automatic migrations may remove or delete objects or data from our existing database. Therefore, we’ll want to make sure automatic migrations are not enabled. We can check and easily change this setting later in the DBContext file.

Run the command enable-migrations in the PMC to see what happens. The output with an error is shown in Figure 3-55.

This error might be particularly confusing. If we look at the app.config file in our library, the connection string is definitely there. However, the config for the console startup project does not have this connection string.

Copy the connection string from the configuration file in the library to the startup project. For convenience, the code is shown first here, and then the placement is shown in Figure 3-56.

<connectionStrings>

  <add name="AdventureWorks"

       connectionString="data source=localhost;initial catalog=AdventureWorks;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"

       providerName="System.Data.SqlClient" />

</connectionStrings>

The placement of the code is shown in Figure 3-57.

If for some reason this section of the config is messed up, try to remove and then reinstall the Entity Framework libraries to the project. That should clear up the config file. Also note that you’ll have two connection strings in the App.config file for the main project as the legacy code connection already existed.

Once again, run the command enable-migrations to see what happens (see Figure 3-58).

With the migrations enabled, we can see that there is a folder “Migrations” in the AWEFDataAccessLayer project. If we look at the configuration file, we can see the line for enabling the migrations right in the constructor (review Figure 3-59).

Note the line for AutomaticMigrationsEnabled = false. If we want to turn automatic migrations on, we can set this option to true. We could have also used that as a flag in the enable-migrations command with the command Enable-Migrations -EnableAutomaticMigrations.

Step 6: Create the initial migration

In some cases, the initial migration may already have been created for you. In our case, it is likely we need to generate it.

Continue to make sure your project builds and that you are in the PMC pointing to the default project of your AWEFDataAccessLayer.

The next part of this activity is only going to happen if you are working against all the data in AdventureWorks or in any other database which includes geography data. I opted to keep this spatial data in for this example, even though referencing geography data will create a number of additional problems for us to solve.

Run the command add-migration “Initial Migration” to see what happens.

If you don’t get an error for spatial types, skip to step 8.

Here, if you do get a geography error, as shown in Figure 3-60, we’ll need to fix it.

This error might happen if you are using an older version of EF6, such as a version less than or equal to version 6.3. This error should not happen on version 6.4+ of EF6.

To fix this error, install the NuGet package Microsoft.SqlServer.Types on the AWEFDataAccessLayer project and the startup console project (see Figure 3-61).

Make sure to rebuild the solution after adding NuGet packages.  Failure to rebuild the solution will not allow the PMC to recognize new NuGet packages.  Once the solution is rebuilt, the package(s) will be recognized in the PMC.

Upon completion, this would look similar to what is shown in Figure 3-62.

Once again, run the command add-migration “Initial Migration” to see what happens. When successful, the new migration should be generated.

Step 8: Comment all “Up” code, delete all “Down” code, update the database

As in the previous activity (activity 0302), we don’t want to re-create all the tables (running would likely error and let us know that the objects already exist). Therefore, we need to clean up our initial migration.

Start by commenting the code for the initial migration’s Up() method. Likely, we will never need or want this code. However, in case the code needs to be restored for some reason, we will still have it (see Figure 3-63).

There should not be a situation where we ever want to run the down code on this migration, as it would be highly destructive, so just remove any code and replace with a comment to note the intentional removal of code (see Figure 3-64).

After cleaning up the initial migration, build the project. Then run the update-database command in the PMC. Figure 3-65 shows the successful update of the database with the new migration.

Validate that the next migration does not require the models again by adding another migration with the command add-migration "test scaffolding" to see what happens (as shown in Figure 3-66).

Since this new migration is blank, we know we are not going to cause any issues, and we can just delete the test migration. Right-click the file and select Delete. If for some reason you had applied this migration, just revert with the command update-database -targetmigration InitialMigration and then delete the test migration.

Step 9: Leverage the new context from the startup app

Now that our context is wired up, we can leverage it from the regular code in our startup application.

Reference the new library project in the console startup application (shown in Figure 3-67).

Note that we’ll need to set a using statement to map Person now, since there is a conflict between our original DAL and the new EF DAL. Assuming all functional UI will rely on the original mappings, we’ll have to map our EF objects back to DAL objects to ensure we don’t break legacy applications. The using statement for Person is as follows: using Person = AWDataLayerObjects.Person;

In case you’ve never seen a using statement assigned to a variable before, review Figure 3-68 to see what the code should look like.

This code should come right before the completion of the program.

Run the program to see it in action. Your output should be similar to the output in Figure 3-69.

Final thoughts

In this final activity, we have worked through creating a new code-first EF6 library against an existing database. Creating against a new database would be a similar process, although creating new EF6 projects is likely something you will not be doing very much of in the future. If a new database and project are being built, you should consider moving to EFCore or .Net vNext (.Net 5+).