How To Database First With EF Core

Entity Framework Core is one of the most popular Object-Relational Mappers (ORMs) in the market, particularly in the dotnet space.

It provides a simple and quick approach for developers to write queries or commands in C# language, while the EF Core takes up the task of translating it into equivalent SQL syntax and execute on the connected database.

While developing an application from the scratch, we generally employ a “Code-First” approach – meaning we design our entities as C# classes with annotations to specify constraints (like Primary or Foreign Keys, NotNull and so on) and finally use EF Core “migrations” to update database with the schema we designed in our application.

But what if we’re working on an already existing database with a schema and database objects already available? Migrations aren’t an option in this case.

Instead, we try to bring the database schema into our application in the form of equivalent classes and objects. EF Core supports this approach too – it is called as “Database First” approach and is one of the most useful features while working with existing databases.

In this article, let’s try integrating an ASP.NET Core (.NET 6) application with an existing SQL database and understand how “Database First” approach works.

The Problem

In one of my recent assignments, there was a requirement to develop a new application from an already existing database design from an old-gen application. While I had all the tables and data in place, I now need to construct my entity classes exactly as what has been defined in the table structure.

This means that all the column names, their datatypes and any constraints on these tables must be exactly reflected in my entities in order for the application to connect and work with the database. Why? Because ORMs expect the application entities be in perfect sync with the database models, otherwise it doesn’t work.

Solution? Scaffolding

Now I can’t sit and design all the entities in my application – its not outright possible. The database might contain hundreds of tables and other objects, replicating which isn’t that easy and not always 100% possible. Alternative? “database scaffolding” feature of EF Core.

How Scaffolding works?

Entity Framework Core or any other Object-Relational Mapper (ORM) for that matter, creates a “bridge” between the actual database and the application models in a way such that all the queries we write on our application model classes as a part of our logic shall be converted into an actual “query” that is executed on the database.

How does this translation happens? It does by means of the strong coupling between the application model class and the database table structures. This works pretty much well for a new database to be created because all the constraints and structuring we impose on our application models is converted into the database during our first-run. This is what we call the “Model-first” approach and this is what happens when we create these “migrations” in the EF Core.

Now when we have the database already in place, what we need is that the tight coupling between the database tables and their application model classes which reside on the other side. For this, what I felt is to have my database create models for me along with all constraints and keys intact within these models so that I could use them in my application without worrying about whether the scaffolding.

This can be done in three steps using Entity Framework Core tools.

Prerequisites for Scaffolding – EF Tools

The approach first requires three packages to be available in the project where we are to scaffold the database.

Note that these three packages need to be installed only once (in the project), and if already installed you can simply skip this and move to the scaffold command.

1. Install EF Core

Although we can assume we’re already having EF Core available for us; but for starters we would need to have EF Core installed in our development machines to get things started. Starting with dotnet core SDK 3.0 and above, we need to install EF Core as an additional tool on top of the core SDK using the below command.

> dotnet tool install dotnet-ef --global

This installs the EF Core toolkit globally within our machine. To verify the installation, run the below command:

> dotnet ef --info

Note: Keep in mind that this is a one-time command and if already installed, you can move on to the other tools.

2. Install EF Core Designer

This lets us use the design packages from the EF Core tool. This can be done by the below command:

> dotnet add package Microsoft.EntityFrameworkCore.Design

3. Install Database Provider tools

This specific to what database we’re gonna connect to. In this case, we shall use SqlServer and hence the command shall be:

> dotnet add package Microsoft.EntityFrameworkCore.SqlServer

Scaffolding the Database into creating Entities

We can scaffold our database into creating entities in our application in two ways – the Command Line (dotnet-cli) approach or the Visual Studio (Package Manager Console) approach.

Approach 1 – Dotnet CLI in Terminal / Command Prompt

Open a command prompt in the project directory, where you’ve first installed the packages above (Tools, Design and EFCore.SqlServer). In the command prompt use the below command to connect to database and create entities.

> dotnet ef dbcontext scaffold "Data Source=(localdb)MSSQLLocalDB;Initial Catalog=ReadersDB" Microsoft.EntityFrameworkCore.SqlServer --output-dir Models

We specify the database connectionString and where the generated application model classes need to be placed within the project directory, using the output directory argument. Since I’m connecting to an SQL Server database, I specify the Microsoft.EntityFrameworkCore.SqlServer driver. For other databases the driver is specified accordingly.

This command creates a DatabaseContext class which contains all the tables present in the schema.

The tables are available as DbSets of the corresponding entities in the project, which are also auto generated along with their constraints and matching datatypes.

Approach 2 – Visual Studio with Package Manager Console

Alternatively, we can use Package Manager Console from the Visual Studio to generate the models as below:

In Visual Studio window, Tools -> Nuget Package Manager -> Package Manager Console

> Scaffold-DbContext 'Data Source=(localdb)MSSQLLocalDB;Initial Catalog=ReadersDB' Microsoft.EntityFrameworkCore.SqlServer --OutputDir Models

Once these steps are done, what we obtain for a database ReadersDB with two tables Users and Readers with the above command is an auto generated DbContext class as below:

using System;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;

namespace MyApplicationNamespace
{
    public partial class ReadersDBContext : DbContext
    {
        public ReadersDBContext()
        {
        }

        public ReadersDBContext(DbContextOptions<ReadersDBContext> options)
            : base(options)
        {
        }

        public virtual DbSet<Readers> Readers { get; set; }
        public virtual DbSet<Users> Users { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
#warning To protect potentially sensitive information in your connection string, you should move it out of source code. See http://go.microsoft.com/fwlink/?LinkId=723263 for guidance on storing connection strings.
                optionsBuilder.UseSqlServer("Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=ReadersDB");
            }
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Readers>(entity =>
            {
                entity.Property(e => e.Id).ValueGeneratedNever();

                entity.Property(e => e.AddedOn)
                    .HasColumnType("datetime")
                    .HasDefaultValueSql("(getdate())");

                entity.Property(e => e.EmailAddress).HasMaxLength(500);

                entity.Property(e => e.UserName).HasMaxLength(500);

                entity.HasOne(d => d.User)
                    .WithMany(p => p.Readers)
                    .HasForeignKey(d => d.UserId)
                    .HasConstraintName("FK__Readers__UserId__3E52440B");
            });

            modelBuilder.Entity<Users>(entity =>
            {
                entity.Property(e => e.Id).ValueGeneratedNever();

                entity.Property(e => e.AddedOn)
                    .HasColumnType("datetime")
                    .HasDefaultValueSql("(getdate())");

                entity.Property(e => e.Name).HasMaxLength(500);
            });

            OnModelCreatingPartial(modelBuilder);
        }

        partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
    }
}

Database First in a Layered Architecture

This approach works for a simple application. What if my application has N-Layers and I need to run this command? The answer is simple – run the same command within the layer where you intend to place your entities.

The project where the scaffold command runs MUST contain the packages Microsoft.EntityFrameworkCore.Design, Microsoft.EntityFrameworkCore.Tools and the respective Database driver (in this case Microsoft.EntityFrameworkCore.SqlServer).

Scaffolding DbContext and Entities into separate Layers

For example, if I want to place my Database Context in a layer (say ./Migrations) and the Entities need to be placed in another layer (say ./Contracts) I can simply run the scaffold command in my Migrations project where I confirm all the required packages are installed as below:

> dotnet ef dbcontext scaffold --force "Data Source=(localdb)MSSQLLocalDB;Initial Catalog=ReadersDB" Microsoft.EntityFrameworkCore.SqlServer --context DatabaseContext --namespace MyApplicationNamespace.Entities --context-namespace MyApplicationNamespace.Migrations --output-dir ../Contracts/Entities --context-dir .

In the above command, I’m specifying the following things:

1. The Context name must be DatabaseContext – otherwise the generated class name would be on the lines of the database name (ReadersDB will have context name ReadersDBContext)
2. The namespace of the Entities created will be MyApplicationNamespace.Entities – otherwise it defaults to the working project namespace
3. The namespace of the context will be MyApplicationNamespace.Migrations – as we need to place this in the same project
4. The output directory for the entities is in ./Contracts folder – since its a folder relative to the current project directory
5. The output directory for the context is in current directory itself – you can even remove this part, since the default is current directory

Adding new tables into Database with Database First

Now you might be wondering, what if I want to add new table to the database and then use it in the application? Since we’re now using database-first approach, the change first happens in the database which is scaffolded into the code.

It means you need to follow two steps:

1. Add the table into the database and ensure it is now created – the application still works fine since the existing entity mappings aren’t disturbed.
2. Run the Database Scaffold command to get the new table code into the application. If the entities already exist, the command might warn of overwriting. Use –force in the arguments. Ensure that you’re using the SAME command as used before – I’d recommend you commit the command in some script within your codebase.

Once the changes are included into the code, you’ll have the entities recreated – along with the new ones.

Adding changes to existing tables with Database First

Now what if the existing table needs to be modified? Its still possible, but need to be careful.

1. Alter the table in the database as required – now the entity mappings are broken and so the application might break due to not connecting to database.
2. Run the database scaffold command to get the new changes into the application code as mentioned above. Use –force along with the command which is used before, and already saved in the codebase.
3. Deploy the application code, post which the application starts working as usual.

Found this article helpful? Please consider supporting!

Conclusion and.. boilerplate 🥳

Together with LINQ, EF Core provides a simple and powerful alternative for developers who want to do a quick query to the database without all the connectivity and mapping jargon.

Database First approach solves the problem for connecting applications to existing databases via EF Core and opens up quite opportunities for developers to migrate to EFCore from their traditional approaches.

While this works quite quick for simpler applications, the scaffold command offers good customizations for applications built with layered architectures – which might require placing entities in a particular way – as we saw in the example above.

Speaking of layered architectures – ContainerNinja.CleanArchitecture is a good starting point for developers who are looking to get started in the space of building applications with Clean Architecture in mind. The boilerplate solution offers industry-standard best practices such as CQRS, Swagger, AutoMapper, Docker containerization and so on.

The solution is now available – ContainerNinja.CleanArchitecture – GitHub Repository