win7user10 / Laraue.EfCoreTriggers

Library to write triggers in C# with EF.Core
MIT License
112 stars 20 forks source link

Invalid SQL generated for char properties when inserting constant #37

Closed JarRami closed 2 years ago

JarRami commented 2 years ago

It seems that the SQL generated for a char property can be wrong in some cases? I was looking into using this library for generating triggers to save History information for Insert, Update and Delete with tables like below

public class Project {
   [Key]
   public int ID { get; set; }
}

public class ProjectHistory {
   [Key]
   public int ID { get; set; }
   public int ProjectID { get; set; }
   public char ChangeType { get; set; }
}

Then, if I create the triggers with something like this (and the equivalent for Update and Delete), so that the ChangeType is set from a constant

modelBuilder.Entity<Project>()
    .AfterInsert(trigger => trigger
        .Action(action => action
            .Insert<ProjectHistory>(fromProject => new ProjectHistory() {
                ProjectID = fromProject.ID
                                ChangeType = 'I',               
            })
        )
    )

I get erroneus SQL like this where the 'I' is not set, but is instead used incorrectly: CREATE TRIGGER LC_TRIGGER_AFTER_INSERT_PROJECT ON Project AFTER Insert AS BEGIN DECLARE @NewID INT DECLARE InsertedProjectCursor CURSOR FOR SELECT ID FROM Inserted OPEN InsertedProjectCursor FETCH NEXT FROM InsertedProjectCursor INTO @NewID WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO ProjectHistory (\"ChangeType\", \"ProjectID\") SELECT i, @NewID; FETCH NEXT FROM InsertedProjectCursor INTO @NewID END CLOSE InsertedProjectCursor DEALLOCATE InsertedProjectCursor END"

The line to lookout for being "INSERT INTO ProjectHistory (\"ChangeType\", \"ProjectID\") SELECT i, @NewID;".

Unfortunately, I am not currently able to develop for .NET6 so this is coming from library version 5.3.2.

win7user10 commented 2 years ago

Support chars added in versions 5.3.3 and 6.3.1

JarRami commented 2 years ago

Did a quick test and both this and #38 seem to work perfectly. Thank you!