How to implement data versioning on EntityFramework CodeFirst (version table method, TPC approach)?

Question

PostgreSQL

There was a task to implement data versioning in the database. The paradigm of creating version tables was chosen, that is, an EntityHistory table is created for each Entity table, in which there are all fields from Entity + DateModification (date of the entry modification), EntityId (link to the entity record in the Entity table), UserId (link to the user who made change), Operation (type of record change - create, update, delete, etc.). From such a scheme, the use of the TPC approach suggests itself: I create an Entity model, inherit EntityHistory from it, and specify the appropriate mapping in DataContext.OnModelCreating(). But at the same time, in the generated migration, commands are created to delete ForeignKeys on the Entity table from other tables, and ForeignKey is not created in the EntityHistory table on Entity. What am I doing wrong and how can I overcome this?
Examples:
EntityHistory.cs:

public class EntityHistory : Entity, IHistory
    {
        public DateTimeOffset DateModification { get; set; } 

        [Required]
        public int IdEntity { get; set; }

        [ForeignKey("IdEntity")]
        public virtual Entity Entity { get; set; }

        public int? IdOperation { get; set; }

        [Required]
        public string IdUser { get; set; }

        [ForeignKey("IdUser")]
        public AspNetUser User { get; set; }

        public EntityHistory(string idUser, int idEntity, int idOperation)
        {
            this.IdUser = idUser;
            this.IdEntity = idEntity;
            this.IdOperation = idOperation;
            this.DateModification = DateTimeOffset.Now;
        }

        private EntityHistory()
        {

        }

    }

Entity.cs:

public class Entity : AttributeAnnotatedValidator, Identified<int>
    {
        [Key]
        public int Id { get; set; }

        [Required(ErrorMessage = "Необходимо указать номер договора!")]
        public string Number { get; set; }

        ...

        [ForeignKey("Contractor")]
        [Required]
        public int ContractorId { get; set; }

        public Organization Contractor { get; set; }

        [ForeignKey("Customer")]
        public int? CustomerId { get; set; }

        public Organization Customer { get; set; }

        public List<EntityHistory> EntityHistory;
    }

DefaultConnection.cs :

public class DefaultConnection : DefaultStorageContext
    {
        public DbSet<AspNetUser> AspNetUsers { get; set; }
        public DbSet<Entity> Entity{ get; set; }
        public DbSet<Contractor> Contractors { get; set; }
        public DbSet<AgreementReason> AgreementReasons { get; set; }
        public DbSet<StateOfAgreement> StateOfAgreements { get; set; }
        public DbSet<AgreementStateFlow> AgreementStateFlow { get; set; }
        public DbSet<EntityHistory> EntityHistory { get; set; }
        public DbSet<Organization> Organizations { get; set; }
        ...

        public DefaultConnection()
        {
            Database.SetInitializer<DefaultConnection>(new DropCreateDatabaseIfModelChanges<DefaultConnection>());
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Conventions.Remove<ManyToManyCascadeDeleteConvention>();
            modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();
         modelBuilder.Conventions.Remove<OneToOneConstraintIntroductionConvention>();

            modelBuilder.Entity<EntityHistory>().Map(
                m => {
                    m.MapInheritedProperties();
                    m.ToTable("EntityHistory");
                });

            base.OnModelCreating(modelBuilder);
        }
}

incorrectly generated migration:

public partial class Create_EntityHistory : DbMigration
    {
        public override void Up()
        {
            DropForeignKey("dbo.AgreementManagedObjects", "AgreementId", "dbo.Entity");
            DropForeignKey("dbo.AgreementStateFlows", "AgreementId", "dbo.Entity");
            DropForeignKey("dbo.ExportAgreements", "Agreement_Id", "dbo.Entity");
            DropIndex("dbo.AgreementManagedObjects", new[] { "EntityId" });
            DropIndex("dbo.AgreementStateFlows", new[] { "EntityId" });
            DropIndex("dbo.ExportAgreements", new[] { "Entity_Id" });
            DropPrimaryKey("dbo.Entity");
            CreateTable(
                "dbo.EntityHistory",
                c => new
                    {
                        Id = c.Int(nullable: false),
                        Number = c.String(nullable: false),
                        ...
                        DateModification = c.DateTimeOffset(nullable: false, precision: 7),
                        IdEntity = c.Int(nullable: false),
                        IdOperation = c.Int(),
                        IdUser = c.String(nullable: false, maxLength: 128),
                    })
                .PrimaryKey(t => t.Id)
                .ForeignKey("dbo.Organizations", t => t.ContractorId)
                .ForeignKey("dbo.Organizations", t => t.CustomerId)
                .ForeignKey("dbo.AspNetUsers", t => t.IdUser)
                .Index(t => t.ContractorId)
                .Index(t => t.CustomerId)
                .Index(t => t.IdUser);

            AlterColumn("dbo.Entity", "Id", c => c.Int(nullable: false));
            AddPrimaryKey("dbo.Entity", "Id");            
        }
    }
}

Answer 1

Are you sure you need to torture EF with this? I think data versioning is one of the cases when you should use triggers, and not raise versioning to the level of an object database.

Answer 2

Cant EF apparently, in my ASP.Net Core (SQLite), EF in migration first deletes all indexes, then creates again, and for a table that has not changed in the model. Rule the hands of migration.