audit-proof (immutable) database entries with Entity Framework Core (INSERT instead of UPDATE)

Question

I am using Entity Framework Core 6.0 and I am currently looking for a way on how to persist data audit-proof (immutably).

Concretely, any intended modification of an existing database entry (SQL UPDATE statement) shall be translated into a new version/ revision of the existing entity (SQL INSERT statement) and, thus, result in a new database entry which supersedes the existing one (correlation among related entities is established by CorrelationId). Only the metadata of an entry are allowed to be updated, such that predecessor and successor relate to each other by ID (see PredecessorId and SucessorId, respectively).

Of course, this should not only work for entity relations (one-to-one, one-to-many, many-to-many) on first level but all along a complete object graph (arbitrary level).

I've added sample code for entity modeling for a contrived example to illustrate the idea: Once I have placed an Order Order any subsequent changes of an ordered product Product must not have any influence on the order placed (not the names, not the prices). Instead a change in a product shall result in a new database entry with the same CorrelationId and metadata info on current and previous entry to relate to each other.

The example is contrived and the data modeling in the example should not be considered from a domain perspective.

I already looked into Entity Framework Plus and its audit functionality but from what I understood, it does only provide a separate table for the changes made to entities. However, an update on an entity results in a SQL UPDATE and in the example above a change in product price would affect the order already placed (Total would be different). The old state of the entity does not exist in its form any longer but rather one can reconstruct and comprehend who, when and how the entity changed with the audit information.

So, is there any clever solution on how to handle this in Entity Framework Core? I already tried myself in overriding the SaveChanges(Async) methods in the DbContext but I was not successful in preserving the entity relations:

case 1: principal changed, dependent(s) unchanged
      -> new principal entity with reference to unchanged dependent(s)
case 2: principal unchanged, dependent(s) changed
      -> no new principal entity with reference to new dependent(s)
case 3: principal changed, dependent(s) changed
      -> new principal entity with reference to new dependent(s)
case 4: principal unchanged, dependent(s) unchanged
      -> no action required

using System;
using System.Linq;
using System.Collections.Generic;

public abstract record BaseEntity
{
    public long Id { get; init; }

    public long? PredecessorId { get; private init; }

    public long? SuccessorId { get; private init; }

    public DateTimeOffset CreatedTimestamp { get; private init; }

    public bool IsDeleted { get; private init; }

    public DateTimeOffset DeletedTimestamp { get; private init; }

    public Guid CorrelationId { get; init; }
}

public record Product : BaseEntity
{
    public string Name { get; init; }

    public double Price { get; init; }
}

public record Order : BaseEntity
{
    public IReadOnlyCollection<Product> Products { get; init; }

    public double Total => Products.Sum(o => o.Price);
}