EFCore - Triggers not getting scaffolded

[shvmgpt116]

I have following table definition which uses trigger to auto populate the column.

CREATE TABLE [dbo].[tab1] (
    [col1] INT NULL
);

CREATE TABLE [dbo].[tab2] (
    [col2] INT NULL
);

create trigger tr_1
on tab2
after insert 
as
begin
insert into tab1 (col1) values (1);
end;

When I scaffold this, following model gets generated.

protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Tab1>(entity =>
            {
                entity.HasNoKey();

                entity.ToTable("tab1");

                entity.Property(e => e.Col1).HasColumnName("col1");
            });

            modelBuilder.Entity<Tab2>(entity =>
            {
                entity.HasNoKey();

                entity.ToTable("tab2");

                entity.Property(e => e.Col2).HasColumnName("col2");
            });

            OnModelCreatingPartial(modelBuilder);
        }

The model does not have anything for the triggers. I get the same model after scaffolding when there were no triggers.
The trigger used here is simple and for demonstration purpose only.

However my question is why does the model not have anything for the triggers?
A trigger may work to auto populate the column or as default value for it. Shouldn't we include it in the generated model?

Include provider and version information

EF Core version: EFCore 6.0.1
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 6.0
Operating system: Windows 10
IDE: Visual Studio 2022 17.0.0 Preview 3.1

[shvmgpt116]

First scaffolding and then applying code first migration on the model would create different table definitions. Won't that be an issue?

[roji]

Duplicate of #10770

[roji]

@shvmgpt116 EF Core doesn't currently support triggers in migrations or scaffolding.

First scaffolding and then applying code first migration on the model would create different table definitions. Won't that be an issue?

What issue are you thinking about specifically? AFAIK creating a trigger is always a separate statement (CREATE TRIGGER ...) after the table creation anyway...

[shvmgpt116]

@shvmgpt116 EF Core doesn't currently support triggers in migrations or scaffolding.

First scaffolding and then applying code first migration on the model would create different table definitions. Won't that be an issue?

What issue are you thinking about specifically? AFAIK creating a trigger is always a separate statement (CREATE TRIGGER ...) after the table creation anyway...

Sorry I was not clear in my last statement. What I mean is that the old schema definition has the triggers which we scaffolded to generate model. But after applying code-first migration from the generated model, the new schema definition will not have the triggers.

[roji]

@shvmgpt116 OK, thanks for clarifying.

There are many things in the database which EF Core does not scaffold (stored functions/procedures, various database configuration settings). EF Core does not aim to reproduce the scaffolded database 100% - this would generally be unfeasible across databases.

[ajcvickers]

@shvmgpt116 It would be interesting to understand your development flow here. Are you starting with an existing database and scaffolding it once, then moving to migrations? Or are you intending to use both scaffolding and migrations simultaneously on the same database? If the latter, can you provide some details on the motivation/reasons for doing this?

[gao-artur]

I was able to extend ReverseEngineerScaffolder to pull triggers from database and generate DbContext extensions class with InitTriggers method. For the context: we are working with DB-First mode and using scaffold tool to update our C# models. We have integration tests where we start mysql docker container then call EnsureCreated method to initialize schema and tables. Few schemas have triggers that must present on tested DB for correct work. This code is MySql specific but it should be easy to adopt it for other providers (EF Core 3.1):

using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using Microsoft.EntityFrameworkCore.Design;
using Microsoft.EntityFrameworkCore.Scaffolding;
using Microsoft.EntityFrameworkCore.Scaffolding.Internal;
using Microsoft.EntityFrameworkCore.Storage.Internal;
using MySql.Data.MySqlClient;

namespace Scaffold.MySql.Console.EfCustomizations
{
    public class CustomReverseEngineerScaffolder : ReverseEngineerScaffolder
    {
        private readonly INamedConnectionStringResolver _connectionStringResolver;

        public CustomReverseEngineerScaffolder(
            IDatabaseModelFactory databaseModelFactory,
            IScaffoldingModelFactory scaffoldingModelFactory,
            IModelCodeGeneratorSelector modelCodeGeneratorSelector,
            ICSharpUtilities cSharpUtilities,
            ICSharpHelper cSharpHelper,
            INamedConnectionStringResolver connectionStringResolver)
            : base(
                databaseModelFactory,
                scaffoldingModelFactory,
                modelCodeGeneratorSelector,
                cSharpUtilities,
                cSharpHelper,
                connectionStringResolver)
        {
            _connectionStringResolver = connectionStringResolver;
        }

        public override ScaffoldedModel ScaffoldModel(
            string connectionString,
            DatabaseModelFactoryOptions databaseOptions,
            ModelReverseEngineerOptions modelOptions,
            ModelCodeGenerationOptions codeOptions)
        {
            var model = base.ScaffoldModel(connectionString, databaseOptions, modelOptions, codeOptions);

            var connString = _connectionStringResolver.ResolveConnectionString(connectionString);

            var triggers = GetTriggers(connString);

            if (triggers.Any())
            {
                var triggersCode = GenerateTriggersCode(triggers);

                var extensions = GenerateDbContextExtensions(codeOptions.ContextNamespace, codeOptions.ContextName, triggersCode);

                model.AdditionalFiles.Add(extensions);
            }

            return model;
        }

        private IList<TriggerInfo> GetTriggers(string connString)
        {
            using var connection = new MySqlConnection(connString);
            connection.Open();

            using var command = connection.CreateCommand();
            command.CommandText = "SHOW TRIGGERS;";

            var triggers = new List<TriggerInfo>();

            using var reader = command.ExecuteReader();
            while (reader.Read())
            {
                var trigger = new TriggerInfo
                {
                    Trigger = reader.GetString("Trigger"),
                    Event = reader.GetString("Event"),
                    Table = reader.GetString("Table"),
                    Statement = reader.GetString("Statement"),
                    Timing = reader.GetString("Timing"),
                };

                triggers.Add(trigger);
            }

            return triggers;
        }

        private string GenerateTriggersCode(IList<TriggerInfo> triggers)
        {
            const string indentation = "            ";
            var sb = new StringBuilder();

            for (var i = 0; i < triggers.Count; i++)
            {
                var trigger = triggers[i];
                sb.Append(indentation).Append("@\"");
                sb.Append(trigger);
                sb.Append("\"");

                if (i < triggers.Count - 1)
                {
                    sb.AppendLine(",");
                }
            }

            return sb.ToString();
        }

        private ScaffoldedFile GenerateDbContextExtensions(
            string @namespace,
            string dbContextName,
            string triggersCode)
        {
            var stream = typeof(CustomReverseEngineerScaffolder)
                .GetTypeInfo()
                .Assembly
                .GetManifestResourceStream("Scaffold.MySql.Console.EfCustomizations.DbContextExtensionsTemplate");

            using var reader = new StreamReader(stream!);

            var template = reader.ReadToEnd();

            var className = $"{dbContextName}Extensions";

            var code = string.Format(template, @namespace, className, dbContextName, triggersCode);

            return new ScaffoldedFile
            {
                Path = $"{className}.cs",
                Code = code
            };
        }
    }

    public class TriggerInfo
    {
        public string Trigger { get; set; }

        public string Event { get; set; }

        public string Table { get; set; }

        public string Statement { get; set; }

        public string Timing { get; set; }

        public override string ToString()
        {
            return $"CREATE TRIGGER {Trigger} {Timing} {Event} ON {Table} {Environment.NewLine}FOR EACH ROW{Environment.NewLine}  {Statement}";
        }
    }
}

The DbContextExtensionsTemplate file:

using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;

namespace {0}
{{
    public static class {1}
    {{
        private static string[] _triggers = new[]
        {{
{3}
        }};

        public static async Task InitTriggers(this {2} dbContext)
        {{
            foreach (var trigger in _triggers)
            {{
                await dbContext.Database.ExecuteSqlRawAsync(trigger);
            }}
        }}
    }}
}}

[ajcvickers]

@gao-artur

For the context: we are working with DB-First mode and using scaffold tool to update our C# models. We have integration tests where we start mysql docker container then call EnsureCreated method to initialize schema and tables.

This can work if you understand what the difference is between what reverse engineering understands and reflect in your model, but this is pretty limiting (triggers, views, stored procedures, etc.) and so this approach definitely does not work in general and is not something we would recommend.

[gao-artur]

Sure, I understand this. But this approach still can be useful as workaround for people who need triggers in their code after running scaffold.

[ajcvickers]

@gao-artur What I'm saying is that if you don't use migrations to manage your production database, then it would be better not to use migrations (or EnsureCreated, which is migrations under the covers) to manage your test databases. Instead create instances of your test databases using the same SQL DDL that is used to create your production database. (Or generate such DDL from the production database, if you don't already have it.)