How to Track Full Update History of TPT Entities in EF Core with MySQL [duplicate]

Question

I'm designing a .NET Core application using EF Core with a MySQL database, following a Table-per-Type (TPT) inheritance strategy.

I have a base Report class with several derived types such as QualityReport, MaintenanceReport, SafetyReport, etc. As expected with TPT, each derived type maps to its own table.

I want to implement a custom audit system that logs every update to any Report or its derived types. showing the old and new values when a record is updated, or deleted.

I know that SQL Server supports system-versioned (temporal) tables for this kind of use case, but since I'm using MySQL, that option isn't available.

What path should I follow to implement a reliable, property-level audit system that captures all changes across TPT entities in EF Core with MySQL?

// Base report class with polymorphism
[Table("Reports")]
[JsonPolymorphic(TypeDiscriminatorPropertyName = "Type")]
[JsonDerivedType(typeof(QualityReport), "qualityReport")]
[JsonDerivedType(typeof(MaintenanceReport), "maintenanceReport")]
[JsonDerivedType(typeof(SafetyReport), "safetyReport")]
public abstract class Report
{
    [Key]
    public int Id { get; set; }

    [Required]
    public DateTime CreatedDate { get; set; }

    [Required]
    public DateTime UpdatedDate { get; set; }

    [Required, MaxLength(200)]
    public string Title { get; set; } = null!;

    [Required, MaxLength(100)]
    public string CreatedBy { get; set; } = null!;

    [MaxLength(100)]
    public string? UpdatedBy { get; set; }
}

public class QualityReport : Report
{
    public string QualityMetric { get; set; }
    public string InspectorName { get; set; }
    public DateTime InspectionDate { get; set; }
    public string Summary { get; set; }
    public int SeverityLevel { get; set; }
}

Is the question about changes to report structure (adding and removing columns in the reports) or changing calculations for a report column? — Shadow
– Shadow, Commented Jul 13 at 12:01
@Shadow I'm talking about tracking data changes not changes to the database structure or how values are calculated. I want to log which fields were changed and what their old and new values were whenever a record is updated, or deleted. — Kafkaa
– Kafkaa, Commented Jul 13 at 12:04

Lajos Arpad · Accepted Answer · 2025-07-13 12:14:54Z

You can create a log table of the format of

log_myentity(id, vid, entity_id, fieldname, type, oldvalue, newvalue)

and a trigger on insert and update (perhaps delete too) that inserts a log entry into this table whenever the trigger fires.

myentity: the entity whose log you work with (you could also create one huge log table for all entities as an alternative, so it is not written in stone that you need a separate table for all your entities)
id: the id of the log
vid: version id. You may have an ever-increasing vid in the myentity table this refers to which you have inside the update (and possibly delete) trigger and which is trivial in case of insert
entity_id: the id of the entity the log represents
fieldname: the name of the field involved
type: the type of the field at the time of the change
oldvalue: the value it changed from
newvalue: the value it changed to

Collectives™ on Stack Overflow

How to Track Full Update History of TPT Entities in EF Core with MySQL [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related