In the. NET Core class library, use EF Core to migrate the database to SQL Server. coreef

In the. NET Core class library, use EF Core to migrate the database to SQL Server. coreef

Preface

If you just use EntityFramework Core as the orm framework, you may encounter database migration problems.

At first, I was in ASP. NET Core Web project, but later found that it is not very reasonable to put it here, some database migration, such as adding tables, fields, modifying field types, etc, it should not be associated with the top-level Web project. It is a bit redundant and messy to put the data migration files here, therefore, it is better and clearer to think about how to manage a specific project separately!

Note that the target framework is. NET Core 2.0 instead of. NET Standard 2.0.

0. Preparations

A) Table entity definition, which is stored in the. NET Standard 2.0 class library.

/// <Summary> /// user entity of the system application /// </summary> public class ApplicationUser: baseModel {/// <summary> /// user name // </summary> public string UserName {get; set ;} /// <summary> /// Password /// </summary> public string Password {get; set ;} /// <summary> /// Email address /// </summary> public string Email {get; set ;}}

B) Create a. NET Core 2.0 class library and define the context of the database we want to use. This is very simple. Let's start with our body.

/// <Summary> /// SYSTEM context /// </summary> public class LightContext: DbContext {public LightContext (DbContextOptions <LightContext> options): base (options) {}/// <summary> /// System Application User /// </summary> public DbSet <ApplicationUser> ApplicationUser {get; set ;} /// <summary> /// Role table /// </summary> public DbSet <Role> Role {get; set ;}}

1. Problem Summary

First, make sure that the following two Nuget packages have been introduced into the warehouse class library. If not, use the Package Manager for installation. We do not recommend that you directly introduce the original package: Microsoft. AspNetCore. All. Just import the package as needed.

Install-Package Microsoft.EntityFrameworkCore.SqlServerInstall-Package Microsoft.EntityFrameworkCore.Tools

A) Open CMD, switch to the path of the class library, and execute the following command. However, you can also use the Package Manager Console (PMC) for migration, but there will be some changes. The following table lists some commands:

Migration command description	CMD command	PMC command
Create migration: migrationname indicates the migration name.	Dotnet ef migrations add migrationname	Add-migration migrationname
Remove migration (delete the latest migration)	Dotnet ef migrations remove	Remove-migration
Latest Application migration (migrate file application to database)	Dotnet ef database update	Update-database
Migration specified by the application	Dotnet ef database update migrationname	Update-database migrationname
View migration list	Dotnet ef migrations list
View database context information	Dotnet ef dbcontext info

dotnet ef

Error message:

The executable file matching the command "dotnet-ef" is not found.

Solution:

In the project file Light. Repository. csproj, add the following nodes:

<ItemGroup> <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="2.0.1" /></ItemGroup>

Re-execute the above command. If the EF Core sign (a wild horse ready to go) appears, it indicates it has succeeded.

B) execute the following command for Migration

dotnet ef migrations add InitLightDB

Error message:

The specified framework version '2. 0' could not be parsed
The specified framework 'Microsoft. NETCore. app', version '2. 0' was not found.
-Check application dependencies and target a framework version installed:
\
-Alternatively, install the framework version '2. 0 '.

Solution:

Add the following nodes to the project file:

<PropertyGroup> <TargetFramework>netcoreapp2.0</TargetFramework> <RuntimeFrameworkVersion>2.0.3</RuntimeFrameworkVersion> </PropertyGroup>

C) Run step B again. The error message is as follows:

Error message:

Unable to create an object of type 'lightcontext'. Add an implementation of 'idesigntimedbcontextfactory <LightContext> 'to the project, or seeHttps://go.microsoft.com/fwlink? Linkid = 851728For additional patterns supported at design time.

This problem does not occur if the link string of DbContext is configured in a Web project. Apparently, DbConnectionString is not found in the migration command. Next, follow the prompts to implement an IDesignTimeDbContextFactory <LightContext>.

Solution:

Create a DesignTimeDbContextFactory file in the same directory as DbContext, implement the CreateDbContext method in the interface, and configure ConnectionString

public class DesignTimeDbContextFactory : IDesignTimeDbContextFactory<LightContext> {  public LightContext CreateDbContext(string[] args)  {   var builder = new DbContextOptionsBuilder<LightContext>();   builder.UseSqlServer("Server=(localdb)\\MSSQLLocalDB;Integrated Security=true;Initial Catalog=Light;");   return new LightContext(builder.Options);  } }

The migration command was executed again and finally succeeded.

Success prompt:

Done. To undo this action, use 'ef migrations delete'

At the same time, the Migrations folder and related Migration files will be generated under the class library.

2. Test the migration command

A) use the following command to migrate applications and generate databases and tables.

dotnet ef database update

View the structure of the generated database through the SQL Server resource manager of VS, where _ EFMigrationsHistory is the record table for each migration

B) because the data type after a string field is migrated to the database is nvarchar (max) and can be empty, we will use the Fluent API to configure the ApplicationUser table fields, you can also use Attribute annotations for configuration, because I do not like "contaminated" Table entities.

public static void ConfigApplicationUser(ModelBuilder modelBuilder)  {   modelBuilder.Entity<ApplicationUser>(m =>   {    m.Property(t => t.Email)      .HasMaxLength(50);    m.Property(t => t.UserName)      .IsRequired()      .HasMaxLength(50);    m.Property(t => t.Password)      .IsRequired()      .HasMaxLength(20);   });  }

Then use the preceding two commands to re-migrate and update the database structure.

Observe that the database table structure has been updated

Similarly, adding a field makes it easy to delete fields in the same migration operation.

3. Expansion

A) In order to facilitate the demonstration, the database connection string during the above migration in the class library is actually written to death. The best way is to read the connections already configured in the Web project, in this way, the upper and lower consistency can be ensured without having to maintain a redundant database connection configuration for EF migration. The transformation is also very simple, that is, reading the appsonstrings node of etettings. json through the Configuration component. After the transformation, it is like this:

Public class DesignTimeDbContextFactory: IDesignTimeDbContextFactory <LightContext> {public LightContext CreateDbContext (string [] args) {Directory. setCurrentDirectory (".. "); // set the current path to the path of the current solution string paiettingbasepath = Directory. getCurrentDirectory () + "/Light. authorityApi "; // change it to your appsettings. json project name var configBuilder = new ConfigurationBuilder (). setBasePath (appSettingBasePath ). addJsonFile ("deleteworkflow. json "). build (); var builder = new DbContextOptionsBuilder <LightContext> (); // builder. useSqlServer ("Server = (localdb) \ MSSQLLocalDB; Integrated Security = true; Initial Catalog = Light;"); builder. useSqlServer (configBuilder. getConnectionString ("LightConnection"); return new LightContext (builder. options );}}

Note that you need to introduce the following Nuget package:

Install-Package Microsoft.Extensions.Configuration.Json

B) attribute annotation [Column (Order = 1)] has not reached the Order in which fields generated by the database can be adjusted for EF Core, however, we can modify the object attribute order of the migration file to achieve the desired effect. The following figure shows the table that I re-generated after the adjustment. Is it different from the above one:

C) The last step is to create a SeedData migration file to add the initial data of the database. :)

4. Last

EF Core is far more powerful than this, and more methods are available for us to discover and explore. It's a pleasure to make a little progress every day!