We use cookies to provide you with a great user experience, analyze traffic and serve targeted promotions.   Learn More   Accept
Card image cap

Database First - Generating Models from an Existing Database using EF Core

ASP.NET Core  • Posted 2 months ago

While developing applications with EF Core are often associated with building models in line with the use cases being developed and then transforming these entity models into actual database tables; what we call generally a "Model-first" approach. And then there are scenarios where we might need to connect to an already existing database as a part of application upgrades or some other scenarios. In such cases, we might have these "loose-ends" in successfully scaffolding the entity models with equivalent table schemas. Or in a better approach; let the existing database be used to "create" models for the applications and then use these model classes for our application logic development. This is what we called as a "Database-first" approach and we can do so using the tools provided by the Entity Framework Core.

Setting up the Context:

In one of my recent assignments, there was a requirement to develop a new application from an already frozen database design from an older application. This means that while I had all the tables and sample data in place facilitating my development; it also meant I need to construct my entity classes exactly as what has been defined in the table structure. This meant that all the column names, their datatypes and any constraints on these tables must be exactly reciprocated within my entity models in order to create a "perfect scaffolding" between the database and the applications using the ORM: in my case was Entity Framework 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. And 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.

1. Prerequisite - 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.

2. Install the 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 the 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 --version 3.1.3

4. Finally - Scaffold the Database into create Models:

With this command we shall specify what database we're gonna connect to by specifying the connectionString and where the generated application model classes shall reside by using the output directory. We can achieve this by the below command:


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

By using this command, we can create models from the existing database into model classes. This command also create a DatabaseContext class which contains all the tables present in the schema represented as DbSets of the corresponding entity classes in the project; which are also auto generated along with their constraints and matching datatypes.

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 dotnetcore3app
{
    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);
    }
}

While the other models are also created inline with the context. This way we can achieve the "Database-first" approach using EF Core.