Database-First approach in Entity Framework Core

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

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

Creating Database in SQL Server

First Create a simple company’s database in your SQL Server and name it Company. Create 2 tables on it and name them:

• 1. Employee
• 2. Department

The id column is both Primary Key and Identity.

The id column is both Primary Key and Identity.

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

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

You can run the below scripts to create these tables on your database.

Department

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

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

DotNet CLI Scaffold Command

Now you can run the DotNet CLI Scaffold Command. On the ‘Package Manager Console’ execute the below command:

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

Note my database server name is given as Server=vaio(it may be different on your case.

Database=Company; is the name of the database.

Trusted_Connection=True; specifies the Windows authentication. It will use Windows credentials to connect to the SQL Server.

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.

Context & Entity Classes

Once the command executes (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.

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 Department Department { get; set; }
}

public partial class Department
{
    public Department()
    {
        Employee = new HashSet<Employee>();
    }

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

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

The context class created is CompanyContext.cs

public partial class CompanyContext : DbContext
{
    public virtual DbSet<Department> Department { get; set; }
    public virtual DbSet<Employee> Employee { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
        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");
        });
    }
}

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