Reverse Engineering Existing Databases with Entity Framework Core
While many developers would prefer to work on green-field projects, many of us have to work with existing systems, also known as brown-field projects. Existing projects have databases managed by database administrators and relied on by business stakeholders. So what is a .NET developer to do when tasked with integrating and extending existing databases in their Entity Framework Core projects?
In this short post, we’ll explore the process of reverse engineering an existing SQL Server database and how you might apply database migrations to a current schema. Let’s get started.
Step 0: The Existing Database Schema
It’s not uncommon for organizations to ask you to work with existing databases, especially in the .NET enterprise space, where database administrators are essential to a software development team. These databases have evolved over the years to store business information and are likely non-negotiable as part of a new software development project.
Don’t worry. You can still use the latest and greatest Entity Framework Core, but you’ll need to rethink your approach. Rather than starting with a clean slate and a Code First approach, you’ll generate models and relationships by introspecting an existing schema.
For this tutorial, let’s use the famous Northwind database schema, which has tables, relationships, and views. However, before continuing to follow this tutorial, you’ll need to create a new Northwind database that you can access with a connection string. You can find a copy of the Northwind SQL script in this GitHub Repository.
Step 1: Install Entity Framework Core Dotnet Tool
Let’s start by installing all the EF Core CLI tooling and the necessary NuGet dependencies in our Console Application. First, open up the terminal at the solution root and type the following commands.
> dotnet new tool-manifest > dotnet tool install dotnet-ef
These commands will install the EF Core CLI tooling to your solution and allow you to access the dotnet ef command line actions.
Tip: By adding a tools manifest, all dotnet CLI tools are targeted at the current solution, allowing you to use different versions in different solutions on your system. No need for global tools!
The next step is to install the EF Core NuGet dependencies. You can use JetBrains Rider’s NuGet tool window to search for the following packages: Microsoft.EntityFrameworkCore.SqlServer and Microsoft.EntityFrameworkCore.Design. Your project file should look like this after installation.
<Project Sdk="Microsoft.NET.Sdk">
<PropertyGroup>
<OutputType>Exe</OutputType>
<TargetFramework>net7.0</TargetFramework>
<ImplicitUsings>enable</ImplicitUsings>
<Nullable>enable</Nullable>
</PropertyGroup>
<ItemGroup>
<PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="7.0.3">
<PrivateAssets>all</PrivateAssets>
<IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
</PackageReference>
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="7.0.3" />
</ItemGroup>
</Project>
The design package allows EF Core tooling to scan a schema and translate it into C# objects. If you forget to include the design package in your project dependencies, you will get errors.
Step 2: Scaffold The EF Core Model
The command you’ll be running is dotnet ef dbcontext scaffold. This command has a lot of flags but only two required arguments: Connection and Provider. The first argument is the database’s connection string you hope to reverse engineer. The second is the EF Core database provider; in this case, you’ll use Microsoft.EntityFrameworkCore.SqlServer. If you have another database provider, feel free to substitute the package name.
dotnet ef dbcontext scaffold "Data Source=localhost,11433;Initial Catalog=Northwind;User Id=sa;Password=Pass123\!;Encrypt=false" Microsoft.EntityFrameworkCore.SqlServer --project Northwind -o Models
The above command uses the -o flag to add all generated classes to a “Models” folder at the root of your project.
Once you’ve run the command, you’ll notice 28 new classes in your project, the most crucial being NorthwindContext, your database-specific implementation of DbContext.
You’ll notice that all the views and tables were created, along with related collections inside of entities. So, for example, here’s the Territory table in C#.
public partial class Territory
{
public string TerritoryId { get; set; } = null!;
public string TerritoryDescription { get; set; } = null!;
public int RegionId { get; set; }
public virtual Region Region { get; set; } = null!;
public virtual ICollection<Employee> Employees { get; } = new List<Employee>();
}
All the classes use the partial keyword to allow you to extend the models and will enable you to rerun the reverse engineering process in the future.
Note: You will get a warning about sensitive information in your DbContext. Move your connection string out to your configuration provider rather than keeping it hard coded.
You can now write queries against your existing database.
using Northwind.Models;
var db = new NorthwindContext();
var count = db.Customers.Count();
Console.WriteLine($"Customer count: {count}");
var customer = db.Customers.First();
Console.WriteLine($"The first customer is {customer.ContactName} ({customer.CustomerId})");
var products = db.ProductsAboveAveragePrices
.OrderBy(x => x.UnitPrice)
.ToList();
products.ForEach(p => {
Console.WriteLine($"{p.ProductName} ({p.UnitPrice:$00.00})");
});
Step 3: Adding Database Migrations
This step is for folks responsible for maintaining and expanding an existing database. If you already have database administrators, you can skip this section. However, if the schema is your responsibility, consider using EF Core’s migrations to evolve from the starting point.
First, you’ll need to run the following command.
> dotnet ef migrations add Initial --project Northwind
This command will create a new “Migrations” folder, a migration file, and NorthwindContextModelSnapshot. Next, you’ll want to go into the “Initial” migration and clear both the Up and Down methods of their content.
using System;
using Microsoft.EntityFrameworkCore.Migrations;
#nullable disable
namespace Northwind.Migrations
{
/// <inheritdoc />
public partial class Initial : Migration
{
/// <inheritdoc />
protected override void Up(MigrationBuilder migrationBuilder)
{
}
/// <inheritdoc />
protected override void Down(MigrationBuilder migrationBuilder)
{
}
}
}
You can run the following command to initialize your database’s __EFMigrationsHistory table, which keeps track of existing database migrations.
> dotnet ef database update --project Northwind
You can evolve the schema using Code First changes and add additional migrations to the existing schema.
Summary
You’ll likely need to develop on top of or extend existing databases in the Enterprise world. With EF Core’s reverse engineering tools, you can avoid the complexity of modeling a large database schema by hand and trust what EF Core sees in your database. However, reverse engineering has some limitations, including logical concepts like table inheritance, owned types, and table splitting. I recommend testing any reverse-engineered DbContext and ensuring generated queries work with your schema. Ultimately, reverse engineering is a great place to start for many developers and can save many work hours.
We hope you enjoyed this post, and feel free to leave comments and questions in the section below.
References
- Reverse engineering EF Core: https://learn.microsoft.com/en-us/ef/core/managing-schemas/scaffolding/?tabs=dotnet-core-cli
- Image Credit: Hugo Delauney
Subscribe to a monthly digest curated from the .NET Tools blog:
