How to setup a tsql code to be inserted in migration from OnModelCreating

[inlineHamed]

I'm writing a library that requires the final project to add some TSQL code in a migration to setup some database stuff (like triggers, ...)
Currently my solution is to document it somewhere to tell the final user to add an empty migration and inside that paste my desired code. I'm looking for a more automatic way so that the final user only add a call to a method that I wrote in his OnApplicationModelCreating and my code setup all required stuff like this:

protected override void OnApplicationModelCreating(ModelBuilder builder)
{
    var manager = new MyLibraryDbContextModelManager();
    manager.SetupDbContextModel(builder);
}

currently inside 'SetupDbContextModel' I do normal stuff like these:

builder.Entity<Student>().HasOne("Teacher").WithMany().HasForeignKey("TeacherId");

Is there any way that I place some code in 'SetupDbContextModel' so when the user do Add-Migration my desired TSQL code will be placed in his migration like this:

(inside SetupDbContextModel )

builder.RunSql("Create Trigger MyTrigger As ...");

(Inside the Migration):

migrationBuilder.Sql("Create Trigger MyTrigger As ...");

[roji]

I don't know what "SetupDbContextModel" or "OnApplicationModelCreating", but you seem to be looking to include raw SQL into the model configuration (e.g. OnModelCreating), so that it gets executed when migrations are executed.

The main issue with this is that the model represents a state (usable by the program), while migrations represent transitions between two model states. More concretely, what would happen if the user modifies the trigger definition in your RunSql above? If the new SQL were executed again, it would fail since MyTrigger already exists; an ALTER TRIGGER statement is required instead. Similarly, if the RunSql is removed, do we expect the trigger to be dropped (i.e. DROP TRIGGER)? That would require a different SQL to be executed, etc.

[inlineHamed]

You are right about the change problem. let's say it's my responsibility to write my raw query in a way that it will handle changes itself. For example in create trigger case, I will place 'DROP Trigger IF Exists' before my command.
let's say the exact query will be a part of model states. Something like RelationalIndexBuilderExtensions.HasFilter() where any changes in the 'sql' param will result in an AlterColumn command appearance in next migration.
I'm not sure but maybe I'm looking for a custom way to extend migration system. I wonder if we are able in EF Core to define our custom migration generators so if someone call a method in OnModelCreating that put something in the model states( ApplicationDbContextModelSnapshot ) and will result in some command in next migration.

[roji]

You are right about the change problem. let's say it's my responsibility to write my raw query in a way that it will handle changes itself. For example in create trigger case, I will place 'DROP Trigger IF Exists' before my command.
let's say the exact query will be a part of model states. Something like RelationalIndexBuilderExtensions.HasFilter() where any changes in the 'sql' param will result in an AlterColumn command appearance in next migration.

This is the point I was trying to make. EF Core contains specific support for index filters; when the index filter's raw SQL changes, EF Core knows how to change it, and issues an ALTER INDEX. This isn't possible to do for totally arbitrary SQL - EF Core must be aware of how to manage the database object in question (how to create, alter and drop it).

#10770 already tracks adding EF Core support for triggers in Migration, but we haven't been convinced this would be a good idea. Triggers have many configuration options across database (e.g. compare the CREATE TRIGGER command on SQL Server and on PostgreSQL), and so a lot of work would have to happen to model what is essentially a wrapper for raw SQL (that's the main part of a trigger).

One possible approach here is for you to provide APIs which your library's users can call from inside their scaffolded migrations instead of specifying the raw SQL themselves. This would remove the burden of writing the raw trigger SQL, and since its your library, you don't necessarily have to take care of multiple database types etc.

[inlineHamed]

I think the answer is no but asking:
Is it possible for someone from outside of EF Core to add (register) support for some database object in migration?

[ajcvickers]

@hmdhasani See Custom Operations in the docs.

[inlineHamed]

Thanks, I think that will solve my problem