How to Apply Date Range Filtering in a Temporal Table Using LINQ Query?

Question

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.

Answer 1

One of the best approaches, is select columns ValidFrom and ValidTo in your retrieval process by select, and apply condition for special Ids in your scenario. (Without apply specific Ids, be aware that it have performance issue because it retrieves all data.)

   // Retrieve all historical data for the given IDs in one query
   var historicalData = await context.Addresses
       .TemporalAll()
       .Where(h => ids.Contains(h.Id))
       .OrderBy(c => EF.Property<DateTime>(c, "ValidFrom"))
       .Select(
           c => new
           {
               Id = c.Id,
               Address = c,
               ValidFrom = EF.Property<DateTime>(c, "ValidFrom"),
               ValidTo = EF.Property<DateTime>(c, "ValidTo")
           })
       .ToListAsync();

And then you can apply your condition with current data set which are in the memory.

Something like this:

 address = historicalData
                    .Where(h => h.Id == addressId &&
                                h.ValidFrom <= date &&
                                h.ValidTo >= date)
                    .OrderByDescending(h => h.ValidFrom)
                    .Select(h => h.Address)
                    .FirstOrDefault();