Adding an Entity Framework Core 5 to an existing database

Entity Framework Core 5 is a light and easy to use ORM, that let you use the database without writing any SQL commands. Built-in mechanisms will translate your LINQ queries on your entity classes to SQL queries and return mapped objects.

Here is an article about adding EF Core with migrations to an empty database: PrimeHotel – adding Entity Framework Core 5 in .NET

Adding an Entity Framework Core 5 is super simple if you have an empty database, but is it that easy when working with a database that has some data inside? Do we need to map it all, or can we just work with a part of the database that interests us? Let’s start from the beginning.

Let’s see what we need

To work with EF Core 5 we need to install NuGet packages:

  • Microsoft.EntityFrameworkCore
  • Microsoft.EntityFrameworkCore.Design
  • Microsoft.EntityFrameworkCore.SqlServer

The last one reveals that we will work with the MS SQL Server database. Now let’s connect to our database and see how it looks like.

I’m using an Azure Data Studio, which is a lightweight and fast tool that can perform most of the basic operations of databases. It’s much faster than SQL Server Management Studio and now for most of my work, I just use the first one.

We will add EF Core for aspnetcore database, which looks like this.

We need a DbContext

The next thing we need to do is to create our DbContext. We could create it by hand and type everything manually. However, .NET Core has tools to scaffold that and generate it for us.

The process is called reverse engineering and it is scaffolding entity type classes and a DbContext class based on a database schema. To perform this operation we will use the .NET CLI tool, that you would have to install if you haven’t done it already. Type this command to check it out:

dotnet tool install --global dotnet-ef

You can also update the tool, once installed:

The scaffolding process needs a connection string to pass. We could pass it in our command, but we can also accomplish this task in a more elegant way by passing only its name.

Let’s go to the appsettings.json file and set up a connection string for our new database.

I’ve added a connection string with the name aspnetcore in ConnectionStrings section. 

The command that we are going to use is very simple, run it in your project directory:

dotnet ef dbcontext scaffold Name=aspnetcore Microsoft.EntityFrameworkCore.SqlServer

This command will add aspnetcoreDbContext and all entities representing your database.

However, it would be nice to have some more control over the process.

Customize the process to your needs

Thankfully, there are more parameters that we can use. Let’s take a look at some of them:

  • --table can be used to include specific tables
  • --use-database-names option will preserve the original database names as much as possible. However, invalid .NET identifiers will still be fixed
  • --context can be used to give generated DbContext your own name
  • --context-dir can be used to scaffold the DbContext class to a specific directory
  • --output-dir can be used to scaffold entity classes to a specific directory
  • --force will override the existing DbContext class and entity classes

I’m going to modify my command, so it will look like this:

dotnet ef dbcontext scaffold Name=aspnetcore --table Profiles --table Events
  --context AspNetCoreDbContext --context-dir AspNetCoreModels 
  --output-dir AspNetCoreModels Microsoft.EntityFrameworkCore.SqlServer

Let’s see what classes were generated.

Notice that only tables Events and Profiles were generated, DbContext class is named AspNetCoreCotext and all was generated in AspNetCoreModels directory. Awesome! 

The limitations

Reverse engineering does a tremendous job of scaffolding entity classes so that we don’t need to write it on our own. However, there are some limitations to this process:

  • not everything about the model is presented in the database schema. For example inheritance hierarchies, owned types and table splitting will not be reverse-engineered
  • also, EF Core documentation claims, that there are some column types that will not be included in the model
  • nullable types will not be mapped as nullable. For example, string columns that can be null, will not be scaffolded as string? type. You will have to edit it yourself

More of that you can read in this Microsoft article.

Updating the model

Whenever something changes in the database, you would need to update your model. Most of the changes will be trivial, like adding a column to the table, renaming a table name, or change columns type. Those changes can be quickly applied in the entity classes manually. 

However, if you’re not sure how to map your changes, you can always regenerate the whole DbContext with all entity classes. To do this, use --force parameter in the scaffold command. Note, however, that all changes done manually, will be overwritten. Currently, there is no option to update model from database schema and preserve manual changes.

Updating the database

Scaffolding a DbContext is just a way to generate classes, that match the database. This means that you can add migrations even to an existing database.

First, you would need to add AspNetCoreDbContext to your DI container. Go to the Startup.cs file and in ConfigureServices add the following line.

    services.AddDbContext<AspNetCoreDbContext>(options =>
        options.UseSqlServer(Configuration.GetConnectionString("aspnetcore")));

Now you can add migrations to the second database. It’s a good practice to keep models and migrations separate for every database. Execute this command:

dotnet ef migrations add InitialCreate --context AspNetCoreDbContext --output-dir Migrations/AspNetCore

Here is what was generated:

There’s one thing worth noticing. Entity Framework Core generated initial migration with all changes that are currently in the AspNetCoreDbContext. You might need to clear all changes in that migration because those tables already exist.

Summary

When adding an Entity Framework Core 5 to an existing database, a good idea is to scaffold entity classes and context with a dedicated .NET CLI tool. You have a handful of parameters that you can provide and scaffold exactly what you need and how you need it.

You can add EF Core 5 migrations later on for keeping your database up to date with your code. One thing that you should keep in mind is that updating DbContext from the database again will override all changes that you did manually. Because of this, scaffolding DbContext is more of a one-time thing.

All code posted here was applied to a PrimeHotel project, that is available at my GitHub, so you can download it freely and experiment. Take a look also at this post on how to run it: PrimeHotel – how to run this project.

Hope you liked the post, cheers!

 

 

5 thoughts on “Adding an Entity Framework Core 5 to an existing database

  1. Erwan

    Hi Michał,
    Thank you for your blog, it helped me more that once 🙂

    I had a question for you about Scaffolding…
    I just scaffolded my (new and empty) database, and the Many to Many joins have not been detected.
    The table between is just composed by the 2 FKs fields, which themselves compose its PK… The usual…
    I did not find anything on the subject, do you know if it is a known limitation ?

    I used the 2 last release candidates of EF Core 5.

    Thanks a lot !
    Erwan

    Reply
    1. Michał Białecki Post author

      Hi Erwan, thanks for the kind words!
      I tried that as well, on a PrimeHotel DB and it worked partially. Whole project is here: https://github.com/mikuam/PrimeHotel.
      I tried with both EF Core 5 RC2 packages installed and a dotnet-ef global tool RC2 and I managed to scaffold many-to-many relation, but with a joined table mapping. In EF Core 5 with Code First approach, the joined table is not needed anymore, but probably a global tool is not updated yet. Of course the joined table will exist in the database anyway, but it doesn’t need to be visible in the project.

      Reply
  2. Gary

    This is great, thank you so much for sharing this brilliant information. I’ve been working in SQL for a good few years and I’m just starting this Entity Framework Core journey.
    Looking forward to using this resource to learn more!!
    I’d buy you a beer if I could!

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *