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.
Let me show you how to do this for a Company’s Database.
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.
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 –
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 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.
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
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.
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:
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.
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.
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; }
}
}
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
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);
}
}
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: