Creating model with Existing Database in Entity Framework Core
Here you will learn how to create the context and entity classes for an existing database in Entity Framework Core. Creating entity & context classes for an existing database is called Database-First approach.
EF Core does not support visual designer for DB model and wizard to create the entity and context classes similar to EF 6. So, we need to do reverse engineering using the Scaffold-DbContext command. This reverse engineering command creates entity and context classes (by deriving DbContext) based on the schema of the existing database.
Let's create entity and context classes for the following EmpDB (employee DB) database in the local MS SQL Server shown below.
CREATE DATABASE EmpDB
GO
USE EmpDB
GO
CREATE TABLE tblEmployee
(
ID INT IDENTITY(1,1) PRIMARY KEY,
FirstName NVARCHAR(50) NOT NULL,
LastName NVARCHAR(50) DEFAULT '' NOT NULL,
EmailID NVARCHAR(100) NOT NULL,
Password NVARCHAR(100) NOT NULL,
IsActive BIT DEFAULT 1 NOT NULL,
CreatedAt DATETIME NOT NULL,
CreatedBy INT REFERENCES tblEmployee(ID),
)
GO
CREATE TABLE tblEmployeeDetail
(
ID INT IDENTITY(1,1) PRIMARY KEY,
EmpID INT REFERENCES tblEmployee(ID) UNIQUE NOT NULL,
JoinDate DATETIME NOT NULL,
Qualification NVARCHAR(50) NOT NULL,
Team NVARCHAR(50) NOT NULL,
Salary DECIMAL(10) NOT NULL,
CreatedAt DATETIME NOT NULL,
CreatedBy INT REFERENCES tblEmployee(ID),
UpdateBy INT REFERENCES tblEmployee(ID),
UpdateAt DATETIME
)
Scaffold-DbContext Command
Use Scaffold-DbContext to create a model based on your existing database. The following parameters can be specified with Scaffold-DbContext in Package Manager Console:
Scaffold-DbContext [-Connection] [-Provider] [-OutputDir] [-Context] [-Schemas>] [-Tables>]
[-DataAnnotations] [-Force] [-Project] [-StartupProject] [<CommonParameters>]
In Visual Studio, select menu Tools -> NuGet Package Manger -> Package Manger Console and run the following command:
PM> Scaffold-DbContext "Data Source=.\SQLEXPRESS;Initial Catalog=EmpDB;Persist Security Info=True;User ID=sa;Password=XXXXXXX" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -ContextDir DbContexts -Context EmpDBContext -force In the above command, the first parameter is a connection string which includes three parts: DB Server, database name and security info.
Here,
- Data Source= .\SQLEXPRESS -> Refers to local SQLEXPRESS database server.
- Initial Catalog=EmpDB; -> Specifies the database name "EmpDB" for which we are going to create classes
- Persist Security Info=True -> Specifies the Windows authentication. It will use Windows credentials to connect to the SQL Server.
- User ID; Password -> Represents the SQL server user id and password
- Microsoft.EntityFrameworkCore. SqlServer -> We use provider for the SQL Server
- -OutputDir -> Parameter specifies the directory where we want to generate all the classes which is the Models folder in this case.
- -ContextDir -> Parameter specifies the directory where we want to generate db context class which is the DbContexts in this case.
- -Context -> Parameter specifies the class name of the DBContext, which we will all the place to create db object
- -force -> force will help to update the DB classes
Use the following command to get the detailed help on Scaffold-DbContext command:
PM> get-help scaffold-dbcontext –detailed
The above Scaffold-DbContext command creates entity classes for each table in the EmpDB database and context class (by deriving DbContext) with Fluent API configurations for all the entities in the Models folder
The following is the generated EmpDB entity class for the Employee table
using System;
using System.Collections.Generic;
#nullable disable
namespace ECCoreTutorial.Models
{
public partial class TblEmployee
{
public TblEmployee()
{
InverseCreatedByNavigation = new HashSet<TblEmployee>();
TblEmployeeDetailCreatedByNavigations = new HashSet<TblEmployeeDetail>();
TblEmployeeDetailUpdateByNavigations = new HashSet<TblEmployeeDetail>();
}
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string EmailId { get; set; }
public string Password { get; set; }
public bool? IsActive { get; set; }
public DateTime CreatedAt { get; set; }
public int? CreatedBy { get; set; }
public virtual TblEmployee CreatedByNavigation { get; set; }
public virtual TblEmployeeDetail TblEmployeeDetailEmp { get; set; }
public virtual ICollection<TblEmployee> InverseCreatedByNavigation { get; set; }
public virtual ICollection<TblEmployeeDetail> TblEmployeeDetailCreatedByNavigations { get; set; }
public virtual ICollection<TblEmployeeDetail> TblEmployeeDetailUpdateByNavigations { get; set; }
}
} The following is the EmpDBContext class which you can use to save or retrieve data.
using ECCoreTutorial.Models;
using Microsoft.EntityFrameworkCore;
#nullable disable
namespace ECCoreTutorial.DbContexts
{
public partial class EmpDBContext : DbContext
{
public EmpDBContext()
{
}
public EmpDBContext(DbContextOptions<EmpDBContext> options): base(options)
{
}
public virtual DbSet<TblEmployee> TblEmployees { get; set; }
public virtual DbSet<TblEmployeeDetail> TblEmployeeDetails { 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. You can avoid scaffolding the connection string by using the Name= syntax to read it from configuration - see https://go.microsoft.com/fwlink/?linkid=2131148. For more guidance on storing connection strings, see http://go.microsoft.com/fwlink/?LinkId=723263.
optionsBuilder.UseSqlServer("Data Source=.\\SQLEXPRESS;Initial Catalog=EmpDB;Persist Security Info=True;User ID=sa;Password=xxxxxxx");
}
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.HasAnnotation("Relational:Collation", "SQL_Latin1_General_CP1_CI_AS");
modelBuilder.Entity<TblEmployee>(entity =>
{
entity.ToTable("tblEmployee");
.......
}
}Note: EF Core creates entity classes only for tables and not for StoredProcedures or Views.
DotNet CLI
If you use dotnet command line interface to execute EF Core commands then open command prompt and navigate to the root folder and execute the following dotnet ef dbcontext scaffold command:
PM> dotnet ef dbcontext scaffold "Server=.\SQLEXPRESS;Database=EmpDB;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -o Models This is how, you can create EF Core model for an existing database.
Note: Once you have created the model, you must use the Migration commands whenever you change the model to keep the database up to date with the model
Summary:
EF Core does not support visual designer for DB model and wizard to create the entity and context classes similar to EF 6. So, we need to do reverse engineering using the Scaffold-DbContext command. This reverse engineering command creates entity and context classes (by deriving DbContext) based on the schema of the existing database.0 people like this