I have a temporal table in SQL Server that stores versions of addresses(version-history) table, with ValidFrom and ValidTo columns representing the version history. In my application, I’ve configured the Address entity using Entity Framework with the temporal table settings as follows:

public class Address : EntityBase
{
    public string Name { get; set; }
    public string AddressString { get; set; }
    public bool IsDeleted { get; set; }
    public DateTime? DateDeleted { get; set; }   
    public string? DeletedBy { get; set; }   
}

protected override void ConfigureCore(EntityTypeBuilder<Address> builder)
{
    builder.ToTable("Addresses", b => b.IsTemporal(
    b =>
    {
        b.HasPeriodStart("ValidFrom");
        b.HasPeriodEnd("ValidTo");
        b.UseHistoryTable("AddressesHistory");
    }));
}

For getting one address I apply the bellow linq code and it is ok:

        public async Task<Address> GetAtDate(long Id, DateTime utcDate)
        {
            return await context.Addresses.TemporalAsOf(utcDate).FirstOrDefaultAsync(h => h.Id == Id);
        }

But for getting addresses, this approach does not work for me.

I want to filter addresses based on a date range, but I’m having trouble writing the correct LINQ query to achieve this. The ValidFrom and ValidTo date filters are not being applied as expected.

my current code:

public async Task<List<Address>> GetAddressesAtDatesAsync(List<Tuple<long, long>> idDateTicksList)
{
    // Convert to a dictionary for better access to data.
    var idDateDictionary = idDateTicksList.ToDictionary(
        item => item.Item1,
        item => new DateTime(item.Item2)
    );

    // Get all addresses with id.
    var ids = idDateDictionary.Keys.ToList();
    var addresses = await context.Addresses.Where(h => ids.Contains(h.Id)).ToListAsync();

    // Filter addresses by date tickes.
    var result = addresses
        .Where(h => idDateDictionary.ContainsKey(h.Id) && h.ValidFrom <= idDateDictionary[h.Id] && h.ValidTo >= idDateDictionary[h.Id])
        .ToList();

    return result ?? new List<Address>();
}

The query doesn’t seem to apply the date filtering correctly. I ensure dates are correct, because I wrote SQL query and it is correct.. How should I modify this LINQ query to correctly filter addresses by the validfrom and validTo date range in a temporal table?

get multiple data by date range in a temporal table.