Database-First approach in Entity Framework Core

Last Updated: August 14, 2020

In Database-First approach the entity and context classes are automatically created by the EF Core from the database. So this means you have to first create your database for the EF Core.

This tutorial is a part of Entity Framework Core series.

Let me show you how to do this for a Company’s Database.

Creating Database in SQL Server

You must have SQL Server installed in your Local PC in order to work with EF Core. There are 2 ways to do so, choose any one from them.

1. SQL Server 2019 – there is 180 days evaluation time for new users which is enough for beginners to learn implementation of EF Core. After 180 days time you will have to buy the license.
2. SQL Server Express LocalDB – this installation copies a minimal set of files necessary to start the SQL Server Database Engine and is best for beginners to learn codes. It is completely free to use.

If you have installed SQL Server 2019 then you can open the SQL Server Management Studio from the Start menu of Windows. Else if you have SQL Server Express LocalDB then open it from Visual Studio directly, go to View ➤ SQL Sever Object Explorer in VS.

So first create a simple company’s database in your SQL Server and name it Company. Create 2 tables on it and name them as –

1. Employee
2. Department

Important ASP.NET Core tutorial based on your interest – Calling ASP.NET Core Web APIs with JavaScript and performing CRUD operations

The Employee Table is shown below:

The id column is both Primary Key and Identity.

The Department Table is shown below:

The id column is both Primary Key and Identity.

Relationship between Tables

There is One-to-Many relationship between the Employee & Department table. This means one department can have multiple employees.

Here I have created the DepartmentId column of Employee table as the foreign key for the Id column of the Department table.

Scripts of the Tables

You can run the below scripts on the Query Window of your Company’s SQL database to create these tables.

Department table

CREATE TABLE [dbo].[Department](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Employee table

CREATE TABLE [dbo].[Employee](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [DepartmentId] [int] NOT NULL,
    [Name] [varchar](100) NOT NULL,
    [Designation] [varchar](25) NOT NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
SET ANSI_PADDING OFF
GO
 
ALTER TABLE [dbo].[Employee]  WITH CHECK ADD  CONSTRAINT [FK_Employee_Department] FOREIGN KEY([DepartmentId])
REFERENCES [dbo].[Department] ([Id])
GO
 
ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [FK_Employee_Department]
GO

.NET Core command-line interface (CLI) Scaffold Command

Now you run the CLI Scaffold Command on the Package Manager Console window. You can open this window from Tools ➤ NuGet Package Manager ➤ Package Manager Console in Visual Studio.

Before running the command make sure you have Install EF Core Tools installed in your project. I have covered this on Installation of Entity Framework Core tutorial.

The command to run is shown below.

PM> dotnet ef dbcontext scaffold "Server=vaio;Database=Company;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -o Models

Note the following things:

Inside the quotation marks (“”) is my DB Connection String which is Server=vaio;Database=Company;Trusted_Connection=True;. It may be different on your case
The Database server name is given as Server=vaio.
Database=Company is the name of the database.
Trusted_Connection=True specifies the Windows authentication i.e. it will use Windows credentials to connect to the SQL Server. On live servers you use SQL Server Authentication which has an SQL user name and password.
Microsoft.EntityFrameworkCore.SqlServer; is the name of the provider for SQL Server.
-o Models refers to the directory name where all the classes will be generated. Here it is the Models folder.

How to find Database Connection String

There is an easy way to find the Database Connection String. Open SQL Sever Object Explorer then right click on the database to open the Properties Window. In the properties window you will find the Connection String field. Simply copy it’s value from there. I have shown this in the below video.

Context & Entity Classes

After the command has finished executing (usually takes 15 to 20 seconds), you will see the context & entity classes created inside the Models folder.

The 2 entity Classes created are the Employee.cs & Department.cs.

Employee.cs

using System;
using System.Collections.Generic;

namespace DatabaseFirst.Models
{
    public partial class Employee
    {
        public int Id { get; set; }
        public int DepartmentId { get; set; }
        public string Name { get; set; }
        public string Designation { get; set; }

        public virtual Department Department { get; set; }
    }
}

Department.cs

using System;
using System.Collections.Generic;

namespace DatabaseFirst.Models
{
    public partial class Department
    {
        public Department()
        {
            Employee = new HashSet<Employee>();
        }

        public int Id { get; set; }
        public string Name { get; set; }

        public virtual ICollection<Employee> Employee { get; set; }
    }
}

The context class created is CompanyContext.cs

CompanyContext.cs

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

namespace DatabaseFirst.Models
{
    public partial class CompanyContext : DbContext
    {
        public CompanyContext()
        {
        }

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

        public virtual DbSet<Department> Department { get; set; }
        public virtual DbSet<Employee> Employee { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                #warning To protect potentially sensitive information in your connection string
                optionsBuilder.UseSqlServer("Server=vaio;Database=Company;Trusted_Connection=True;");
            }
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Department>(entity =>
            {
                entity.Property(e => e.Name)
                    .IsRequired()
                    .HasMaxLength(50)
                    .IsUnicode(false);
            });

            modelBuilder.Entity<Employee>(entity =>
            {
                entity.Property(e => e.Designation)
                    .IsRequired()
                    .HasMaxLength(25)
                    .IsUnicode(false);

                entity.Property(e => e.Name)
                    .IsRequired()
                    .HasMaxLength(100)
                    .IsUnicode(false);

                entity.HasOne(d => d.Department)
                    .WithMany(p => p.Employee)
                    .HasForeignKey(d => d.DepartmentId)
                    .OnDelete(DeleteBehavior.ClientSetNull)
                    .HasConstraintName("FK_Employee_Department");
            });

            OnModelCreatingPartial(modelBuilder);
        }

        partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
    }
}

Scaffold-DbContext Command

You can skip DotNet CLI Scaffold Command, and use Scaffold-DbContext Command for creating context & entity classes from a database.

This will do the exact same thing.

Run the following command on the Package Manager Console.

PM> Scaffold-DbContext "Server=vaio;Database=Company;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

Download the source codes:

DOWNLOAD

Next tutorial – DbContext Class in Entity Framework Core

Database-First approach in Entity Framework Core

This tutorial is a part of Entity Framework Core series.

Creating Database in SQL Server

.NET Core command-line interface (CLI) Scaffold Command

Context & Entity Classes

Scaffold-DbContext Command

Share this article -

You may also like to read these -

ABOUT THE AUTHOR

EF Core Tutorial

YOGIHOSTING

SUBSCRIBE TO NEWSLETTER

MOST READ TOPICS