Temporal property optimization with Entity Framework

Question

I want to implement temporal properties using an approach similar to that described here, using Entity Framework code-first for database storage.

I want it optimized for getting the current value and have lazy loading for the history, but I don't want to have to add boilerplate code in the parent entity for every usage, as is the approach in the link above.

At the moment I have something like the code below, which by convention results in the database schema as shown below the code.

This will function as I need, but for performance reasons I'd like to avoid the join it requires to get the current property value (i.e. I want to move the TemporalStrings.CurrentValue DB column to Entities.Name instead).

If I try

modelBuilder.Entity<Entity>().Property(o => o.Name.CurrentValue).HasColumnName("Name");

it doesn't work. I get an exception like

The type 'ConsoleApplication1.TemporalString' has already been configured as an entity type. It cannot be reconfigured as a complex type.

Is there some way I can achieve this mapping, or is there a better approach for achieving this functionality?

Code:

public class TemporalString
{
    public int Id { get; set; }
    public string CurrentValue { get; set; } // Setter would be customized to append to History.
    public virtual List<TemporalStringValue> History { get; set; }
    // Other methods such as string ValueAt(DateTime) would exist.
}

public class TemporalStringValue
{
    public int Id { get; set; }
    public DateTime EffectiveFrom { get; set; }
    public string Value { get; set; }
}

public class Entity
{
    public int Id { get; set; }
    public virtual TemporalString Name { get; set; }
}

public class TestDbContext : DbContext
{
    public DbSet<Entity> Entities { get; set; }
    public DbSet<TemporalString> TemporalStrings { get; set; }
    public DbSet<TemporalStringValue> TemporalStringValues { get; set; }
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        //modelBuilder.Entity<Entity>().Property(o => o.Name.CurrentValue).HasColumnName("Name");
        // TODO: Map DB column TemporalStrings.CurrentValue to DB column Entities.Name?
    }
}

internal class Program
{
    private static void Main(string[] args)
    {
        Database.SetInitializer(new DropCreateDatabaseIfModelChanges<TestDbContext>());
        using (var context = new TestDbContext())
        {
            var entity = new Entity
                             {
                                 Name = new TemporalString
                                            {
                                                CurrentValue = "Current Value",
                                                History = new List<TemporalStringValue>
                                                              {
                                                                  new TemporalStringValue
                                                                      {
                                                                          EffectiveFrom = DateTime.UtcNow,
                                                                          Value = "Current Value"
                                                                      },
                                                                  new TemporalStringValue
                                                                      {
                                                                          EffectiveFrom = DateTime.UtcNow.AddMonths(-1),
                                                                          Value = "Old Value"
                                                                      },
                                                                  new TemporalStringValue
                                                                      {
                                                                          EffectiveFrom = DateTime.UtcNow.AddMonths(-2),
                                                                          Value = "Older Value"
                                                                      }
                                                              }
                                            }
                             };
            context.Entities.Add(entity);
            context.SaveChanges();
        }
        Console.Write("Done.");
        Console.ReadKey();
    }
}

Resulting schema:

Entities
(PK) Id
(FK) Name_Id (references TemporalStrings.Id)

TemporalStrings
(PK) Id
     CurrentValue

TemporalStringValues
(PK) Id
     EffectiveFrom
     Value
(FK) TemporalString_Id

Desired schema:

Entities
(PK) Id
(FK) Name_Id (references TemporalStrings.Id)
     Name (formerly TemporalStrings.CurrentValue)

TemporalStrings
(PK) Id

TemporalStringValues
(no change)

Constipate

I don't know how you expect to avoid a join from Entities to TemporalStrings. How do you expect TemporalStrings to be updated when you change the value of Entities? If you read the property then you can get away with caching the Name in your Entity. Otherwise, you need to set something up akin to a OnPropertyChangedEvent that updates the TemporalString so it has the most recent change when you write to Name. – Tufts

Thanks for taking a look at this Mike. I'm not sure if I understand your questioning completely. I have updated my question to try to make it more clear. – Constipate

In terms of updating the temporal property value, I've got my implementation set up so that when entity.Name.CurrentValue = "New Value" is executed, the setter also appends to the History with the current date/time and the new value. So the current value will always be simultaneously held in entity.Name.CurrentValue and the latest entity.Name.History entry. – Constipate

I've also added the desired schema at the bottom of the question to make that clear. I'd like to be able to get something like name = context.Entities.First().Name.CurrentValue without having to join to the TemporalStrings table. – Constipate

TemporalStrings serves no purpose in your new schema. Unless you want to re-use the table across multiple tables. – Tufts

Hi Mike. I am indeed reusing the TemporalString class across multiple entities and sometimes even multiple times on the same entity. For example, for the code above you might also have on the Entity class something like public virtual TemporalString Description { get; set; }. – Constipate

Very interesting question. Have you solved this the way you wanted, or perhaps you found another way of doing it? Could you answer your own question if so? I would love to see your final approach here. – Riven

Hi @julealgon. I'm using this temporal property approach successfully but I haven't found an answer to this question, which would be a good performance improvement. I'm able to get away with the performance hit in the system I'm working with due to the nature of the data volume, etc. If a solution could be found it would make a great generic way to deal with temporal properties. – Constipate