The following code joins multiple tables with the TemporalAll function (e.g. FOR SYSTEM_TIME ALL) which are JOINED using a typical Id/FK join and "BETWEEN" condition to ensure all of the data is at a particular point in time. That works lovely. The problem is the inability to load related data such as "Patron" below using the EFCore "Include" feature. How should this be accomplished?
I found a similiar question (issue) which seems to indicate that this feature is not supported: https://github.com/dotnet/efcore/issues/26704
var query =
from rr in coreService.UnitOfWork.Repository<RewardsRedemption>().TemporalAll()!
from rcr in coreService.UnitOfWork.Repository<RewardsCatalogReward>().TemporalAll()!
.Where(rcr => rcr.Id == rr.Id)
.Where(rcr => EF.Property<DateTime>(rr, "ValidFrom") >= EF.Property<DateTime>(rcr, "ValidFrom") &&
EF.Property<DateTime>(rr, "ValidFrom") <= EF.Property<DateTime>(rcr, "ValidTo"))
.DefaultIfEmpty()
from p in coreService.UnitOfWork.Repository<Patron>().TemporalAll()!
.Where(p => p.Id == rr.PatronId)
.Where(p => EF.Property<DateTime>(rr, "ValidFrom") >= EF.Property<DateTime>(p, "ValidFrom") &&
EF.Property<DateTime>(rr, "ValidFrom") <= EF.Property<DateTime>(p, "ValidTo"))
.DefaultIfEmpty()
where
rr.Id == 1
select rr;
var data = await coreService.UnitOfWork.Repository<RewardsRedemption>().ListAsync(query, coreService.GetIncludesForDataRetrieve<RewardsRedemption>(defaultIncludesForDataRetrieve));
return coreService.AutoMapper.Map<IList<RewardsRedemption>, IReadOnlyCollection<FullRewardsRedemptionDTO>>(data!);