Open Closed

Querying Data in a System-Versioned Temporal Table #7216


0
samara081 created

We are implementing a solution to query a temporal of any table.

As you know, when enable a temporal table on sql server for any table, sql will automatically add a second table with extra “_History” at the end of the table to track history. For example if we have “student” table, sql server will add “student_History” table.

To query the student history, all what we need is querying student table and add “FOR SYSTEM_TIME AS OF '2015-09-01 T10:00:00.7230011' ;” at the end of the statement. So instead of write:

Select * from student

We will write: Select * from student FOR SYSTEM_TIME AS OF '2015-09-01 T10:00:00.7230011'

Is there any way to automatically append this statement at the end of the query?

It is like intercepting the query and applying query filter like soft table, but now it is not filter, it is just statement at the end of the statement.


5 Answer(s)
  • 0
    ismcagdas created
    Support Team

    Hi @samara081,

    Currently I'm not sure how to create a generic solution for this use-case.

    For now, you can create a custom repository (see https://aspnetboilerplate.com/Pages/Documents/Repositories#custom-repositories) and manually execute the SQL query on DbContext using FromSql as explained in https://www.eidias.com/blog/2018/8/29/using-sql-temporal-tables-with-entity-framework-core

  • 0
    samara081 created

    Hi @ismcagdas,

    Seems this is the only solution that supported by entity framwork

    Thanks for your support

  • 0
    murat.yuceer created

    I did own generic sulition maybe @ismcagdas could add boilerplate

        public interface IHistoryTable
        {
            Guid LogId { get; set; }
    
            string EntityState { get; set; }
    
            DateTime EntityOperationDate { get; set; }
        }
        
        public interface IHaveHistoryTable<T> where T : IHistoryTable
        {
        }
        
         public class UserTaskHistory : UserTask, IHistoryTable
        {
            public Guid LogId { get; set; }
            public string EntityState { get; set; }
            public DateTime EntityOperationDate { get; set; }
        }
    
        public class UserTask : FullAuditedAggregateRoot<Guid>, IMayHaveTenant, IHaveHistoryTable<UserTaskHistory>
        {
            public int? TenantId { get; set; }
    
            public long UserId { get; protected set; }
            public User User { get; protected set; }
    
            public string Title { get; protected set; }
    
            public virtual string Description { get; protected set; }
        }
        
        public static class ModelBuilderExtensions
        {
            public static ModelBuilder EntitiesOfType<T>(this ModelBuilder modelBuilder,
                Action<EntityTypeBuilder> buildAction) where T : class
            {
                return modelBuilder.EntitiesOfType(typeof(T), buildAction);
            }
    
            public static ModelBuilder EntitiesOfType(this ModelBuilder modelBuilder, Type type,
                Action<EntityTypeBuilder> buildAction)
            {
                foreach (var entityType in modelBuilder.Model.GetEntityTypes())
                    if (type.IsAssignableFrom(entityType.ClrType))
                        buildAction(modelBuilder.Entity(entityType.ClrType));
    
                return modelBuilder;
            }
    
            public static ModelBuilder ApplyConfiguration<T>(this ModelBuilder modelBuilder, Type configurationType, Type entityType)
            {
                if (typeof(T).IsAssignableFrom(entityType))
                {
                    // Build IEntityTypeConfiguration type with generic type parameter
                    var configurationGenericType = configurationType.MakeGenericType(entityType);
                    // Create an instance of the IEntityTypeConfiguration implementation
                    var configuration = Activator.CreateInstance(configurationGenericType);
                    // Get the ApplyConfiguration method of ModelBuilder via reflection
                    var applyEntityConfigurationMethod = typeof(ModelBuilder)
                        .GetMethods()
                        .Single(e => e.Name == nameof(ModelBuilder.ApplyConfiguration)
                                     && e.ContainsGenericParameters
                                     && e.GetParameters().SingleOrDefault()?.ParameterType.GetGenericTypeDefinition() == typeof(IEntityTypeConfiguration<>));
                    // Create a generic ApplyConfiguration method with our entity type
                    var target = applyEntityConfigurationMethod.MakeGenericMethod(entityType);
                    // Invoke ApplyConfiguration, passing our IEntityTypeConfiguration instance
                    target.Invoke(modelBuilder, new[] { configuration });
                }
    
                return modelBuilder;
            }
        }
        
        public partial class DbContext : AbpZeroDbContext<Tenant, Role, User, KuysDbContext>, IAbpPersistedGrantDbContext
        {
                    public virtual DbSet<UserTask> UserTasks { get; set; }
                     public virtual DbSet<UserTaskHistory> UserTaskHistories { get; set; }
                     
             protected override void OnModelCreating(ModelBuilder modelBuilder)
            {
                foreach (var entityType in modelBuilder.Model.GetEntityTypes())
                {
                    modelBuilder.ApplyConfiguration<IHistoryTable>(typeof(EntityHistoryConfiguration<>), entityType.ClrType);
                }
            }
            
            public override async Task<int> SaveChangesAsync(CancellationToken cancellationToken = new CancellationToken())
            {
                await SaveChangesToHistoryAsync(cancellationToken);
    
                return await base.SaveChangesAsync(cancellationToken);
            }
    
            private async Task SaveChangesToHistoryAsync(CancellationToken cancellationToken = new CancellationToken())
            {
                var historyProvidedentries = ChangeTracker
                    .Entries()
                    .Where(e => !(e.Entity is IHistoryTable) &&
                                e.State != EntityState.Unchanged &&
                                e.Entity.GetType().GetInterfaces().Any(x => x.IsGenericType && x.GetGenericTypeDefinition() == typeof(IHaveHistoryTable<>)))
                    .ToList();
    
                foreach (var entityEntry in historyProvidedentries)
                {
                    var historyType = (from iType in entityEntry.Entity.GetType().GetInterfaces()
                        where iType.IsGenericType && iType.GetGenericTypeDefinition() == typeof(IHaveHistoryTable<>)
                        select iType.GetGenericArguments()[0]).First();
    
                    var historyEntity = Activator.CreateInstance(historyType) as IHistoryTable;
                    historyEntity.LogId = Guid.NewGuid();
                    historyEntity.EntityOperationDate = Clock.Now;
                    historyEntity.EntityState = entityEntry.State.ToString();
                    foreach (var property in entityEntry.Properties)
                    {
                        historyType.GetProperty(property.Metadata.Name).SetValue(historyEntity, property.CurrentValue, null);
                    }
                    await AddAsync(historyEntity, cancellationToken);
                }
            }
        }
        
        public class EntityHistoryConfiguration<T> : IEntityTypeConfiguration<T> where T : class, IHistoryTable
        {
            public void Configure(EntityTypeBuilder<T> builder)
            {
                var baseType = builder.Metadata.BaseType;
                baseType.RemoveDiscriminatorValue();
    
                builder.ToTable(baseType.ClrType.Name + "History", baseType.GetSchema());
                builder.HasBaseType((Type)null);
                builder.HasNoDiscriminator();
                builder.Ignore("Id");
                builder.HasKey(p => p.LogId);
                builder.Property("Id").HasColumnName("EntityId");
            }
        }
    
  • 0
    ismcagdas created
    Support Team

    @murat.yuceer

    Thank you for sharing this, I will check it.

  • 0
    jtallon created

    HI samara081,

    Would it be possible for you to share how you hooked up registering the tables to be system versioned?

    I was taking a look at EntityFrameworkCore.TemporalTables @ https://github.com/findulov/EntityFrameworkCore.TemporalTables, but its not picking up to to make the changes within the AspNetZero framework.

    I am sure I am mostlikely missing something.

    Would apprciate any insight?

    Regards John