I have seen Demos of Entity Framework migrations before but have never used the technology in a project also the demos I had seen were using MS Sql Server as the Database.
Does EF Core Support Migrations on other Databases ?
Could We use EF Core Migrations as part of Continous Deployment process ?
Could I combine the Unicorn power of EF CORE with the MySql dolphin.
I fired up Visual Studio 2019 and created a dotnet core class library
I then loaded in the following Nuget Packages by running the following command in the Visual Studio Package Manager Console
Install-Package Microsoft.EntityFrameworkCore -Version 3.1.8
Install-Package Microsoft.EntityFrameworkCore.Tools -Version 3.1.8
Install-Package Pomelo.EntityFrameworkCore.MySql -Version 3.1.2I setup a DbContext and related classes
public class MySqlDbContext :DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder options)
=> options.UseMySql("Server=localhost;Port=3306;Database=dataMigrate_db;Uid=root;Pwd=itsasecret;");
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<UserPrivilege>()
.HasKey(c => new {c.UserId, c.PrivilegeId});
}
public DbSet<User> Users { get; set; }
public DbSet<Privilege> Privileges { get; set; }
public DbSet<UserPrivilege> UserPrivileges { get; set; }
}
public class Privilege
{
public Privilege()
{
UserPrivileges = new List<UserPrivilege>();
}
public long Id { get; set; }
public string Name { get; set; }
public IList<UserPrivilege> UserPrivileges { get; set; }
}
public class User
{
public User()
{
UserPrivileges = new List<UserPrivilege>();
}
public long Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string Email { get; set; }
public IList<UserPrivilege> UserPrivileges { get; set; }
}
public class UserPrivilege
{
public long UserId { get; set; }
public long PrivilegeId { get; set; }
public User User { get; set; }
public Privilege Privilege { get; set; }
}In the Package Manager Console, I generate the Initial Create Migration
Add-Migration InitialCreateThis produces the following code in a migrations folder
public partial class InitialCreate : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.CreateTable(
name: "Privileges",
columns: table => new
{
Id = table.Column<long>(nullable: false)
.Annotation("MySql:ValueGenerationStrategy", MySqlValueGenerationStrategy.IdentityColumn),
Name = table.Column<string>(nullable: true)
},
constraints: table =>
{
table.PrimaryKey("PK_Privileges", x => x.Id);
});
migrationBuilder.CreateTable(
name: "Users",
columns: table => new
{
Id = table.Column<long>(nullable: false)
.Annotation("MySql:ValueGenerationStrategy", MySqlValueGenerationStrategy.IdentityColumn),
FirstName = table.Column<string>(nullable: true),
LastName = table.Column<string>(nullable: true),
Email = table.Column<string>(nullable: true)
},
constraints: table =>
{
table.PrimaryKey("PK_Users", x => x.Id);
});
migrationBuilder.CreateTable(
name: "UserPrivileges",
columns: table => new
{
UserId = table.Column<long>(nullable: false),
PrivilegeId = table.Column<long>(nullable: false)
},
constraints: table =>
{
table.PrimaryKey("PK_UserPrivileges", x => new { x.UserId, x.PrivilegeId });
table.ForeignKey(
name: "FK_UserPrivileges_Privileges_PrivilegeId",
column: x => x.PrivilegeId,
principalTable: "Privileges",
principalColumn: "Id",
onDelete: ReferentialAction.Cascade);
table.ForeignKey(
name: "FK_UserPrivileges_Users_UserId",
column: x => x.UserId,
principalTable: "Users",
principalColumn: "Id",
onDelete: ReferentialAction.Cascade);
});
migrationBuilder.CreateIndex(
name: "IX_UserPrivileges_PrivilegeId",
table: "UserPrivileges",
column: "PrivilegeId");
}
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.DropTable(
name: "UserPrivileges");
migrationBuilder.DropTable(
name: "Privileges");
migrationBuilder.DropTable(
name: "Users");
}
}I call the Update-Database Command in the Package Manager Console
This produces the following tables in MySql
create table Privileges
(
Id bigint auto_increment
primary key,
Name longtext null
);
create table Users
(
Id bigint auto_increment
primary key,
FirstName longtext null,
LastName longtext null,
Email longtext null
);
create table UserPrivileges
(
UserId bigint not null,
PrivilegeId bigint not null,
primary key (UserId, PrivilegeId),
constraint FK_UserPrivileges_Privileges_PrivilegeId
foreign key (PrivilegeId) references Privileges (Id)
on delete cascade,
constraint FK_UserPrivileges_Users_UserId
foreign key (UserId) references Users (Id)
on delete cascade
);
create index IX_UserPrivileges_PrivilegeId
on UserPrivileges (PrivilegeId);An additional Table was generated called __EFMigrationsHistory this is used by EF Core to track what migrations have been applied to the Database.
At this point it was clear to me that this was not really an option for the team.
There were also some issues with the Data Types. I do not think longtext would be the first choice for string columns if I was writing the tables manually.
So I would need to be a bit more explicit in the OnModelCreating method
I could continue to add Migration Classes in C# code. but there was really no point. This was not a DBA Friendly format !
There was no way I could see our DBA team opening up a C# solution and looking at C# Code to determine issues with Query and Index performance
While its was not a choice I could go with. EF Core Migrations is maybe a choice for your project, especially if you are working on a stand alone Desktop app that needs a local database its something to consider.
Sadly there would be no Unicorns and Dolphins this time. I would have to find something else for the team.