How to UPDATE modified and deleted entities on SaveContext?

Question

Goal is to track who had changed and deleted an entity.

So I have an entity that implements an interface:

interface IAuditable {
   string ModifiedBy {get;set;}
}

class User: IAuditable {
   public int UserId {get;set;}
   public string UserName {get;set;}
   public string ModifiedBy {get;set;}
   [Timestamp]
   public byte[] RowVersion { get; set; }
}

Now the code of entity remove operation could look like this :

User user = context.Users.First();
user.ModifiedBy = CurrentUser.Name;
context.Users.Remove(employer);
context.SaveContext();

In real: ModifiedBy update will be never executed (when my db history triggers expect to "handle" it). Only delete statement will be executed on DB.

I want to know how to force EF Core "update" deleted entities/entries (which implements the specific interface) if entity was modified.

Note: RowVersion adds additional complexity.

P.S. To put additional SaveContext call manually - of course is an option, but I would like to have a generic solution: many various updates and deletes, then one SaveContext do all analyzes.

To update those properties manually before SaveContext collecting var deletedEntries = entries.Where(e => e.State == EntityState.Deleted && isAuditable(e)) it is not an option since it can ruin EF Core locks order management and therefore provoke deadlocks.

Most clear solution would be just stay with one SaveContext call but inject UPDATE statement on auditable fields just before EF CORE call DELETE. How to achieve this? May be somebody has the solution already?

Alternative could be "on delete do not compose DELETE statement but call stored procedure that can accept auditable fields as paramaters"

Answer 1

I want to know how to inject my "UPDATE statement" just before EF call its "DELETE statement"? Do we have such API?

Interesting question. At the time of writing (EF Core 2.1.3), there is no such public API. The following solution is based on the internal APIs, which in EF Core fortunately are publicly accessible under the typical internal API disclaimer:

This API supports the Entity Framework Core infrastructure and is not intended to be used directly from your code. This API may change or be removed in future releases.

Now the solution. The service responsible for modification command creation is called ICommandBatchPreparer:

A service for preparing a list of ModificationCommandBatchs for the entities represented by the given list of IUpdateEntrys.

It contains a single method called BatchCommands:

Creates the command batches needed to insert/update/delete the entities represented by the given list of IUpdateEntrys.

with the following signature:

public IEnumerable<ModificationCommandBatch> BatchCommands(
    IReadOnlyList<IUpdateEntry> entries);

and default implementation in CommandBatchPreparer class.

We will replace that service with custom implementation which will extend the list with "modified" entries and use the base implementation to do the actual job. Since batch is basically a lists of modification commands sorted by dependency and then by type with Delete being before Update, we will use separate batch(es) for the update commands first and concatenate the rest after.

The generated modification commands are based on IUpdateEntry:

The information passed to a database provider to save changes to an entity to the database.

Luckily it's an interface, so we will provide our own implementation for the additional "modified" entries, as well as for their corresponding delete entries (more on that later).

First we'll create a base implementation which simply delegates the calls to the underlying object, thus allowing us to override later only the methods that are essential for what we are trying to achieve:

class DelegatingEntry : IUpdateEntry
{
    public DelegatingEntry(IUpdateEntry source) { Source = source; }
    public IUpdateEntry Source { get; }
    public virtual IEntityType EntityType => Source.EntityType;
    public virtual EntityState EntityState => Source.EntityState;
    public virtual IUpdateEntry SharedIdentityEntry => Source.SharedIdentityEntry;
    public virtual object GetCurrentValue(IPropertyBase propertyBase) => Source.GetCurrentValue(propertyBase);
    public virtual TProperty GetCurrentValue<TProperty>(IPropertyBase propertyBase) => Source.GetCurrentValue<TProperty>(propertyBase);
    public virtual object GetOriginalValue(IPropertyBase propertyBase) => Source.GetOriginalValue(propertyBase);
    public virtual TProperty GetOriginalValue<TProperty>(IProperty property) => Source.GetOriginalValue<TProperty>(property);
    public virtual bool HasTemporaryValue(IProperty property) => Source.HasTemporaryValue(property);
    public virtual bool IsModified(IProperty property) => Source.IsModified(property);
    public virtual bool IsStoreGenerated(IProperty property) => Source.IsStoreGenerated(property);
    public virtual void SetCurrentValue(IPropertyBase propertyBase, object value) => Source.SetCurrentValue(propertyBase, value);
    public virtual EntityEntry ToEntityEntry() => Source.ToEntityEntry();
}

Now the first custom entry:

class AuditUpdateEntry : DelegatingEntry
{
    public AuditUpdateEntry(IUpdateEntry source) : base(source) { }
    public override EntityState EntityState => EntityState.Modified;
    public override bool IsModified(IProperty property)
    {
        if (property.Name == nameof(IAuditable.ModifiedBy)) return true;
        return false;
    }
    public override bool IsStoreGenerated(IProperty property)
        => property.ValueGenerated.ForUpdate()
            && (property.AfterSaveBehavior == PropertySaveBehavior.Ignore
                || !IsModified(property));
}

First we "modify" the source state from Deleted to Modified. Then we modify the IsModified method which returns false for Deleted entries to return true for the auditable properties, thus forcing them to be included in the update command. Finally we modify the IsStoreGenerated method which also returns false for Deleted entries to return the corresponding result for the Modified entries (EF Core code). This is needed to let EF Core correctly handle the database generated values on update like RowVersion. After executing the command, EF Core will call SetCurrentValue with the values returned from the database. Which does not happen for normal Deleted entries and for normal Modified entries propagates to their entity.

Which leads us to the need of the second custom entry, which will wrap the original entry and also will be used as source for the AuditUpdateEntry, hence will receive the SetCurrentValue from it. It will store the received values internally, thus keeping the original entity state unchanged, and will treat them as both "current" and "original". This is essential because the delete command will be executed after update, and if the RowVersion does not return the new value as "original", the generated delete command will fail.

Here is the implementation:

class AuditDeleteEntry : DelegatingEntry
{
    public AuditDeleteEntry(IUpdateEntry source) : base(source) { }
    Dictionary<IPropertyBase, object> updatedValues;
    public override object GetCurrentValue(IPropertyBase propertyBase)
    {
        if (updatedValues != null && updatedValues.TryGetValue(propertyBase, out var value))
            return value;
        return base.GetCurrentValue(propertyBase);
    }
    public override object GetOriginalValue(IPropertyBase propertyBase)
    {
        if (updatedValues != null && updatedValues.TryGetValue(propertyBase, out var value))
            return value;
        return base.GetOriginalValue(propertyBase);
    }
    public override void SetCurrentValue(IPropertyBase propertyBase, object value)
    {
        if (updatedValues == null) updatedValues = new Dictionary<IPropertyBase, object>();
        updatedValues[propertyBase] = value;
    }
}

With these two custom entries we are ready to implement our custom command batch builder:

class AuditableCommandBatchPreparer : CommandBatchPreparer
{
    public AuditableCommandBatchPreparer(CommandBatchPreparerDependencies dependencies) : base(dependencies) { }

    public override IEnumerable<ModificationCommandBatch> BatchCommands(IReadOnlyList<IUpdateEntry> entries)
    {
        List<IUpdateEntry> auditEntries = null;
        List<AuditUpdateEntry> auditUpdateEntries = null;
        for (int i = 0; i < entries.Count; i++)
        {
            var entry = entries[i];
            if (entry.EntityState == EntityState.Deleted && typeof(IAuditable).IsAssignableFrom(entry.EntityType.ClrType))
            {
                if (auditEntries == null)
                {
                    auditEntries = entries.Take(i).ToList();
                    auditUpdateEntries = new List<AuditUpdateEntry>();
                }
                var deleteEntry = new AuditDeleteEntry(entry);
                var updateEntry = new AuditUpdateEntry(deleteEntry);
                auditEntries.Add(deleteEntry);
                auditUpdateEntries.Add(updateEntry);
            }
            else
            {
                auditEntries?.Add(entry);
            }
        }
        return auditEntries != null ?
            base.BatchCommands(auditUpdateEntries).Concat(base.BatchCommands(auditEntries)) :
            base.BatchCommands(entries);
    }
}

and we are almost done. Add a helper method for registering our service(s):

public static class AuditableExtensions
{
    public static DbContextOptionsBuilder AddAudit(this DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.ReplaceService<ICommandBatchPreparer, AuditableCommandBatchPreparer>();
        return optionsBuilder;
    }
}

and call it from you DbContext derived class OnConfiguring override:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    // ...
    optionsBuilder.AddAudit();
}

and you are done.

All this is for single auditable field populated manually just to get the idea. It can be extended with more auditable fields, registering a custom auditable fields provider service and automatically filling the values for insert/update/delete operations etc.

---

P.S. Full code

using System;
using System.Collections.Generic;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.ChangeTracking;
using Microsoft.EntityFrameworkCore.Metadata;
using Microsoft.EntityFrameworkCore.Metadata.Internal;
using Microsoft.EntityFrameworkCore.Update;
using Microsoft.EntityFrameworkCore.Update.Internal;
using Auditable.Internal; 

namespace Auditable
{
    public interface IAuditable
    {
        string ModifiedBy { get; set; }
    }

    public static class AuditableExtensions
    {
        public static DbContextOptionsBuilder AddAudit(this DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.ReplaceService<ICommandBatchPreparer, AuditableCommandBatchPreparer>();
            return optionsBuilder;
        }
    }
}

namespace Auditable.Internal
{
    class AuditableCommandBatchPreparer : CommandBatchPreparer
    {
        public AuditableCommandBatchPreparer(CommandBatchPreparerDependencies dependencies) : base(dependencies) { }

        public override IEnumerable<ModificationCommandBatch> BatchCommands(IReadOnlyList<IUpdateEntry> entries)
        {
            List<IUpdateEntry> auditEntries = null;
            List<AuditUpdateEntry> auditUpdateEntries = null;
            for (int i = 0; i < entries.Count; i++)
            {
                var entry = entries[i];
                if (entry.EntityState == EntityState.Deleted && typeof(IAuditable).IsAssignableFrom(entry.EntityType.ClrType))
                {
                    if (auditEntries == null)
                    {
                        auditEntries = entries.Take(i).ToList();
                        auditUpdateEntries = new List<AuditUpdateEntry>();
                    }
                    var deleteEntry = new AuditDeleteEntry(entry);
                    var updateEntry = new AuditUpdateEntry(deleteEntry);
                    auditEntries.Add(deleteEntry);
                    auditUpdateEntries.Add(updateEntry);
                }
                else
                {
                    auditEntries?.Add(entry);
                }
            }
            return auditEntries != null ?
                base.BatchCommands(auditUpdateEntries).Concat(base.BatchCommands(auditEntries)) :
                base.BatchCommands(entries);
        }
    }

    class AuditUpdateEntry : DelegatingEntry
    {
        public AuditUpdateEntry(IUpdateEntry source) : base(source) { }
        public override EntityState EntityState => EntityState.Modified;
        public override bool IsModified(IProperty property)
        {
            if (property.Name == nameof(IAuditable.ModifiedBy)) return true;
            return false;
        }
        public override bool IsStoreGenerated(IProperty property)
            => property.ValueGenerated.ForUpdate()
                && (property.AfterSaveBehavior == PropertySaveBehavior.Ignore
                    || !IsModified(property));
    }

    class AuditDeleteEntry : DelegatingEntry
    {
        public AuditDeleteEntry(IUpdateEntry source) : base(source) { }
        Dictionary<IPropertyBase, object> updatedValues;
        public override object GetCurrentValue(IPropertyBase propertyBase)
        {
            if (updatedValues != null && updatedValues.TryGetValue(propertyBase, out var value))
                return value;
            return base.GetCurrentValue(propertyBase);
        }
        public override object GetOriginalValue(IPropertyBase propertyBase)
        {
            if (updatedValues != null && updatedValues.TryGetValue(propertyBase, out var value))
                return value;
            return base.GetOriginalValue(propertyBase);
        }
        public override void SetCurrentValue(IPropertyBase propertyBase, object value)
        {
            if (updatedValues == null) updatedValues = new Dictionary<IPropertyBase, object>();
            updatedValues[propertyBase] = value;
        }
    }

    class DelegatingEntry : IUpdateEntry
    {
        public DelegatingEntry(IUpdateEntry source) { Source = source; }
        public IUpdateEntry Source { get; }
        public virtual IEntityType EntityType => Source.EntityType;
        public virtual EntityState EntityState => Source.EntityState;
        public virtual IUpdateEntry SharedIdentityEntry => Source.SharedIdentityEntry;
        public virtual object GetCurrentValue(IPropertyBase propertyBase) => Source.GetCurrentValue(propertyBase);
        public virtual TProperty GetCurrentValue<TProperty>(IPropertyBase propertyBase) => Source.GetCurrentValue<TProperty>(propertyBase);
        public virtual object GetOriginalValue(IPropertyBase propertyBase) => Source.GetOriginalValue(propertyBase);
        public virtual TProperty GetOriginalValue<TProperty>(IProperty property) => Source.GetOriginalValue<TProperty>(property);
        public virtual bool HasTemporaryValue(IProperty property) => Source.HasTemporaryValue(property);
        public virtual bool IsModified(IProperty property) => Source.IsModified(property);
        public virtual bool IsStoreGenerated(IProperty property) => Source.IsStoreGenerated(property);
        public virtual void SetCurrentValue(IPropertyBase propertyBase, object value) => Source.SetCurrentValue(propertyBase, value);
        public virtual EntityEntry ToEntityEntry() => Source.ToEntityEntry();
    }
}

---

Update: Full code updated for EF Core 5 (not tested):

using System;
using System.Collections.Generic;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.ChangeTracking;
using Microsoft.EntityFrameworkCore.Metadata;
using Microsoft.EntityFrameworkCore.Metadata.Internal;
using Microsoft.EntityFrameworkCore.Update;
using Microsoft.EntityFrameworkCore.Update.Internal;
using Auditable.Internal;

namespace Auditable
{
    public interface IAuditable
    {
        string ModifiedBy { get; set; }
    }

    public static class AuditableExtensions
    {
        public static DbContextOptionsBuilder AddAudit(this DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.ReplaceService<ICommandBatchPreparer, AuditableCommandBatchPreparer>();
            return optionsBuilder;
        }
    }
}

namespace Auditable.Internal
{
    class AuditableCommandBatchPreparer : CommandBatchPreparer
    {
        public AuditableCommandBatchPreparer(CommandBatchPreparerDependencies dependencies) : base(dependencies) { }

        public override IEnumerable<ModificationCommandBatch> BatchCommands(IList<IUpdateEntry> entries, IUpdateAdapter updateAdapter)
        {
            List<IUpdateEntry> auditEntries = null;
            List<IUpdateEntry> auditUpdateEntries = null;
            for (int i = 0; i < entries.Count; i++)
            {
                var entry = entries[i];
                if (entry.EntityState == EntityState.Deleted && typeof(IAuditable).IsAssignableFrom(entry.EntityType.ClrType))
                {
                    if (auditEntries == null)
                    {
                        auditEntries = entries.Take(i).ToList();
                        auditUpdateEntries = new List<IUpdateEntry>();
                    }
                    var deleteEntry = new AuditDeleteEntry(entry);
                    var updateEntry = new AuditUpdateEntry(deleteEntry);
                    auditEntries.Add(deleteEntry);
                    auditUpdateEntries.Add(updateEntry);
                }
                else
                {
                    auditEntries?.Add(entry);
                }
            }
            return auditEntries != null ?
                base.BatchCommands(auditUpdateEntries, updateAdapter).Concat(base.BatchCommands(auditEntries, updateAdapter)) :
                base.BatchCommands(entries, updateAdapter);
        }
    }

    class AuditUpdateEntry : DelegatingEntry
    {
        public AuditUpdateEntry(IUpdateEntry source) : base(source) { }
        public override EntityState EntityState => EntityState.Modified;
        public override bool IsModified(IProperty property)
        {
            if (property.Name == nameof(IAuditable.ModifiedBy)) return true;
            return false;
        }
        public override bool IsStoreGenerated(IProperty property)
            => property.ValueGenerated.ForUpdate()
                && (property.GetAfterSaveBehavior() == PropertySaveBehavior.Ignore
                    || !IsModified(property));
    }

    class AuditDeleteEntry : DelegatingEntry
    {
        public AuditDeleteEntry(IUpdateEntry source) : base(source) { }
        Dictionary<IPropertyBase, object> updatedValues;
        public override object GetCurrentValue(IPropertyBase propertyBase)
        {
            if (updatedValues != null && updatedValues.TryGetValue(propertyBase, out var value))
                return value;
            return base.GetCurrentValue(propertyBase);
        }
        public override object GetOriginalValue(IPropertyBase propertyBase)
        {
            if (updatedValues != null && updatedValues.TryGetValue(propertyBase, out var value))
                return value;
            return base.GetOriginalValue(propertyBase);
        }
        public override void SetStoreGeneratedValue(IProperty property, object value)
        {
            if (updatedValues == null) updatedValues = new Dictionary<IPropertyBase, object>();
            updatedValues[property] = value;
        }
    }

    class DelegatingEntry : IUpdateEntry
    {
        public DelegatingEntry(IUpdateEntry source) { Source = source; }
        public IUpdateEntry Source { get; }
        public virtual IEntityType EntityType => Source.EntityType;
        public virtual EntityState EntityState { get => Source.EntityState; set => Source.EntityState = value; }
        public virtual IUpdateEntry SharedIdentityEntry => Source.SharedIdentityEntry;
        public virtual object GetCurrentValue(IPropertyBase propertyBase) => Source.GetCurrentValue(propertyBase);
        public virtual TProperty GetCurrentValue<TProperty>(IPropertyBase propertyBase) => Source.GetCurrentValue<TProperty>(propertyBase);
        public virtual object GetOriginalValue(IPropertyBase propertyBase) => Source.GetOriginalValue(propertyBase);
        public virtual TProperty GetOriginalValue<TProperty>(IProperty property) => Source.GetOriginalValue<TProperty>(property);
        public virtual object GetPreStoreGeneratedCurrentValue(IPropertyBase propertyBase) => Source.GetPreStoreGeneratedCurrentValue(propertyBase);
        public virtual object GetRelationshipSnapshotValue(IPropertyBase propertyBase) => Source.GetRelationshipSnapshotValue(propertyBase);
        public virtual bool HasTemporaryValue(IProperty property) => Source.HasTemporaryValue(property);
        public virtual bool IsConceptualNull(IProperty property) => Source.IsConceptualNull(property);
        public virtual bool IsModified(IProperty property) => Source.IsModified(property);
        public virtual bool IsStoreGenerated(IProperty property) => Source.IsStoreGenerated(property);
        public virtual void SetOriginalValue(IProperty property, object value) => Source.SetOriginalValue(property, value);
        public virtual void SetPropertyModified(IProperty property) => Source.SetPropertyModified(property);
        public virtual void SetStoreGeneratedValue(IProperty property, object value) => Source.SetStoreGeneratedValue(property, value);
        public virtual EntityEntry ToEntityEntry() => Source.ToEntityEntry();
    }
}