The technique database first allows us to take an existing database, and from it generate all the models and DbContext to be able to use Entity Framework Code with said database. This is ideal when you have a database already created, and you want to use Entity Framework Core with it.

We have to alternatives to use database first with Entity Framework Core 2. The first alternative is to generate models and the DbContext, and then activate migrations, this means that we will be able to make changes on our database through C# code, and we’ll keep a log of all the changes that we do from now on our database. The second alternative is that, after using database first, we do not activate migrations, but simply update the models and DbContext to that they become in sync with the database, in case the database has changed. Let’s see in details this two ways, but first, let’s use database first in Entity Framework Core 2.0.

In this post, we’ll work with Visual Studio 2017, Entity Framework Core 2.0 and SQL Server, though nothing stops you from using other database engines, like MySQL.

Doing Database First

The first thing we need is a .net project. I’ll use a .net core 2 web application, but any project that uses .net 4.6.1+, and hence, supports Entity Framework Core 2 will suffice.

In order to be able to do database first we need a database. Then we’ll use Entity Framework tooling to generate the models and dbContext that work with our database. For this we’ll use the Scaffold-DbContext command.

Scaffold-DbContext

With the Scaffold-DbContext command we can do database first. This command allows us to use an existing database and generate the models that reflect its tables and the DbContext with the proper configurations. The minimum that we need to use this command is:

• The connectionString to connect to our database
• The provider for our database engine. We’ll use SQL Server standard’s provider

Scaffold-DbContext have other parameters that we can use. One of them is to set the directory in which the models and DbContext will be placed. Another parameter is to indicate which tables of the database you want to generate. By defect, all tables are generated. We will generate all the tables as models and will put the C# classes in the Models folder.

In the end, the command will look like this:

Scaffold-DbContext “Data Source=MY_SERVER;Initial Catalog=MY_DATABASE;Integrated Security=True” Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

You have to put this command in the Package Manager Console. In Visual Studio 2017, you can find this window in Tools > Nuget Package Manager > Package Manager Console.

Let’s explain the command we just did. After Scaffold-DbContext we put the connectionString. After the connectionString, we placed the SQL Server’s provider. At last, we used the -OutputDir parameter to indicate that we want to place the files on the Models folder.

With this, we have used database first, nonetheless, this is rarely the end, there’ll be occasions in which you’ll want to make changes to the database, perhaps in the C# (e.g.: adding new tables, adding new columns, etc.); or whether you update the database through SQL Server Management Studio, and you need that your C# code reflects those changes. This are the two ways to follow after doing database first. We’ll see how to implement each.

The First Way – Activating Migrations

If after doing the database first you want to activate migrations, because:

• You want to make changes to your database using Entity Framework;
• You want to keep a history of changes to the database

Then you should follow this way.

You just have to follow these steps to correctly configure migrations in this scenario:

Step 1 – Go to Package Manager Console.

Step 2 – Use the command “Add-Migration Initial”

Step 3 – Use the command “Script-Migration“. This will generate a SQL script with the changes of the migration.

Step 4 – Copy the Create Table de __EFMigrationHistory and the Insert statement on that same table. The code that you are looking for is like this:

IF OBJECT_ID(N'__EFMigrationsHistory') IS NULL
BEGIN
CREATE TABLE [__EFMigrationsHistory] (
[MigrationId] nvarchar(150) NOT NULL,
[ProductVersion] nvarchar(32) NOT NULL,
CONSTRAINT [PK___EFMigrationsHistory] PRIMARY KEY ([MigrationId])
);
END;

GO

INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
VALUES (N'20180322234456_inicial', N'2.0.1-rtm-125'); /*This values may be different in your case*/

GO

Step 5 – Paste this queries in SQL Server Management Studio

Step 6 – Execute them against your database. This will make Entity Framework think that the “Initial” migration has been applied.

With this done, migrations are activated and you can continue with your project as if it were using Code First, that means that you can make changes to your models and DbContext, and then you can use migrations to keep a log of those changes, and push them to the database with the Update-Database command.

If this isn’t exactly what you want, if you don’t want to use migrations, but that your project just reflect the changes in the database in another manner, then the second way you shall take.

The Second Way – Updating models and DbContext without Migrations

If after doing database first you want that your models and DbContext reflect new changes of the database, then you have at least two ways to proceed:

a) You can make the changes by hand in your models. For example, if you added a new column to a table, then you can go to its correspondent model, and add the appropriate property. This requires you to know how to make the adequate configurations in Entity Framework (like string length annotations, or fluent API configurations). The advantage of doing it like this is that, if there are few changes, then you can do them quickly. The disadvantage is that it can become tedious, and you have be careful, otherwise you may get an exception at execution time.

b) You can do the Scaffold-DbContext command again to bring the new changes to your project. The advantage of this is that it’s easy. The disadvantage is that you lose any changes that you may have done to your models and to the DbContext. For example, if from the DbContext you refactored the connectionString so it is placed in a config file, then when you use the Scaffold-DbContext command again you’ll lose that change, and you’ll have to do it again.

The disadvantages may not be a huge deal, because databases tend to be relatively stable after some time.

Conclusion

When we do Database First to generate the DbContext and models from a Database so that we can use Entity Framework, we have several choices to work with afterward. One of them is to activate migrations and do the changes of your database through code, using Code First. The other choice is to make the changes in the database directly, and then update your models and DbContext either by hand, or by running the Scaffold-DbContext command.