ExecuteRawlSql doesn't work as I was imagining

[PrestigiousP]

Here is my configuration for my entity:

public void Configure(EntityTypeBuilder<Categorie> builder)
{
    string sql = @"IF EXISTS (
                        SELECT 1
                        FROM inserted i
                        JOIN dbo.categories p ON i.parentid = p.id
                        WHERE p.parentid IS NOT NULL
                    )
                    BEGIN
                        RAISERROR('Cannot insert more than one level deep', 16, 1);
                        ROLLBACK TRANSACTION;
                        RETURN;
                    END;";

    builder.HasOne(c => c.Parent)
        .WithMany()
        .HasForeignKey(c => c.ParentId)
        .OnDelete(DeleteBehavior.Restrict); // Don't delete parent if child exists

    builder.AfterInsert(trigger => trigger
                .Action(action => action.ExecuteRawSql(sql)));
}

What I was expecting is that I'd get a trigger written like this:

CREATE TRIGGER prevent_deep_inserts
ON dbo.Categories
AFTER INSERT
AS
BEGIN
  IF EXISTS (
           SELECT 1
           FROM inserted i
           JOIN dbo.categories p ON i.parentid = p.id
           WHERE p.parentid IS NOT NULL
  )
  BEGIN
    RAISERROR('Cannot insert more than one level deep', 16, 1);
    ROLLBACK TRANSACTION;
    RETURN;
  END;
END;

Instead, I got a trigger written like this:

USE [BIBLIOTHEQUE]
GO
/****** Object:  Trigger [dbo].[LC_TRIGGER_AFTER_INSERT_CATEGORIE]    Script Date: 2023-04-27 11:51:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[LC_TRIGGER_AFTER_INSERT_CATEGORIE] ON [dbo].[Categories] AFTER Insert AS
BEGIN
  DECLARE InsertedCategorieCursor CURSOR LOCAL FOR SELECT * FROM Inserted
  OPEN InsertedCategorieCursor
  FETCH NEXT FROM InsertedCategorieCursor
  WHILE @@FETCH_STATUS = 0
  BEGIN
    IF EXISTS (
                            SELECT 1
                            FROM inserted i
                            JOIN dbo.categories p ON i.parentid = p.id
                            WHERE p.parentid IS NOT NULL
                        )
                        BEGIN
                            RAISERROR('Cannot insert more than one level deep', 16, 1);
                            ROLLBACK TRANSACTION;
                            RETURN;
                        END;
  FETCH NEXT FROM InsertedCategorieCursor
  END
  CLOSE InsertedCategorieCursor DEALLOCATE InsertedCategorieCursor
END

[joshcomley]

Yeah same, having looked at the code there's no way to avoid a cursor, unfortunately. Otherwise very cool project

[joshcomley]

You can achieve this if you copy SqlServerTriggerVisitor from the source code and register your copy when registering the trigger services. Bit faffy and unfortunate duplication but it works. Although I am on an older version for .NET 7, might be improved now.