Reverse Engineering Existing Databases in Entity Framework Core 2

In my ASP.NET Core 2.0 Pluralsight course, I specifically teach how to build DbContext classes and the POCO classes that go with them. But I’ve been getting many questions about how to work with existing databases, so I thought I’d explain it in a blog post.

I purposely teach the DbContext and POCO classes first because I want the students to understand what is happening. The process of using it with an existing database generates sometimes a large amount of code.

Preparing the Project

Before you get started, you’ll need to make sure the project has some required packages and tools. If you open up your .csproj file, you’ll need to add EntityFrameworkCore.Design and SqlServer (or whatever database you’re using) as references:

  <ItemGroup>
    <PackageReference Include="Microsoft.AspNetCore" Version="2.0.1" />
    <PackageReference Include="Microsoft.AspNetCore.Mvc" Version="2.0.1" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="2.0.1" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="2.0.1" />
  </ItemGroup>

In addition, you need to add a tool reference for EntityFrameworkCore.Tools.DotNet:

  <ItemGroup>
    <DotNetCliToolReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Tools" Version="2.0.1" />
    <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="2.0.1" />
  </ItemGroup>

To make sure this works, open a console and type:

>dotnet ef

It should return with some basic information about the tools for Entity Framework Core:

If you’re getting something similar, then you’re setup correctly.

Scaffolding the DbContext and POCO Classes

The reverse engineering of your database takes the form of scaffolding a context class and all the entity (or POCO classes which stands for Plain Old CLR Objects) classes. Scaffolding just means code generation. It will generate the class files for you in the project.

Before you can scaffold the classes, you’ll need some information about your SQL Server database:

• Connection String to the database
• What Database Engine it’s stored in (probably SQL Server)

With that information, you’ll want to open a console/shell again and use the command for scaffolding. It takes the form of:

dotnet ef dbcontext scaffold

The Connection string should quoted if there are any embedded strings. The DbEngine is the provider for Entity Framework Core (e.g. Microsoft.EntityFrameworkCore.SqlServer). The options change the way you’re going to generate the files. The ones that I think are important are:

• -c: Specifies the name of the DbContext Class
• -o: Specifies the output directory (relative directory)

When you put it all together it looks like this:

> dotnet ef dbcontext scaffold "Server=.;Database=WideWorldImporters;Integrated Security=true;" Microsoft.EntityFrameworkCore.SqlServer -c WorldWideContext -o Data

For every table in the database, it’ll generate a simple class like this (for a table called SupplierCategories):

public partial class SupplierCategories
{
  public SupplierCategories()
  {
    Suppliers = new HashSet<Suppliers>();
  }

  public int SupplierCategoryId { get; set; }
  public string SupplierCategoryName { get; set; }
  public int LastEditedBy { get; set; }
  public DateTime ValidFrom { get; set; }
  public DateTime ValidTo { get; set; }

  public People LastEditedByNavigation { get; set; }
  public ICollection<Suppliers> Suppliers { get; set; }
}

In this case, I’m generating a WorldWideContext class that derives from DbContext and generating all files in a subdirectory called /Data/.

The default behavior is to use the fluent syntax to specify how the mapping to and from your database are:

  public partial class WorldWideContext : DbContext
  {
    public virtual DbSet<Countries> Countries { get; set; }
    ...


    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
     
      ...
      modelBuilder.Entity<Countries>(entity =>
      {
        entity.HasKey(e => e.CountryId);

        entity.ToTable("Countries", "Application");

        entity.HasIndex(e => e.CountryName)
                  .HasName("UQ_Application_Countries_CountryName")
                  .IsUnique();

        entity.HasIndex(e => e.FormalName)
                  .HasName("UQ_Application_Countries_FormalName")
                  .IsUnique();

        entity.Property(e => e.CountryId)
                  .HasColumnName("CountryID")
                  .HasDefaultValueSql("(NEXT VALUE FOR [Sequences].[CountryID])");

        entity.Property(e => e.Continent)
                  .IsRequired()
                  .HasMaxLength(30);

        entity.Property(e => e.CountryName)
                  .IsRequired()
                  .HasMaxLength(60);

        entity.Property(e => e.CountryType).HasMaxLength(20);

        entity.Property(e => e.FormalName)
                  .IsRequired()
                  .HasMaxLength(60);

        entity.Property(e => e.IsoAlpha3Code).HasMaxLength(3);

        entity.Property(e => e.Region)
                  .IsRequired()
                  .HasMaxLength(30);

        entity.Property(e => e.Subregion)
                  .IsRequired()
                  .HasMaxLength(30);

        entity.HasOne(d => d.LastEditedByNavigation)
                  .WithMany(p => p.Countries)
                  .HasForeignKey(d => d.LastEditedBy)
                  .OnDelete(DeleteBehavior.ClientSetNull)
                  .HasConstraintName("FK_Application_Countries_Application_People");
      });

      ...

    }
  }

Once you do this, you’ll have a working DbContext and classes so you can query/add data to the database. But in this case, I’m getting the entire database. It’s a lot of classes. You might want to only Scoffold certain classes.

Not Scaffolding the Entire Database

One option of the Scaffolding is to Scaffold only certain tables. You can do this with the -t option (add a -t with the table name for each Table you want to inlcude:

> dotnet ef dbcontext scaffold ... -t Sales.Orders -t Sales.OrderLines 

Or a complete example:

> dotnet ef dbcontext scaffold "Server=.;Database=WideWorldImporters;Integrated Security=true;" Microsoft.EntityFrameworkCore.SqlServer -c WorldWideContext -o Data -t Sales.Orders -t Sales.OrderLines

Note that if you re-run this you’ll need all the tables you want as it won’t add the tables to an existing DbContext class. So know everything you want to scaffold (or just scaffold it all and delete what you don’t want). Though there is one other option.

Scaffolding By Namespace/Schema

In the database I used for this example, I have a number of tables, but they’re separated into several Schemas (or Namespaces):

Instead of pulling all the classes into one big DbContext class, I’m more likely to just select an entire Schema to scaffold. To do this, I can use the --schema flag. For example, to generate all the tables in the Purchasing namespace:

> dotnet ef dbcontext scaffold ... --schema Purchasing

Or the full version:

> dotnet ef dbcontext scaffold "Server=.;Database=WideWorldImporters;Integrated Security=true;" Microsoft.EntityFrameworkCore.SqlServer -c WorldWideContext -o Data --schema Purchasing

Hope this helps!