Allow temporal Period properties to be mapped to CLR properties (i.e. not shadow properties)

[FransBouma]

(.NET 6, EF Core 6 RC2/latest)

I generate the fields in an entity which are mapped to the period fields in a temporal table as readonly properties and map them as normal fields, however when using them in a query I get:

System.InvalidOperationException : Period property 'Employee.ValidFrom' must be a shadow property.
   at Microsoft.EntityFrameworkCore.SqlServer.Infrastructure.Internal.SqlServerModelValidator

Why do the period properties have the requirement to be shadow properties? I could generate them as such but IMHO it sucks for the user as to use them they have to fall back to string based names for the fields...

[ajcvickers]

@FransBouma We think they make more sense as shadow properties since most uses of the entity do not need the period properties. (Essentially, the same reason they are hidden by default in the table.) That being said, we will enable mapping them to non-shadow properties in 7.0.

[FransBouma]

@ajcvickers 👍
I have it working now, having the fields in the entity definition but not mapped in the modelbuilder. The main reason I did keep them in my own framework (instead of hiding them) is also because when you fetch a set of these entities, you can use the values for the period e.g. in a UI or other means. But I admit, it's a subjective thing: do they belong to the entity or are they strictly there to help the DB do its bookkeeping.

[dragos-durlut]

Based on a solution from user @StevenRasmussen (from this thread ) I figured out a hack to map shadow properties to fields in the entity.

Part of the inspiration came from user @FransBouma as well.

The solution is not perfect but it does the job without having to alter too much of the code. This is done over efcore 6.0.1 version.
Code might not work on an upgrade.

The interface I used to keep track of things

public interface ITemporalEntity
{   
	DateTime FromSysDate { get; set; }
	DateTime ToSysDate { get; set; }
}

An example of the implemented entity, do not forget to add NotMapped to the interface members.

public class SomeTemporalEntity : ITemporalEntity
{   
	public int Id {get; set;}
	
	public string Name {get; set;}
	
	[NotMapped]
	DateTime FromSysDate { get; set; }
	
	[NotMapped]
	DateTime ToSysDate { get; set; }
}

The constants class so we do not have to hardcode everything:

public static class TemporalEntityConstants
{
	public static readonly string FromSysDate = "FromSysDate";
	public static readonly string ToSysDate = "ToSysDate";

	public static readonly string FromSysDateShadow = "FromSysDateShadow";
	public static readonly string ToSysDateShadow = "ToSysDateShadow";
}

The overload of EntityMaterializerSource and implementation of IEntityMaterializerSource

using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Metadata;
using Microsoft.EntityFrameworkCore.Query.Internal;
using Microsoft.EntityFrameworkCore.Storage;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;


#pragma warning disable EF1001 // Internal EF Core API usage.
public class TemporalEntityMaterializerSource : EntityMaterializerSource, Microsoft.EntityFrameworkCore.Query.IEntityMaterializerSource
{
	public TemporalEntityMaterializerSource(EntityMaterializerSourceDependencies dependencies) : base(dependencies)
	{
	}

	public override Expression CreateMaterializeExpression(IEntityType entityType, string entityInstanceName, Expression materializationContextExpression)
	{
		var baseExpression = base.CreateMaterializeExpression(entityType, entityInstanceName, materializationContextExpression);
		if (entityType.ClrType.GetInterfaces().FirstOrDefault(i => i == typeof(ITemporalEntity)) != null)
		{
			var fromSysDatePropertyInfo = entityType.ClrType.GetProperty(nameof(ITemporalEntity.FromSysDate));
			var toSysDatePropertyInfo = entityType.ClrType.GetProperty(nameof(ITemporalEntity.ToSysDate));

			var shadowPropertiesHashSet = new HashSet<IPropertyBase>(
			entityType.GetServiceProperties().Cast<IPropertyBase>()
				.Concat(
					entityType
						.GetProperties()
						.Where(p => p.IsShadowProperty()))
				);

			var blockExpressions = new List<Expression>(((BlockExpression)baseExpression).Expressions);
			var instanceVariable = blockExpressions.Last() as ParameterExpression;

			var valueBufferExpression = Expression.Call(materializationContextExpression, MaterializationContext.GetValueBufferMethod);

			var bindingInfo = new ParameterBindingInfo(
									entityType,
									materializationContextExpression);

			var temporalBlockExpressions = new List<Expression>();
			foreach (var shadowPropertyBase in shadowPropertiesHashSet)
			{
				var shadowPropertyMemberType = typeof(DateTime);
				var readValueExpression =
					valueBufferExpression.CreateValueBufferReadValueExpression(
							shadowPropertyMemberType,
							shadowPropertyBase.GetIndex(),
							shadowPropertyBase);
				if (shadowPropertyBase.Name == TemporalEntityConstants.FromSysDateShadow)
					temporalBlockExpressions.Add(CreateMemberAssignment(instanceVariable, fromSysDatePropertyInfo, shadowPropertyBase, readValueExpression));
				if (shadowPropertyBase.Name == TemporalEntityConstants.ToSysDateShadow)
					temporalBlockExpressions.Add(CreateMemberAssignment(instanceVariable, toSysDatePropertyInfo, shadowPropertyBase, readValueExpression));
			}

			blockExpressions.InsertRange(blockExpressions.Count - 1, temporalBlockExpressions);

			return Expression.Block(new[] { instanceVariable }, blockExpressions);
		}

		return baseExpression;

		static Expression CreateMemberAssignment(Expression parameter, MemberInfo memberInfo, IPropertyBase property, Expression value)
		{
			if (property.IsIndexerProperty())
				return Expression.Assign(
					Expression.MakeIndex(
						parameter, (PropertyInfo)memberInfo, new List<Expression> { Expression.Constant(property.Name) }),
					value);
			return Expression.MakeMemberAccess(parameter, memberInfo).Assign(value);
		}
	}
}
#pragma warning restore EF1001 // Internal EF Core API usage.

The setup in DbContext:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
	optionsBuilder.ReplaceService<IEntityMaterializerSource, TemporalEntityMaterializerSource>();
	base.OnConfiguring(optionsBuilder);
}

Not sure if necessary in Startup:

services.AddScoped<IEntityMaterializerSource, TemporalEntityMaterializerSource>();

How to setup the entity tables:

TemporalTableBuilder<TEntity> temporalTableBuilder
temporalTableBuilder.HasPeriodStart(TemporalEntityConstants.FromSysDateShadow).HasColumnName(TemporalEntityConstants.FromSysDate)
temporalTableBuilder.HasPeriodEnd(TemporalEntityConstants.ToSysDateShadow).HasColumnName(TemporalEntityConstants.ToSysDate)

On querying the data, you will have to still use EF.Property:

public Task<List<TEntity>> GetAllAsync(Expression<Func<TEntity, bool>> criteria)
	=> DbSet.Where(criteria)
	.OrderBy(x => EF.Property<DateTime>(x, TemporalEntityConstants.FromSysDateShadow))
	.ThenBy(x => EF.Property<DateTime>(x, TemporalEntityConstants.ToSysDateShadow))
	.ToListAsync();

That is about it. I will add more if I find any bugs.

[dragos-durlut]

I've added a demo for this implementation on a temporal tables demo solution.
It can be found here (branch period-properties).

I have renamed PeriodStart -> FromSysDate & PeriodEnd -> ToSysDate for easier debugging. (I had some issues with following the correct columns)

A note is that you have to be careful with migrations.
In some instances the [NotMapped] attribute does not help and has to be temporarily commented out, generate the migration and then put it back in.

Then column names have to be swapped:

/*b.Property<DateTime>("FromSysDate")
	.HasColumnType("datetime2")
	.HasColumnName("FromSysDate");*/

b.Property<DateTime>("FromSysDateShadow")
	.ValueGeneratedOnAddOrUpdate()
	.HasColumnType("datetime2")	
	.HasColumnName("FromSysDate");

/*b.Property<DateTime>("ToSysDate")
	.HasColumnType("datetime2")
	.HasColumnName("ToSysDate");*/

b.Property<DateTime>("ToSysDateShadow")
	.ValueGeneratedOnAddOrUpdate()
	.HasColumnType("datetime2")	
	.HasColumnName("ToSysDate");

You can see the list of changes here (commit 1597590)

[burtonrodman]

Maybe I'm using temporal tables wrong ;)

It makes perfect sense to me that I would present status messages throughout my UI based on the Period column values:
ex: $"Last edited by {row.ModifiedBy} on {row.PeriodStart}"

While accessing shadow properties is workable for simple scenarios, if I'm doing a more complex history query where for example, I need to find the last edit date of the most recently edited row from a navigation property, there is no practical way to state this query in EF/LINQ based on shadow properties of a navigation target.

I don't know if this needs to be said, but to be crystal of what I'm expecting, please allow mapping Period columns EXACTLY like all other mapped properties on an entity (from a query perspective).

P.S.
and while we're at it, why not take that last statement to its logical end and make the fluent API for mapping temporal tables and period columns match with mapping any other table/property:

modelBuilder.Entity<MyTemporalTable>(entity => {
  entity.ToTable(nameof(MyTemporalTable)).IsTemporal("MyTemporalTableHistory");
  entity.Property(e => e.SysStartTime).IsPeriodStart();
});