win7user10 / Laraue.EfCoreTriggers

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

Incorrect SQL generated when comparing property to constant char in Update #42

Closed JarRami closed 2 years ago

JarRami commented 2 years ago

It seems that if I try to update a table on a delete trigger, but in the finding of the rows to update I compare a property to a constant char, incorrect SQL is generated. If I have 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; }
}

and this time, instead of inserting a new ProjectHistory row, I want to find a suitable history row to update with a trigger like this:

modelBuilder.Entity<Project>()          
   .AfterDelete(trigger => trigger
    .Action(action => action
        .Update<ProjectHistory>(
            (deletedProject, findHistory) =>
                findHistory.ChangeType == 'U' &&
                findHistory.ProjectID == deletedProject.ID,
            (deletedProject, oldHistory) => new ProjectHistory() {
                ChangeType = 'D'
            }
        )
    )
);

then everything else goes fine, but the comparison of the ChangeType is incorrect:

ALTER TRIGGER [dbo].[LC_TRIGGER_AFTER_DELETE_PROJECT] ON [dbo].[Project] AFTER Delete AS
BEGIN
  DECLARE @OldID INT
  DECLARE DeletedProjectCursor CURSOR FOR SELECT ID FROM Deleted
  OPEN DeletedProjectCursor
  FETCH NEXT FROM DeletedProjectCursor INTO @OldID
  WHILE @@FETCH_STATUS = 0
  BEGIN
    UPDATE ProjectHistory
    SET ChangeType = 'D'
    WHERE CAST(ProjectHistory.ChangeType AS INT) = 85 AND ProjectHistory.ProjectID = @OldID;
    FETCH NEXT FROM DeletedProjectCursor INTO @OldID
  END
  CLOSE DeletedProjectCursor DEALLOCATE DeletedProjectCursor
END

and deletion from Project fails with

"Msg 245, Level 16, State 1, Procedure LC_TRIGGER_AFTER_DELETE_PROJECT, Line 9 [Batch Start Line 25] Conversion failed when converting the nvarchar value 'U' to data type int."

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

win7user10 commented 2 years ago

Hi, I made fix for this case

JarRami commented 2 years ago

Just tried 5.3.4 and it works for me, thank you!