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
▋Supported framework
▌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)
▋/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
沒有留言:
張貼留言