Querying Data in a System-Versioned Temporal Table #7216

5 Answer(s)

• 

  0

  ismcagdas created 4 years ago

  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

  Save Cancel
• 

  0

  samara081 created 4 years ago

  Hi @ismcagdas,

  Seems this is the only solution that supported by entity framwork

  Thanks for your support

  Save Cancel
• 

  0

  murat.yuceer created 4 years ago

  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");
          }
      }
  

  Save Cancel
• 

  0

  ismcagdas created 4 years ago

  Support Team

  @murat.yuceer

  Thank you for sharing this, I will check it.

  Save Cancel
• 

  0

  jtallon created 4 years ago

  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

  Save Cancel