Skip to content

Instantly share code, notes, and snippets.

@Grinderofl
Last active October 30, 2018 14:11
Show Gist options
  • Save Grinderofl/69ee7e8361ddc11d324e3249fb3bbeff to your computer and use it in GitHub Desktop.
Save Grinderofl/69ee7e8361ddc11d324e3249fb3bbeff to your computer and use it in GitHub Desktop.
Actual idempotent Insert or Update Migration Script for EF Core
public class CreateOrUpdateOperation : MigrationOperation
{
public EntityWithName Entity { get; }
public Type EntityType { get; }
public CreateOrUpdateOperation(EntityWithName entity, Type entityType)
{
Entity = entity;
EntityType = entityType;
}
}
public class CreateOrUpdateOperation<T> : CreateOrUpdateOperation where T : EntityWithName
{
public CreateOrUpdateOperation(T entity) : base(entity, typeof(T))
{
}
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
base.OnConfiguring(optionsBuilder);
optionsBuilder.ReplaceService<IMigrationsSqlGenerator, TicketToolMigrationsSqlGenerator>();
}
public partial class Test : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.CreateOrUpdate(YourEntity.SeedData);
}
protected override void Down(MigrationBuilder migrationBuilder)
{
}
}
public static class MigrationExtensions
{
public static MigrationBuilder CreateOrUpdate<T>(this MigrationBuilder builder, params T[] entities) where T : EntityWithName
{
foreach(var entity in entities)
builder.Operations.Add(new CreateOrUpdateOperation<T>(entity));
return builder;
}
}
public class TicketToolMigrationsSqlGenerator : SqlServerMigrationsSqlGenerator
{
public TicketToolMigrationsSqlGenerator([NotNull] MigrationsSqlGeneratorDependencies dependencies,
[NotNull] IMigrationsAnnotationProvider migrationsAnnotations)
: base(dependencies, migrationsAnnotations)
{
}
protected override void Generate(MigrationOperation operation, IModel model, MigrationCommandListBuilder builder)
{
switch (operation)
{
case CreateOrUpdateOperation createOrUpdate:
GenerateCreateOrUpdate(createOrUpdate, model, builder);
break;
default:
base.Generate(operation, model, builder);
break;
}
}
private void GenerateCreateOrUpdate(CreateOrUpdateOperation operation, IModel model, MigrationCommandListBuilder builder)
{
var sqlHelper = Dependencies.SqlGenerationHelper;
var entityType = model.FindEntityType(operation.EntityType);
var relationalType = entityType.Relational();
var primaryKey = entityType.GetProperties().Single(x => x.IsPrimaryKey()).Relational().ColumnName;
var stringMapping = Dependencies.TypeMappingSource.FindMapping(typeof(string));
var longMapping = Dependencies.TypeMappingSource.FindMapping(typeof(long));
var schema = string.IsNullOrWhiteSpace(relationalType.Schema) ? sqlHelper.DelimitIdentifier("dbo") : sqlHelper.DelimitIdentifier(relationalType.Schema);
var table = sqlHelper.DelimitIdentifier(relationalType.TableName);
var idColumn = sqlHelper.DelimitIdentifier(primaryKey);
var nameColumn = sqlHelper.DelimitIdentifier(nameof(EntityWithName.Name));
var idValue = longMapping.GenerateSqlLiteral(operation.Entity.Id);
var nameValue = stringMapping.GenerateSqlLiteral(operation.Entity.Name);
builder.Append($"SET IDENTITY_INSERT {schema}.{table} ON")
.AppendLine(sqlHelper.StatementTerminator)
.AppendLine($"IF EXISTS (SELECT * FROM {schema}.{table} WHERE {idColumn} = {idValue})")
.AppendLine($"\tUPDATE {schema}.{table} SET {nameColumn} = {nameValue} WHERE {idColumn} = {idValue}")
.AppendLine($"ELSE")
.Append($"\tINSERT INTO {schema}.{table} ({idColumn}, {nameColumn}) VALUES ({idValue}, {nameValue})")
.AppendLine(sqlHelper.StatementTerminator)
.Append($"SET IDENTITY_INSERT {schema}.{table} ON")
.Append(sqlHelper.StatementTerminator)
.EndCommand();
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment