Add SQL Server temporal tables support

[jmciMiS]

Is your feature request related to a problem?

I had to to use sql temporal tables for a project.
First I tried mapping the initial iqueriable to a dto that includes the periodStart property.
Found that this was lagging on my queries to the database specially overfetching fields and relationships while using Projections. Decided to use typeExtensions and a resolver with a data loader for fetching the PeriodStart property in case this field is asked for.
This part looks like this:
`

 public class Query
    {
       
        [UsePaging]
        [UseProjection]
        [UseFiltering]
        [UseSorting]
        public  IQueryable<Foo> GetFoo(
            [ScopedService]ApplicationDbContext context 
    
        ) 
        {
            var foo= context.Foo;     
            return Foo;
        }
    }

`

`

public class FooTypeExtension : ObjectTypeExtension<Foo>
    {
        protected override void Configure(IObjectTypeDescriptor<Foo> descriptor)
        {
            descriptor.Field("timeStamp")
            .Type<DateTimeType>()
            
            .Resolve(async(context, ct)=>{
                Foo parent = context.Parent<Foo>();
                
                DateTime date =  await context.Service<FooTimeStampDataLoader>      ().LoadAsync(parent.ID, ct);
                return TimeZoneInfo.ConvertTimeFromUtc(date, TimeZoneInfo.Local);
            });
        }
    }

`

`

 public class FooTimeStampDataLoader : BatchDataLoader<Guid, DateTime>
    {
        private readonly IDbContextFactory<ApplicationDbContext> _dbContextFactory;

        public FooTimeStampDataLoader (
            IBatchScheduler batchScheduler, 
            DataLoaderOptions options,
            IDbContextFactory<ApplicationDbContext> dbContextFactory)
            : base(batchScheduler, options)
        {
            _dbContextFactory = dbContextFactory ?? 
                throw new ArgumentNullException(nameof(dbContextFactory));
        }

        protected override async Task<IReadOnlyDictionary<Guid, DateTime>> LoadBatchAsync(
            IReadOnlyList<Guid> keys, 
            CancellationToken cancellationToken)
        {
            await using ApplicationDbContext dbContext = 
                _dbContextFactory.CreateDbContext();
   

            var p = await dbContext.Foo
            .Where(s => keys.Contains(s.ID))
            .Select(x => new {
                ID = x.ID,
                TimeStamp = EF.Property<DateTime>(x, "PeriodStart")})
                .
                ToDictionaryAsync(x => x.ID, x => x.TimeStamp)
                ;
            return p;
        }
    }

`

After finding out this works by executing two separate querys to the db in case timeStamp is asked I wanted to add filtering and order by timeStamp as well. For sorting I did by going into the hotchocolate 12.15.2 source code and copying QueryableDefaultSortFieldHandler and modified it like so:

`

public class TimeStampSortHandler
    : SortFieldHandler<QueryableSortContext, QueryableSortOperation>
{
    public override bool CanHandle(
        ITypeCompletionContext context,
        ISortInputTypeDefinition typeDefinition,
        ISortFieldDefinition fieldDefinition) =>
        fieldDefinition.Member is not null;

    public override bool TryHandleEnter(
        QueryableSortContext context,
        ISortField field,
        ObjectFieldNode node,
        [NotNullWhen(true)] out ISyntaxVisitorAction? action)
    {
        if (node.Value.IsNull())
        {
            context.ReportError(
                ErrorHelper.CreateNonNullError(field, node.Value, context));

            action = SyntaxVisitor.Skip;
            return true;
        }
        IExtendedType runtime = new ExtendedType(typeof(DateTime));
        // if (field.RuntimeType is null)
        // {
        //     Console.WriteLine("isnull");
        //     action = null;
        //     return false;
        // }

        if (!(context.GetInstance() is QueryableFieldSelector lastFieldSelector))
        {
            throw ThrowHelper.Sorting_InvalidState_ParentIsNoFieldSelector(field);
        }

        Expression lastSelector = lastFieldSelector.Selector;
        Expression<Func<Foo, DateTime>> expression = _ => EF.Property<DateTime>(_, "PeriodStart");
        Expression nextSelector = Expression.Invoke(expression, lastSelector);
        // Expression nextSelector = field.Member switch
        // {
        //     PropertyInfo i => Expression.Property(lastSelector, i),
        //     MethodInfo i => Expression.Call(lastSelector, i),
        //     { } i => throw ThrowHelper.QueryableSorting_MemberInvalid(i, field),
        //     null => throw ThrowHelper.QueryableSorting_NoMemberDeclared(field),
        // };

        if (context.InMemory)
        {
            nextSelector = SortExpressionBuilder.IfNullThenDefault(
                lastSelector,
                nextSelector,
                Expression.Default(runtime.Source));
        }

        context.PushInstance(lastFieldSelector.WithSelector(nextSelector));
        context.RuntimeTypes.Push(runtime);
        action = SyntaxVisitor.Continue;
        return true;
    }

    public override bool TryHandleLeave(
        QueryableSortContext context,
        ISortField field,
        ObjectFieldNode node,
        [NotNullWhen(true)] out ISyntaxVisitorAction? action)
    {
        // if (field.RuntimeType is null)
        // {
        //     action = null;
        //     return false;
        // }

        // Deque last
        context.PopInstance();
        context.RuntimeTypes.Pop();

        action = SyntaxVisitor.Continue;
        return true;
    }

`

and added a sortInputType that uses this handler:

`

    public class FooSortType : SortInputType<Foo>
{
    protected override void Configure(ISortInputTypeDescriptor<Foo> descriptor)
    {
        
        descriptor.BindFieldsImplicitly();
        descriptor.Field("timeStamp").Type<DefaultSortEnumType>()
         .Extend()
            .OnBeforeCreate(_ => _.Handler = new TimeStampSortHandler());
    }
}


}

`

this works for sorting by more than one field including timeStamp.
Would you do this any differently, is there another way to implement temporalTables with hotchocolate?

The solution you'd like

Would like to not use the dataLoader and let it select foo.PeriodStart as TimeStamp directly on the extended type.
As well as integration for sorting and filtering.
Are you planning for adding support for sql server temporal tables on 13.0?
Thank you.

Product

Hot Chocolate