Using .Net 7 Entity Framework, I have a system of tables that mark as Temporal, so that I can reconstruct the data structure from the past (i.e. when a user makes a change, I should be able to roll back the change to a previous state). For the most part, it seems to work fine, but when I try to query a table that has Many-To-Many relationships, that’s where the issues arise.

Here is my code:

 var timeConstraints = dbContext
     .MyParrentDB
     .TemporalAll()
     .Where(c => c.Id == id)                  
     .Select(
         c => new
         {             
             PeriodStart = EF.Property<DateTime>(c, "PeriodStart"),
             PeriodEnd = EF.Property<DateTime>(c, "PeriodEnd"),
         })
     .Single();

 var foundItem = await dbContext.MyParrentDB
     .TemporalFromTo(timeConstraints.PeriodStart, timeConstraints.PeriodEnd)
     .Where(c => c.Id == id)
     .Include(x => x.ExampleManyToManyTable!.Where(p => p != null))
          .ThenInclude(c => c.ChildTable)
     .FirstOrDefaultAsync();
 

But this throws an error that Navigation expansion is only supported for ‘AsOf’ temporal operation. For other operations use join manually. So, what is the correct way to query the temporal table? Here is how I setup the tables and their relationships:

public class MyParrentDB 
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;     
    public string Description { get; set; } = string.Empty;       
    public ICollection<ExampleManyToManyTable>? ExampleManyToManyTable { get; set; } 
}
 public class ExampleManyToManyTable
 {
     public int MyParrentDBId { get; set; }
     public MyParrentDB MyParrentDB { get; set; }
     public int ChildTableId { get; set; }
     public ChildTable ChildTable { get; set; }
 }
public class ChildTable
{
    public int Id { get; set; }
    public string ChildName { get; set; } = string.Empty;
    public ICollection<ExampleManyToManyTable>? ExampleManyToManyTable { get; set; }
}

Setting up the table relationships: 

    public DbSet<MyParrentDB> MyParrentDB { get; set; }
    public DbSet<ExampleManyToManyTable> ExampleManyToManyTable { get; set; }
    public DbSet<ChildTable> ChildTable { get; set; }   
    
       modelBuilder.Entity<ExampleManyToManyTable>()
          .HasKey(bc => new { bc.MyParrentDBId, bc.ChildTableId });
       modelBuilder.Entity<ExampleManyToManyTable>()
          .HasOne(bc => bc.MyParrentDB)
          .WithMany(b => b.ExampleManyToManyTable)
          .HasForeignKey(bc => bc.MyParrentDBId);
       modelBuilder.Entity<ExampleManyToManyTable>()
          .HasOne(bc => bc.ChildTable)
          .WithMany(c => c.ExampleManyToManyTable)
          .HasForeignKey(bc => bc.ChildTableId);   
           
       modelBuilder.Entity<MyParrentDB>().Property<DateTime>("PeriodStart");
       modelBuilder.Entity<MyParrentDB>().Property<DateTime>("PeriodEnd");
       
       modelBuilder.Entity<MyParrentDB>().ToTable("MyParrentDB", c => c.IsTemporal());
       modelBuilder.Entity<ChildTable>().ToTable("ChildTable", c => c.IsTemporal());
       modelBuilder.Entity<ExampleManyToManyTable>().ToTable("ExampleManyToManyTable", c => c.IsTemporal());