How to use EF core to migrate database to SQL Server in. Net core class library

Time:2020-10-12

preface

If you have just used the Entity Framework core as the ORM framework, you will surely encounter some problems with database migration.

At first I was in ASP.NET It was carried out in the web project of core, but later it was found that it was not very reasonable to put it here. Some database migrations, such as adding new tables, fields, modifying field types, etc., should not be associated with the top-level web project. The data migration file is a bit redundant and messy, so I want to be managed by a special project alone It will be better and clearer!

Note that the target framework is. Net core 2.0 instead of. Net standard 2.0

Preliminary preparation, 0

a) , table entity definition, which is stored in the class library of. Net standard 2.0.

/// <summary>
 ///User entity of 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 new. Net core 2.0 class library and define the database context we want to use. It is very simple. Next, we will start 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 repository class library. If not, please use the package manager to install them.It is not recommended to introduce the original package directly Microsoft.AspNetCore.All, as needed


Install-Package Microsoft.EntityFrameworkCore.SqlServer
Install-Package Microsoft.EntityFrameworkCore.Tools

a) Open CMD, then 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. Some commands are shown in the following table:

Migration command description CMD command PMC command
Create migration: migrationname is the migration name dotnet ef migrations add migrationname add-migration migrationname
Remove migration (delete the most recent migration) dotnet ef migrations remove remove-migration
Apply the latest migration (make the migration file apply to the database) dotnet ef database update update-database
Apply specified migration 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:

No executable matching command “dotnet EF” was found

resolvent:

In the project file Light.Repository.csproj Add the following nodes to the


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

Re execute the above command. If the EF core flag appears (a Mustang ready to go), it means that it has succeeded

b) , execute the following command to migrate


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 at:

\

– Alternatively, install the framework version ‘2.0’.

resolvent:

Add the following nodes to the project file:


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

c) Re execute the command of step B, and 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 see https://go.microsoft.com/fwlink/?linkid=851728 for additional patterns supported at design time.

If this problem is in a web project and the link string of dbcontext is configured, this problem will not occur. Obviously, the migration command did not find dbconnectionstring. Next, we will follow the prompts to implement an idesigntimedbcontextfactory < lightcontext > to try

resolvent:

Create a designtimedbcontextfactory file in the same directory as dbcontext, then implement the method createdbcontext in the interface, and configure the 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);
  }
 }

Execute the migration command again and finally succeed.

Success prompt:

Done. To undo this action, use ‘ef migrations remove’

At the same time, the migrations folder and related migration files will be generated under the class library

2. Test migration command

a) Use the following command to apply the migration and generate the database and table


dotnet ef database update

View the structure of the generated database through the SQL Server Explorer of VS, where__ Efmigrationhistory is the record table for each migration

b) Because the data type of string field after migrating to the database is nvarchar (max) and nullable, we will use fluent API to configure the application user table fields. Similarly, you can also use the attribute annotation method to configure, because I don’t like to “pollute” 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 same two commands above to migrate and update the database structure

The observation database table structure has been updated

In the same way, adding and deleting fields are the same migration operation, which is very convenient

3. Extension

a) In order to facilitate the demonstration, in fact, the database connection string during the migration in the class library is written to death. The best way is to read the configured connection under the web project, so as to ensure the consistency between the top and the bottom. There is no need to maintain a redundant database connection configuration for EF migration. The transformation is also very simple, that is, read through the configuration component appsettings.json After the transformation, the connectionstrings node will look like this:

public class DesignTimeDbContextFactory : IDesignTimeDbContextFactory<LightContext>
 {
  public LightContext CreateDbContext(string[] args)
  {
   Directory.SetCurrentDirectory ("..); // set the current path as the path of the current solution
   string appSettingBasePath =  Directory.GetCurrentDirectory () + "/ Light.AuthorityApi "; // change to yours appsettings.json Project name
   var configBuilder = new ConfigurationBuilder()
    .SetBasePath(appSettingBasePath)
    .AddJsonFile("appsettings.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 the following nuget package needs to be introduced:


Install-Package Microsoft.Extensions.Configuration.Json

b) Attribute annotation [column (order = 1)] for EF core has not reached the order of adjusting the database generated fields, but we can still modify the order of entity attributes of the migration file to achieve the desired effect. The following is the table that I rebuilt after adjustment. Can you see the difference between the table and the above table? One picture is better than ten thousand languages

c) Last step, try it yourself: create a seeddata migration file to add the initial data of the database. :)

4. Finally

The strength of EF core is much more than that, and there are more ways to use it, waiting for us to discover and explore. It’s a pleasure to make progress every day!