Temporal tables with Linq2Sql

Question

I've got a generic repository to either get en entity by ID or to get all entities:

    internal class Repository<TEntity> : IRepository<TEntity>
        where TEntity : BaseEntity
    {
        protected SaiContext Context { get; }

        /// <summary>Gets the entity set.</summary>
        protected virtual DbSet<TEntity> Set => Context.Set<TEntity>();

        public Repository(SaiContext context)
        {
            Context = context;
        }

        public async Task<TEntity> GetAsync(int entityId, IEnumerable<string> includeProperties = null)
        {
            try
            {
                return await GetQueryableWithIncludes(includeProperties).SingleAsync(entity => entity.Id == entityId);
            }
            catch (InvalidOperationException)
            {
                throw new EntityNotFoundException(typeof(TEntity), entityId);
            }
        }

        public async Task<IEnumerable<TEntity>> GetAllAsync(IEnumerable<string> includeProperties = null)
        {
            return await GetQueryableWithIncludes(includeProperties).ToListAsync();
        }

        protected IQueryable<TEntity> GetQueryableWithIncludes(IEnumerable<string> includeProperties = null)
        {
            IQueryable<TEntity> queryable = Set;

            if (includeProperties == null)
            {
                return queryable;
            }

            foreach (var propertyName in includeProperties)
            {
                queryable = queryable.Include(propertyName);
            }

            return queryable;
        }
    }

After having configured the DbContext for entity relations, navigation properties along with all the rest is being loaded correctly for all entities.

Now I've been asked to use temporal SQL tables so that all entities have a validity range.

With SQL I'd include FOR SYSTEM_TIME AS OF @validityDate in the query.

What is the easiest way (if there is any) to adapt the existing implementation in order to respect @validityDate?

What I've tried:

1. Look for a way to configure the wanted system time when performing the SQL query. ISSUE: I couldn't find a way.
2. Expose the query through a table valued function allowing to pass @validityDate as a parameter. ISSUE: I can't pass the parameter using Linq2Sql (or at least I didn't figurte out how).
3. Create a table valued function performing the joins (instead of letting EF do them) so it can be called with context.FromSqlRaw(<query>). ISSUE: How to create the c# object tree? (multiple rows are being returned as there are 1 to many relations)

All examples using temporal tables I've found use FromSqlRaw. If possible I'd like to avoid it, as it means that the entire DB context sonfiguration becomes useless and additional code for the mappings has to be included.

Answer 1

I've found the solution with the efcore-temporal-query (nuget) library.

The code has been adapted to use temporal tables as described in the README.

The repository methods now accept an optional parameter validityDate:

    public async Task<TEntity> GetAsync(int entityId, DateTime? validityDate = null, IEnumerable<string> includeProperties = null)
    {
        try
        {
            var query = GetQueryableWithIncludes(includeProperties);
            query = GetQueryableWithValidityDate(query, validityDate);
            return await query.SingleAsync(entity => entity.Id == entityId);
        }
        catch (InvalidOperationException)
        {
            throw new EntityNotFoundException(typeof(TEntity), entityId);
        }
    }

    protected IQueryable<TEntity> GetQueryableWithIncludes(IEnumerable<string> includeProperties = null)
    {
        IQueryable<TEntity> queryable = Set;

        if (includeProperties == null)
        {
            return queryable;
        }

        foreach (var propertyName in includeProperties)
        {
            queryable = queryable.Include(propertyName);
        }

        return queryable;
    }

    private static IQueryable<TEntity> GetQueryableWithValidityDate(IQueryable<TEntity> query, DateTime? validityDate)
    {
        return validityDate.HasValue ? query.AsOf(validityDate.Value) : query;
    }

Where te relevant part for the historized query is query.AsOf(validityDate.Value).