Auditing deletes is problematic on its own, but so too is tracking and storing the Current User at all in EF or disconnected environments where the user does not have their own secure connection to the database, so we cannot use the database user to determine who the Current user is.
Whilst implementing a "Soft" delete is an option to avoid this, it introduces a new structural dependency (row state flag) that ALL queries against affected tables to take the row state flag into account. Not a bad thing, but its a substantial change imposed on the entire application runtime, including any ad-hoc maintenance, reports and data integrations that might not use the EF context.
Have a look at CONTEXT_INFO, there is a good writeup in this answer to a DBA post: Passing info on who deleted record onto a Delete trigger
utilising CONTEXT_INFO allows us to move the user audit management from the EF code into the database if you want to, the benefit to this is that your auditing will now pickup modifications performed by all interactions with the database, not just the EF application process.
There is an old post about setting CONTEXT_INFO in EF that still mostly applies: Entity Framework and User Context
Create a SP to set the CONTEXT_INFO, this is a 128 byte value.
Add this to your migration scripts
Create Procedure [dbo].[SetEFUserContext]
(
@UserID int,
@UserName Varchar(100)
)
AS
Begin
Begin Try
Declare @CONTEXT_INFO Varbinary(max)
SET @CONTEXT_INFO =cast('UserId='+CONVERT(varchar(10),@UserID)
+',Name=' + RTrim(@UserName)
+REPLICATE(' ',128) as varbinary(128))
SET CONTEXT_INFO @CONTEXT_INFO
End Try
Begin Catch
Declare @Errmsg Varchar(max),@ErrSeverity int
Set @Errmsg=ERROR_MESSAGE()
Set @ErrSeverity=ERROR_SEVERITY()
Raiserror(@Errmsg,@ErrSeverity,1)
End Catch
End
Override the SaveChanges() method in your DbContext to execute the above SP before each database model change:
public override int SaveChanges()
{
SetUserContext();
return base.SaveChanges();
}
public int UserId
{
// Implement your own logic to resolve the current user id
get; set;
}
public int UserName
{
// Implement your own logic to resolve the current user name
get; set;
}
private void SetUserContext ()
{
if (String.IsNullOrWhiteSpace(UserName))
return;
//Open a connection to the database so the session is set up
this.Database.Connection.Open();
//Set the user context
//Cannot use ExecuteSqlCommand here as it will close the connection
using (var cmd = this.Database.Connection.CreateCommand())
{
var userNameParam = cmd.CreateParameter();
userNameParam.ParameterName = "@userName";
userNameParam.Value = UserName;
var userIdParam = cmd.CreateParameter();
userIdParam.ParameterName = "@userId";
userIdParam.Value = UserId;
cmd.CommandText = "SetEFUserContext";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add(userIdParam);
cmd.Parameters.Add(userNameParam);
cmd.ExecuteNonQuery();
};
}
Use a trigger on the tables that you to modify the row before deleting it. This way the final user is updated into the row and your existing temporal logic should retain the user.
NOTE: This has to be configured for each table, you can script it from EF, or write an SP in SQL to generate them, the following is just to demonstrate the usage on a single table called table1
CREATE TRIGGER auditTemporalDeleteTrigger
ON database1.dbo.table1
FOR DELETE
AS
DECLARE @user VARCHAR(100), @userId int;
SELECT @user = SYSTEM_USER
-- support for domain credentials, omit the domain name
IF(CHARINDEX('\', @user) > 0)
SELECT @user = SUBSTRING(@user, CHARINDEX('\', @user) + 1, 25);
SELECT @user = SUBSTRING(@user, 1, 100);
--To support EF or web apps with single shared connection, use Context_info
DECLARE @sCONTEXT_INFO varchar(128) = (SELECT CAST(CONTEXT_INFO() AS VARCHAR) FROM sys.SYSPROCESSES WHERE SPID =@@SPID )
IF @sCONTEXT_INFO like '%UserId%'
BEGIN
SELECT @userId = Substring(@sCONTEXT_INFO, CHARINDEX('UserId=', @sCONTEXT_INFO) + 7, CHARINDEX(',', @sCONTEXT_INFO, CHARINDEX('UserId=', @sCONTEXT_INFO)) - CHARINDEX('UserId=', @sCONTEXT_INFO) - 7)
SELECT @User = RIGHT(RTRIM(@sCONTEXT_INFO), LEN(RTRIM(@sCONTEXT_INFO)) - CHARINDEX('Name=', @sCONTEXT_INFO) - 5 + 1)-- + 1 due to RIGHT function and CHARINDEX
END
-- Update the record before the delete, to affect the logs
UPDATE table1
SET ModifiedBy = @User, UserID = @userId
WHERE ID IN (SELECT ID FROM deleted);
-- Actually perform the delete now
DELETE FROM table1
WHERE ID IN (SELECT ID FROM deleted);
GO
If you go down this route, it's not much more effort to implement AFTER triggers for inserts and updates so you can maintain the ModifiedBy and UserID columns without having to pollute your EF runtime any more than just setting the CONTEXT_INFO.
You can write this into your Migration script generation logic or, as I do now, you can write an SP to generate and maintain the triggers for all the tables that you want to track audits on.
This advice works just the same for custom audit logging as well, except you can have a single trigger that covers AFTER insert,update and delete without having to intercept and manually perform the DELETE as we have done here.
I tried to mock this up in SqlFiddle, but it ran out of memory. I didn't trust it at first, but the FOR DELETE trigger like this works great for temporal tables!
