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.