There is a special base table type in my DbContext
. And when inherited from it I need to generate an additional "SQL" migration operation to create a specific trigger for it. It makes sure table structure is consistent by checking overlapped ranges. Since there are no overlapping indexes or check constraints in SQL Server I have to use triggers (using functions in check constraints leads to same problems with migrations along with cluttering functions "namespace" in SQL).
Since I haven't found any way to create triggers during OnModelCreating
I thought of altering generated migrations. But how to do that?
Tried using SqlServerMigrationsSqlGenerator
and SqlServerMigrationsAnnotationProvider
, but as their name suggests they are used only on a final stage, during generation of SQL commands. This makes them a bit "hidden" from sight when using migrations. Hard to customize when needed and maintain afterwards.
Thought about using CSharpMigrationOperationGenerator
which seems to be perfect for my needs. But there is a problem - I can't access this class. Nor it's namespace.
According to source this class resides in Microsoft.EntityFrameworkCore.Migrations.Design
namespace and is public. And in order to access it a Microsoft.EntityFrameworkCore.Design
package has to be installed.
But it doesn't work.
What am I missing here? How to access and inherit this class? Or perhaps there is a much better and proper way to create triggers automatically during migrations for specific tables?
ICSharpMigrationOperationGenerator
implementationThought about using CSharpMigrationOperationGenerator which seems to be perfect for my needs. But there is a problem - I can't access this class. Nor it's namespace.
According to source this class resides in Microsoft.EntityFrameworkCore.Migrations.Design namespace and is public. And in order to access it a Microsoft.EntityFrameworkCore.Design package has to be installed.
But it doesn't work.
What am I missing here? How to access and inherit this class?
Let's assume you are calling the following CLI command to add a new migration at design time:
dotnet ef migrations add "SomeMigration"
Here is a fully working sample console program, that will use a custom ICSharpMigrationOperationGenerator
implementation called MyCSharpMigrationOperationGenerator
, inherited from CSharpMigrationOperationGenerator
:
using System;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Design;
using Microsoft.EntityFrameworkCore.Internal;
using Microsoft.EntityFrameworkCore.Migrations.Design;
using Microsoft.EntityFrameworkCore.Migrations.Operations;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
namespace IssueConsoleTemplate
{
public class MyCSharpMigrationOperationGenerator : CSharpMigrationOperationGenerator
{
public MyCSharpMigrationOperationGenerator(CSharpMigrationOperationGeneratorDependencies dependencies)
: base(dependencies)
{
}
protected override void Generate(CreateTableOperation operation, IndentedStringBuilder builder)
{
Console.WriteLine("\r\n\r\n---\r\nMyCSharpMigrationOperationGenerator was used\r\n---\r\n");
base.Generate(operation, builder);
}
}
public class MyDesignTimeServices : IDesignTimeServices
{
public void ConfigureDesignTimeServices(IServiceCollection services)
=> services.AddSingleton<ICSharpMigrationOperationGenerator, MyCSharpMigrationOperationGenerator>();
}
public class IceCream
{
public int IceCreamId { get; set; }
public string Name { get; set; }
}
public class Context : DbContext
{
public DbSet<IceCream> IceCreams { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder
.UseSqlServer(@"Data Source=.\MSSQL14;Integrated Security=SSPI;Initial Catalog=So63575132")
.UseLoggerFactory(
LoggerFactory.Create(
b => b
.AddConsole()
.AddFilter(level => level >= LogLevel.Information)))
.EnableSensitiveDataLogging()
.EnableDetailedErrors();
}
}
internal static class Program
{
private static void Main()
{
}
}
}
The MyCSharpMigrationOperationGenerator
class outputs the following lines for every added table as prove that it was called:
---
MyCSharpMigrationOperationGenerator was used
---
As @KasbolatKumakhov pointed out in his comment, it should also be mentinued that the way for referencing Microsoft.EntityFrameworkCore.Design
has been changed from 2.2. to 3.0:
Starting with EF Core 3.0, it is a DevelopmentDependency package. This means that the dependency won't flow transitively into other projects, and that you can no longer, by default, reference its assembly. [...] If you need to reference this package to override EF Core's design-time behavior, then you can update PackageReference item metadata in your project.
<PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="3.0.0"> <PrivateAssets>all</PrivateAssets> <!-- Remove IncludeAssets to allow compiling against the assembly --> <!--<IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>--> </PackageReference>
MigrationOperation
(e.g. for trigger creation)Since I haven't found any way to create triggers during OnModelCreating I thought of altering generated migrations. But how to do that?
To do this properly, you would need to do the following:
MyPrefix:Trigger
)MigrationOperation
(e.g. CreateTriggerMigrationOperation
)IMigrationsModelDiffer
implementation (derived from MigrationsModelDiffer
; this is internal) that returns your own MigrationOperation
ICSharpMigrationOperationGenerator
implementation (derived from CSharpMigrationOperationGenerator
), that then generates the C# code for your own MigrationOperation
IMigrationsSqlGenerator
implementation (derived from SqlServerMigrationsSqlGenerator
) that then handles translating your own MigrationOperation
to SQLIt's not exactly what you asked for, but it does the similar job with low cost and it may come handy for someone.
using Microsoft.EntityFrameworkCore.Migrations;
using Microsoft.EntityFrameworkCore.Migrations.Operations;
public static class MigrationBuilderExtensions
{
public static void ConfigForOracle(this MigrationBuilder migrationBuilder)
{
//For each table registered in the builder, let's create a sequence and a trigger
foreach (CreateTableOperation createTableOperation in migrationBuilder.Operations.ToArray().OfType<CreateTableOperation>())
{
string tableName = createTableOperation.Name;
string primaryKey = createTableOperation.PrimaryKey.Columns[0];
migrationBuilder.CreateSequence<int>(name: $"SQ_{tableName}", schema: createTableOperation.Schema);
migrationBuilder.Sql($@"CREATE OR REPLACE TRIGGER ""TR_{tableName}""
BEFORE INSERT ON ""{tableName}""
FOR EACH ROW
WHEN (new.""{primaryKey}"" IS NULL)
BEGIN
SELECT ""SQ_{tableName}"".NEXTVAL
INTO :new.""{primaryKey}""
FROM dual;
END;");
}
}
}
You can do whatever you want in the extension method and then call it at the end of the Migration.Up()
method. I use it to create sequences and triggers for Oracle 11g tables for identifier increment.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With