none
Entity framework with update trigger RRS feed

  • Question

  • How can I make DbContext.SaveChanges return the fields that have been updated by an SQL trigger?

    All my tables in the database have the fields TraceVersion (int) and UTimestamp (datetime). I have an AFTER UPDATE trigger, that increments TraceVersion by 1 and sets the UTimestamp to the current date and time. Unfortunately DbContext.SaveChanges does not retrieve the fields updated by the trigger, and therefore my entity has not the same values as the database. Is there a way to instruct DbContext.SaveChanges to retrieve the fields TraceVersion and UTimestamp after saving to the database?

    I know I can override SaveChanges and make a Reload as in the following code:

        	public override int SaveChanges()
        	{
        		var entriesModified = 
        			ChangeTracker.Entries().Where(
        				e => e.State == EntityState.Modified).ToList();
        		int rowCount = base.SaveChanges();
        		if (rowCount > 0)
        		{
        			entriesModified.ForEach(e=>e.Reload());
        		return rowCount;
        	}
    

    But this will reload the whole record which is time consuming. I want to reload only the TraceVersion and UTimeStamp. In addition, this reload happens outside the transaction of base.SaveChanges, and therefore the TraceVersion and UTimeStamp might have changed in between.

    Here is an example of the table with the SQL trigger:

    /*
    	drop table [CUsers]
    	drop table [CUsers_Audit]
    */
    --select * from cusers
    
    CREATE TABLE [dbo].[CUsers](
        [Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
        [UserId] [int] NOT NULL,
        [Deleted] [bit] NOT NULL,
        [TraceVersion] [int] NOT NULL,
        [UTimeStamp] [datetime] NOT NULL,
        [NTUser] [varchar](50) NOT NULL,
        [FName] [varchar](20) NOT NULL,
        [LName] [varchar](50) NOT NULL,
        [Active] [bit] NOT NULL,
     CONSTRAINT [IX_CUsers] UNIQUE NONCLUSTERED ([NTUser] ASC) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    
    ALTER TABLE [dbo].[CUsers]  WITH CHECK ADD  CONSTRAINT [FK_CUsers_CUsers] FOREIGN KEY([UserId])
    REFERENCES [dbo].[CUsers] ([Id])
    GO
    
    CREATE TABLE [dbo].[CUsers_Audit](
        [Id] [int] NOT NULL,
        [UserId] [int] NOT NULL,
        [Deleted] [bit] NOT NULL,
        [TraceVersion] [int] NOT NULL,
        [UTimeStamp] [datetime] NOT NULL,
        [NTUser] [varchar](50) NOT NULL,
        [FName] [varchar](20) NOT NULL,
        [LName] [varchar](50) NOT NULL,
        [Active] [bit] NOT NULL,
     CONSTRAINT [PK_CUsers_Audit] PRIMARY KEY CLUSTERED ([Id] ASC, [TraceVersion] ASC) ON [PRIMARY]) ON [PRIMARY]
    GO
    
    ---------- AUDIT TRIGGER SCRIPT FOR TABLE CUsers---------------
    CREATE TRIGGER [dbo].[trCUsers_AUDIT_UD] ON [dbo].[CUsers]
    AFTER UPDATE, DELETE
    AS
    /* If no rows were affected, do nothing */
    IF @@ROWCOUNT=0
        RETURN
    
    SET NOCOUNT ON
    BEGIN TRY
        DECLARE @Counter INT, @Now DATETIME
        SET @Now = GETDATE()
        /* Check the action (UPDATE or DELETE) */
        SELECT @Counter = COUNT(*)
        FROM INSERTED
        IF @Counter = 0 --> DELETE
            THROW 50000, 'DELETE action is prohibited for CUsers', 1
    
        /* Insert previous record to Audit */
        INSERT INTO CUsers_Audit([Id],[UserId],[Deleted],[TraceVersion],[UTimeStamp],[NTUser],[FName],[LName],[Active])  
        SELECT d.[Id],d.[UserId],d.[Deleted],d.[TraceVersion],d.[UTimeStamp],d.[NTUser],d.[FName],d.[LName],d.[Active]
        FROM DELETED d
    
    
        /* Update master record TraceVersion, UTimeStamp */
        UPDATE main
        SET main.TraceVersion = d.TraceVersion + 1, main.UTimeStamp = @Now
        FROM CUsers main
        INNER JOIN DELETED d ON d.Id = main.Id
        INNER JOIN INSERTED i ON i.Id = main.Id
    
        /* Process deleted rows */
        IF NOT EXISTS(SELECT 1 FROM INSERTED WHERE Deleted = 1)
    	BEGIN
    		SELECT d.TraceVersion + 1, @Now
    		FROM DELETED d
            RETURN
    	END
        /* Re-insert last updated master record into Audit table where Deleted = 1 */
        INSERT INTO CUsers_Audit([Id],[UserId],[Deleted],[TraceVersion],[UTimeStamp],[NTUser],[FName],[LName],[Active])  
        SELECT d.[Id],d.[UserId],d.[Deleted],d.[TraceVersion],d.[UTimeStamp],d.[NTUser],d.[FName],d.[LName],d.[Active]
        FROM CUsers d
        INNER JOIN INSERTED i ON d.Id = i.Id
        WHERE i.Deleted = 1
    
        /* Delete master record */
        DELETE c
        FROM CUsers c
        INNER JOIN INSERTED i ON c.Id = i.Id
        WHERE i.Deleted = 1
    END TRY
    BEGIN CATCH
        THROW
    END CATCH
    GO
    
    ALTER TABLE [dbo].[CUsers] ENABLE TRIGGER [trCUsers_AUDIT_UD]
    GO
    
    INSERT CUsers(UserId,Deleted,TraceVersion,UTimeStamp,NTUser,FName,LName,Active)
    VALUES(1,0,1,GETDATE(),'admin','Admin','Admin',1)
    
    

    I would like SaveChanges, to make the Update, but then also issue the following SQL Statement:

    SELECT TraceVersion, UTimeStamp FROM CUsers WHERE Id=@Id

    Is this possible?

    Monday, September 16, 2019 7:07 AM

All replies

  • Hi pappasa,

    Since your question is more related to Entity framework, I will move this case to Entity framework forums for more help.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, September 17, 2019 7:20 AM
  • Hi pappasa,

    To synchronize the data of the context with the data in the database, you can try to refresh the DbContext with method "Refresh".

    Here is the thread you can refer to.

    https://stackoverflow.com/a/18171125/8335151

    Regards,

    Kyle


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, September 18, 2019 5:41 AM
    Moderator
  • When I write:

    var objc = base.ObjectContext;

    I get the following compile error:

    Model1.Context.cs(25,29,25,42): error CS0117: 'DbContext' does not contain a definition for 'ObjectContext'

    It seems, I cannot access ObjectContext, and therefore I cannot access Refresh. Even if I succeeded calling Refresh, I think, it would get the entire row, not just the fields TraceVersion and UTimeStamp. In addition, Refresh would be outside of the transaction in base.SaveChanges().

    Wednesday, September 18, 2019 6:49 AM