Using EF Core to Scaffold DbContext and Models from Existing Database Tables

Introduction

Scaffolding in EF Core is the process of generating code (like classes) for existing databases so we can use it in your .NET Core application. This helps interact with the database using C# code instead of writing SQL queries. We can accomplish this using either the Scaffold-DbContext command in the EF Core Package Manager Console (PMC) tools or the dotnet ef dbcontext scaffold command in the .NET Command-line Interface (CLI) tools.

Need

EF Core does not offer a visual designer for database models or a wizard for creating entity and context classes like EF 6 does. Instead, we need to perform reverse engineering using the Scaffold-DbContext command. This command generates entity and context classes (derived from DbContext) based on the schema of an existing database. Creating entity & context classes for an existing database is also called the Database-First approach.

Steps to Scaffold Your Database

1. Install Required Tools and Packages

First, ensure you have all the necessary tools and packages.

.NET Core SDK: Install .NET Core on your computer. You can download it from the official .NET website.
Entity Framework Core Packages: Add these packages to your project so EF Core can work with SQL Server and generate code.

Open your project in Visual Studio, navigate to the NuGet Package Manager Console (Tools > NuGet Package Manager > Package Manager Console), and run the following commands to install the required packages.

Install-Package Microsoft.EntityFrameworkCore.SqlServer
Install-Package Microsoft.EntityFrameworkCore.Design
Install-Package Microsoft.EntityFrameworkCore.Tools

2. Add Connection String

Both the PMC and the .NET CLI commands have two required arguments: the connection string to the database, and the EF Core database provider to use. There are other optional parameters that we will see example below. But for demo purposes, we will only see how we can execute the command in PMC.

The first argument to the command is a connection string to the database and the other parameters are described as follows. Open the appsettings.json file in your project and get database connection details. The tools will use this connection string to read the database schema. Open the Package Manager Console in Visual Studio (Tools > NuGet Package Manager > Package Manager Console) and run the following command.

Scaffold-DbContext "Server=my_server;Database=my_database;User Id=my_user;Password=my_password;" `
                   Microsoft.EntityFrameworkCore.SqlServer `
                   -OutputDir Models `
                   -UseDatabaseNames `
                   -Tables "my_table" `
                   -Context MyDbContext `
                   -NoPluralize

Let's look at each parameter what it is and its functionality.

Parameter	Description	Importance
Connection String	"Server=my_server;Database=my_database;User Id=my_user;Password=my_password;"	Provides connection details to your SQL Server database, including server, database, and credentials.
Microsoft.EntityFrameworkCore.SqlServer	Specifies the database provider.	Ensures compatibility with SQL Server databases.
OutputDir Models	Specifies the directory for generated classes.	Organizes generated code by placing it in a specific folder.
UseDatabaseNames	Ensures exact usage of table and column names from the database.	Maintains consistency with the database schema by preventing modifications to names.
Tables "my_table"	Specifies the table(s) to scaffold.	Target specific tables, optimizing scaffolding by excluding unnecessary tables.
Context MyDbContext	Names the generated DbContext class.	Defines the name for the context class that links the application to the database.
NoPluralize	Disables pluralization of table names.	Retains original table names without automatically modifying them (e.g., Product remains Product).

Once you run the above command, EF Core will create two main things for you. It's going to generate two classes.

DbContext Class: This class serves as a connection between your application code and the database. It includes properties that represent the tables in your database.
Entity Classes: These classes model the tables in your database, with each class containing properties that correspond to the columns of the respective table.

Example

If your database has a table called my_table with columns ProductID, ProductName, and Price, EF Core will generate a class my_table.cs like this.

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace YourNamespace.Models
{
    [Table("Product")]
    public partial class Product
    {
        [Key]
        [Column("ProductID")]
        public int ProductID { get; set; }
        
        [Required]
        [StringLength(50)]
        [Column("ProductName")]
        public string ProductName { get; set; }
        
        [Column("Price")]
        public decimal? Price { get; set; }
    }
}

And a DbContext class named MyDbContext.cs like this.

using Microsoft.EntityFrameworkCore;
namespace YourNamespace.Models
{
    public partial class YourDbContextName : DbContext
    {
        public YourDbContextName(DbContextOptions<YourDbContextName> options)
            : base(options)
        {
        }
        public virtual DbSet<Product> Products { get; set; }
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Product>(entity =>
            {
                entity.HasKey(e => e.ProductID);
                entity.Property(e => e.ProductName).IsRequired().HasMaxLength(50);
                entity.Property(e => e.Price);
            });
        }
    }
}

That's it. We will see those classes under our .NET Core project. Now we can utilize these files to write REST queries to interact with the database's table.

Conclusion

In this article, we have seen how Scaffolding in EF Core generates the code needed to interact with your existing database in a .NET Core application. By following these steps, you can easily set up your project to use your database with Entity Framework Core.