How can I track object versions using temporal tables in Entity Framework?

127 Views Asked by At

I have a front-end User Profile form that I would like to show a history of changes for.

The form is driven by a Profile object:

public class Profile
{
    public long Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
    public List<ProfileAddress> Addresses { get; set; }
}

public class ProfileAddress
{
    public long Id { get; set; }
    public long ProfileId { get; set; }
    public string City { get; set; }
    public string State { get; set; }
    public Profile Profile { get; set; }
}

After configuring these as temporal tables successfully, I would like to show a timeline of each edit made to the Profile and which fields were changed. This means one edit might have been the user updating just their email, while another edit might include an email change AND a new address. I attempt to retrieve the history:

var profileHistory = await _context.Profile
    .TemporalAll()
    .Include(x => x.Addresses)
    .Where(x => x.Id == 1);

And receive the following error: Navigation expansion is only supported for 'AsOf' temporal operation. For other operations use join manually.

A manual inner .Join() is not going to produce an accurate model as it will just duplicate the Profile for each Address. I am left having to use TemporalAsOf() operator which would require me to know when edits took place and perform tons of queries for each edit. I cannot imagine such a simple use case has this convoluted of a solution. How can I simply retrieve my history on an object with one-to-many relationships?

For further detail, my goal is to not have to query each table's history individually and cobble together my own history, or show each individual change. Since multiple changes can occur on a form in a single update, I am trying to get a list of all "versions" of the object so that I can compare what changed. My current custom solution is to serialize the full object into JSON and save it in a history table after every update, so that my front-end can run a json diff library and find the changes.

0

There are 0 best solutions below