Entity Framework Core with Existing Database

Creating entity & context classes for an existing database is called Database-First approach. EF Core does not support visual designer for DB model and ... EFBasics EFCore EF6DB-First EF6Code-First FAQs EFQuiz EFResources CheatSheet More ✕ EntityFrameworkTutorials EFCore EF6Code-First EF6DB-First EntityFramework EFQuiz FAQs EFResources EFCheatSheet EFCore-Introduction EFCore-Installation EFCore-ExistingDatabase EFCore-DbContext EFCore-FirstApplication EFCore-Querying EFCore-SavingData EFCore-Conventions One-to-ManyConventions One-to-OneConventions EFCore-Configurations EFCore-FluentAPI ConfigureOne-to-ManyRelationship ConfigureOne-to-OneRelationship ConfigureMany-to-ManyRelationship DisconnectedScenario:InsertData UpdateData DeleteData ChangeTracker ShadowProperty WorkingwithDisconnectedEntityGraph TrackingEntityGraph RawSQLQueries WorkingwithStoredProcedure Logging Migration PMCCommands CLICommands Previous Next CreatingaModelforanExistingDatabaseinEntityFrameworkCore HereyouwilllearnhowtocreatethecontextandentityclassesforanexistingdatabaseinEntityFrameworkCore.Creatingentity&contextclassesforanexistingdatabaseiscalledDatabase-Firstapproach. EFCoredoesnotsupportvisualdesignerforDBmodelandwizardtocreatetheentityandcontextclassessimilartoEF6.So,weneedtodoreverseengineeringusingtheScaffold-DbContextcommand. Thisreverseengineeringcommandcreatesentityandcontextclasses(byderivingDbContext)basedontheschemaoftheexistingdatabase. Let'screateentityandcontextclassesforthefollowingSchoolDBdatabaseinthelocalMSSQLServershownbelow. Scaffold-DbContextCommand UseScaffold-DbContexttocreateamodelbasedonyourexistingdatabase.ThefollowingparameterscanbespecifiedwithScaffold-DbContextinPackageManagerConsole: Scaffold-DbContext[-Connection][-Provider][-OutputDir][-Context][-Schemas>][-Tables>] [-DataAnnotations][-Force][-Project][-StartupProject][] InVisualStudio,selectmenuTools->NuGetPackageManger->PackageMangerConsoleandrunthefollowingcommand: PM>Scaffold-DbContext"Server=.\SQLExpress;Database=SchoolDB;Trusted_Connection=True;"Microsoft.EntityFrameworkCore.SqlServer-OutputDirModels Intheabovecommand,thefirstparameterisaconnectionstringwhichincludesthreeparts:DBServer,databasenameandsecurityinfo.Here,Server=.\SQLExpress;referstolocalSQLEXPRESSdatabaseserver. Database=SchoolDB;specifiesthedatabasename"SchoolDB"forwhichwearegoingtocreateclasses.Trusted_Connection=True;specifiestheWindowsauthentication.ItwilluseWindowscredentialstoconnecttotheSQLServer. Thesecondparameteristheprovidername.WeuseproviderfortheSQLServer,soitisMicrosoft.EntityFrameworkCore.SqlServer.The-OutputDirparameterspecifiesthedirectorywherewewanttogeneratealltheclasseswhichistheModelsfolderinthiscase. UsethefollowingcommandtogetthedetailedhelponScaffold-DbContextcommand: PM>get-helpscaffold-dbcontext–detailed TheaboveScaffold-DbContextcommandcreatesentityclassesforeachtableintheSchoolDBdatabaseandcontextclass(byderivingDbContext)withFluentAPIconfigurationsforalltheentitiesintheModelsfolder. ThefollowingisthegeneratedStudententityclassfortheStudenttable. usingSystem; usingSystem.Collections.Generic; namespaceEFCoreTutorials.Models { publicpartialclassStudent { publicStudent() { StudentCourse=newHashSet(); } publicintStudentId{get;set;} publicstringFirstName{get;set;} publicstringLastName{get;set;} publicint?StandardId{get;set;} publicStandardStandard{get;set;} publicStudentAddressStudentAddress{get;set;} publicICollectionStudentCourse{get;set;} } } ThefollowingistheSchoolDBContextclasswhichyoucanusetosaveorretrievedata. usingSystem; usingMicrosoft.EntityFrameworkCore; usingMicrosoft.EntityFrameworkCore.Metadata; namespaceEFCoreTutorials.Models { publicpartialclassSchoolDBContext:DbContext { publicvirtualDbSetCourse{get;set;} publicvirtualDbSetStandard{get;set;} publicvirtualDbSetStudent{get;set;} publicvirtualDbSetStudentAddress{get;set;} publicvirtualDbSetStudentCourse{get;set;} publicvirtualDbSetTeacher{get;set;} protectedoverridevoidOnConfiguring(DbContextOptionsBuilderoptionsBuilder) { if(!optionsBuilder.IsConfigured) { #warningToprotectpotentiallysensitiveinformationinyourconnectionstring,youshouldmoveitoutofsourcecode.Seehttp://go.microsoft.com/fwlink/?LinkId=723263forguidanceonstoringconnectionstrings. optionsBuilder.UseSqlServer(@"Server=.\SQLExpress;Database=SchoolDB;Trusted_Connection=True;"); } } protectedoverridevoidOnModelCreating(ModelBuildermodelBuilder) { modelBuilder.Entity(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(entity=> { entity.Property(e=>e.Description) .HasMaxLength(50) .IsUnicode(false); entity.Property(e=>e.StandardName) .HasMaxLength(50) .IsUnicode(false); }); modelBuilder.Entity(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(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(d=>d.StudentId) .HasConstraintName("FK_StudentAddress_Student"); }); modelBuilder.Entity(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(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:EFCorecreatesentityclassesonlyfortablesandnotforStoredProceduresorViews. DotNetCLI IfyouusedotnetcommandlineinterfacetoexecuteEFCorecommandsthenopencommandpromptandnavigatetotherootfolderandexecutethefollowingdotnetefdbcontextscaffoldcommand: >dotnetefdbcontextscaffold"Server=.\SQLEXPRESS;Database=SchoolDB;Trusted_Connection=True;"Microsoft.EntityFrameworkCore.SqlServer-oModels Thus,youcancreateEFCoremodelforanexistingdatabase. Note:Onceyouhavecreatedthemodel,youmustusetheMigrationcommandswheneveryouchangethemodeltokeepthedatabaseuptodatewiththemodel. Previous Next FastestWaytoInsertusingEFExtensions LearnC#,MVC,ASP.NETCore,LINQ,etc.