Create a model for an existing database in the Entity Framework Core
Here, you will learn how to create context and entity classes for existing databases in the Entity Framework Core. Creating entity and context classes for an existing database is called the database first method.
EF Core does not support DB models and wizards for visual designers to create entity and context classes similar to EF 6. Therefore, we need to use the scaffold DbContext command for reverse engineering. This reverse engineering command creates entity and context classes (by deriving DbContext) based on the schema of an existing database.
Let's create entity and context classes for the following SchoolDB database in the local MS SQL Server shown below.
Scaffold dbcontext command
Use scaffold dbcontext to create a model based on an existing database. You can specify the following parameters in the scaffold dbcontext in the Package Manager console:
Scaffold-DbContext [-Connection] [-Provider] [-OutputDir] [-Context] [-Schemas>] [-Tables>] [-DataAnnotations] [-Force] [-Project] [-StartupProject] [<CommonParameters>]
In Visual Studio, select menu tools - > nuget Package Manager - > package manager console and run the following command:
Scaffold-DbContext "Server=.\SQLExpress;Database=SchoolDB;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models
In the above command, the first parameter is a connection string, which consists of three parts:
Database server, database name and security information. In this case, Server=.\SQLExpress; refers to the local SQLEXPRESS database server.
Database=SchoolDB; specifies the database name "SchoolDB" for which we want to create the class. Trusted? Connection = true; specifies Windows authentication.
It will use Windows credentials to connect to SQL Server. The second parameter is the provider name. We use the provider for SQL Server, so it's Microsoft.EntityFrameworkCore.SqlServer. -The OutputDir parameter specifies the directory in which we want to generate all classes, in this case the Models folder.
Use the following command for detailed help on the scaffold dbcontext command:
get-help scaffold-dbcontext –detailed
The above scaffold DbContext command uses the Fluent API configured for all entities in the Models folder to create entity classes for each table in the SchoolDB database, and creates database context classes by deriving DbContext.
The following is the Student entity class generated for the Student table. :
using System;
using System.Collections.Generic;
namespace EFCoreTutorials.Models
{
public partial class Student
{
public Student()
{
StudentCourse = new HashSet<StudentCourse>();
}
public int StudentId { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public int? StandardId { get; set; }
public Standard Standard { get; set; }
public StudentAddress StudentAddress { get; set; }
public ICollection<StudentCourse> StudentCourse { get; set; }
}
}
The following is the SchoolDBContext class, which can be used to save or retrieve data:
using System;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;
namespace EFCoreTutorials.Models
{
public partial class SchoolDBContext : DbContext
{
public virtual DbSet<Course> Course { get; set; }
public virtual DbSet<Standard> Standard { get; set; }
public virtual DbSet<Student> Student { get; set; }
public virtual DbSet<StudentAddress> StudentAddress { get; set; }
public virtual DbSet<StudentCourse> StudentCourse { get; set; }
public virtual DbSet<Teacher> Teacher { 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. See http://go.microsoft.com/fwlink/?LinkId=723263 for guidance on storing connection strings.
optionsBuilder.UseSqlServer(@"Server=.\SQLExpress;Database=SchoolDB;Trusted_Connection=True;");
}
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Course>(entity =>
{
entity.Property(e => e.CourseName)
.HasMaxLength(50)
.IsUnicode(false);
entity.HasOne(d => d.Teacher)
.WithMany(p => p.Course)
.HasForeignKey(d => d.TeacherId)
.OnDelete(DeleteBehavior.Cascade)
.HasConstraintName("FK_Course_Teacher");
});
modelBuilder.Entity<Standard>(entity =>
{
entity.Property(e => e.Description)
.HasMaxLength(50)
.IsUnicode(false);
entity.Property(e => e.StandardName)
.HasMaxLength(50)
.IsUnicode(false);
});
modelBuilder.Entity<Student>(entity =>
{
entity.Property(e => e.StudentId).HasColumnName("StudentID");
entity.Property(e => e.FirstName)
.HasMaxLength(50)
.IsUnicode(false);
entity.Property(e => e.LastName)
.HasMaxLength(50)
.IsUnicode(false);
entity.HasOne(d => d.Standard)
.WithMany(p => p.Student)
.HasForeignKey(d => d.StandardId)
.OnDelete(DeleteBehavior.Cascade)
.HasConstraintName("FK_Student_Standard");
});
modelBuilder.Entity<StudentAddress>(entity =>
{
entity.HasKey(e => e.StudentId);
entity.Property(e => e.StudentId)
.HasColumnName("StudentID")
.ValueGeneratedNever();
entity.Property(e => e.Address1)
.IsRequired()
.HasMaxLength(50)
.IsUnicode(false);
entity.Property(e => e.Address2)
.HasMaxLength(50)
.IsUnicode(false);
entity.Property(e => e.City)
.IsRequired()
.HasMaxLength(50)
.IsUnicode(false);
entity.Property(e => e.State)
.IsRequired()
.HasMaxLength(50)
.IsUnicode(false);
entity.HasOne(d => d.Student)
.WithOne(p => p.StudentAddress)
.HasForeignKey<StudentAddress>(d => d.StudentId)
.HasConstraintName("FK_StudentAddress_Student");
});
modelBuilder.Entity<StudentCourse>(entity =>
{
entity.HasKey(e => new { e.StudentId, e.CourseId });
entity.HasOne(d => d.Course)
.WithMany(p => p.StudentCourse)
.HasForeignKey(d => d.CourseId)
.OnDelete(DeleteBehavior.ClientSetNull)
.HasConstraintName("FK_StudentCourse_Course");
entity.HasOne(d => d.Student)
.WithMany(p => p.StudentCourse)
.HasForeignKey(d => d.StudentId)
.HasConstraintName("FK_StudentCourse_Student");
});
modelBuilder.Entity<Teacher>(entity =>
{
entity.Property(e => e.StandardId).HasDefaultValueSql("((0))");
entity.Property(e => e.TeacherName)
.HasMaxLength(50)
.IsUnicode(false);
entity.HasOne(d => d.Standard)
.WithMany(p => p.Teacher)
.HasForeignKey(d => d.StandardId)
.OnDelete(DeleteBehavior.Cascade)
.HasConstraintName("FK_Teacher_Standard");
});
}
}
}
Note: EF Core creates entity classes only for tables, not for StoredProcedures or views.
Using DotNet CLI
If you use the dotnet command line interface to execute the EF Core command, open a command prompt and navigate to the root folder, then execute the following dotnet ef dbcontext scaffold command:
Through this article, you can use EF Core to create an EF Core model for an existing database.
Note: after you create the model, whenever you change the model, you must use the migrate command to keep the database up to date with the model.
