[Entity Framework Core] CLI migrations (EF Core 2.X)

 ASP.NET Core    Entity Framework Core   .NET Core CLI   CLI command

▌Introduction

---

We will use CLI command for code-first database migration in .NET Core 2.X and EF Core 2.X.

▋Related articles

[ASP.NET Core] Build MVC website with Entity Framework Core   

[Entity Framework Core] Migrations with EF Command (EF Core 1.X)

[Entity Framework Core] Migrations with EF Command (EF Core 2.X)

▋Supported framework

See .NET implementations supported by EF Core

▌Environment

---

▋.NET Core 2.0.3

▋EF Core 2.0.1

▋EF Core CLT (EF Core .NET Command Line Tools) 2.0.0-rtm-26452

▋MS Sql Server 2012

▌Implement

---

Create two projects

1.  Sample.Website : ASP.NET Core

2.  Sample.DAL : Class library (.NET Core)

Sample.DAL will have the DbContext, Database Models.

▋Data Access layer (DAL)

Make sure the following package are installed:

1.  Microsoft.EntityFrameworkCore (2.0.1)

2.  Microsoft.EntityFrameworkCore.SqlServer (2.0.1)

3.  System.Data.SqlClient (4.4.0)

4.  Microsoft.Extensions.Configuration

5.  Microsoft.Extensions.Configuration.FileExtensions

6.  Microsoft.Extensions.Configuration.Json

▋/Models/DAO/Customer.cs

You can create your own DAO. This one is just for reference.

public class Customer     {         [Key]         [DatabaseGenerated(DatabaseGeneratedOption.Identity)]         public int Id { get; set; }         [StringLength(100)]         [Required]         public string Name { get; set; }         [StringLength(100)]         public string Phone { get; set; }         public int Age { get; set; }         [StringLength(200)]         public string  Description { get; set; }     }

▋/DbContext/NgDbContext.cs

public class NgDbContext : DbContext {         public NgDbContext(DbContextOptions options) : base(options)         {         }         public DbSet<Customer> Customers { get; set; } }

▋Presentation Layer (Website)

Microsoft.AspNetCore.All is default in the Package references. So all we need is installing

1.  Microsoft.EntityFrameworkCore (2.0.1)

2.  Microsoft.EntityFrameworkCore.Tools.DotNet (2.0.0)

The most important is Microsoft.EntityFrameworkCore.Tools.DotNet, which supports the EF Command Line tooling.

Below shows the package references in *.csproj

<ItemGroup>     <PackageReference Include="Microsoft.AspNetCore.All" Version="2.0.3" />     <PackageReference Include="Microsoft.AspNetCore.Cors" Version="2.0.1" />     <PackageReference Include="Microsoft.EntityFrameworkCore" Version="2.0.1" />     <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="2.0.1" />     <PackageReference Include="Newtonsoft.Json" Version="10.0.3" /> </ItemGroup> <ItemGroup>     <DotNetCliToolReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Tools" Version="2.0.1" />     <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="2.0.0" />   </ItemGroup>

▋Test if the EF command works

Open command line or Powershell or Package Management Console*, change directory to the root of the website, and type the command to see if EF Command works.

$> dotnet ef --version

Or $> dotnet ef --help to see the helps.

▋Start Migration

The original add-migration and update-database commands are simple, like this

$> dotnet ef migrations add [migration_name] –c [DbContext_name]

$> dotnet ef database update [migration_name]

PS. [migration_name] = “0” will revert all migrations, or skip this argument to apply all pending migrations.

However, while we put the DbContext in the other project but not the one we are running EF Command, so we have to appoint the target project with DbContext and the startup project for EF Command.

▋Add migration

dotnet ef  --project ../Sample.DAL --startup-project . migrations add [migration_name] --context [DbContext_name]

Specify --context parameter if the target project (Sample.DAL) has multiple DbContext classes.

▋Update database

dotnet ef  --project ../Sample.DAL --startup-project . database update [<migration_name>]

▋Implement IDbContextFactory<DbContext>

Oops, we will get the following error when trying to add a migration:

Unable to create an object of type 'XXXContext'. Add an implementation of 'IDesignTimeDbContextFactory<XXXContext>' to the project..

This error shows that we have to create a design-time DbContext factory for CLT.

See MS Document: Design-time DbContext Creation

▋DesignTimeDbContextFactory.cs

Add the design-time factory into Sample.DAL project which contains DbContext class.

public class DesignTimeDbContextFactory : IDesignTimeDbContextFactory<SvDbContext>     {         public SvDbContext CreateDbContext(string[] args)         {             IConfigurationRoot configuration = new ConfigurationBuilder()                 .SetBasePath(Directory.GetCurrentDirectory())                 .AddJsonFile("appsettings.json")                 .Build();             var builder = new DbContextOptionsBuilder<SvDbContext>();             var connectionString = configuration["Data:DefaultConnection:ConnectionString"];             builder.UseSqlServer(connectionString);             return new SvDbContext(builder.Options);         } }

Notice that you can follow the document and just assign a connection string in this factory.

In the above sample code, the factory’s database connection is depends on appsettings.json in the startup project. (And thaz why we should install Microsoft.Extensions.Configuration* packages in the previous step)

Take a look at my sample solution below and you will get it.

▋appsettings.json

{   "Data": {     "DefaultConnection": {       "ConnectionString": "Server=.;Database=XXX;Persist Security Info=True;User ID=xxxuser;Password=xxxuser;MultipleActiveResultSets=true"     }   } }

▋How to initialize data

Create a class: DbContextSeedData, to seed data for first-time-created tables.

▋DbContextSeedData.cs

public class DbContextSeedData {         public void Seed()         {             var dbContext = (new DbContextFactory()).CreateDbContext();             this.initSvYearEnds(dbContext as SvDbContext);         }         private void initSvYearEnds(SvDbContext dbContext)         {             if (!dbContext.SvYearEnds.Any())//Only initialize when the table is empty             {                 dbContext.SvYearEnds.Add(new SvYearEnd { EmpNo = 8324, IsAttend = true, Name = "JB" });                 dbContext.SaveChanges();             }         } }

Put the Seed function in Startup.cs of Sample.Website

▋Startup.cs

public void Configure(IApplicationBuilder app, IHostingEnvironment env) {      if (env.IsDevelopment())      {                 app.UseDeveloperExceptionPage();                 (new DbContextSeedData()).Seed();      }      //.... }

The Configure function will be triggered every time we add migration or update database. Be aware of that seeding before the table is created will cause an warning like following:

Error Number:208,State:1,Class:16 An error occurred while calling method 'BuildWebHost' on class 'Program'. Continuing without the application service provider. Error: Invalid object name 'XXXXTable'。 Applying migration '20171213090401_InitCreate'. Done.

However, this warning arise from Starup.cs: Configure doesn’t stop the migration actions so you can skip it. Just run dotnet ef database update again to trigger the Seed function after the table is created.

For example,

$> dotnet ef migrations add InitCreate

$> dotnet ef database update

$> dotnet ef database update  => Initialing succeed here!

If you wanna skip the warning, quickly add try catch when initialing data.

try { if (!dbContext.SvYearEnds.Any())//Only initialize when the table is empty {           dbContext.SvYearEnds.Add(new SvYearEnd { EmpNo = 8324, IsAttend = true, Name = "JB" });           dbContext.SaveChanges();     } } catch (Exception) { //... }

▌Reference

---

▋EF Core .NET Command-line Tools

▋Design-time DbContext Creation

▋Cannot make migration. ef core 2.0 #9415

▋Seed initial data in Entity Framework 7 RC 1 and ASP.NET MVC 6