Support for JOINs inside a temporal query

[orty]

Hi,

First things first, thanks a lot for the ton of effort put in this library. The support for the EF Core temporal table query feature is very appreciated and produces great results with many of my use cases.

I have a request though, since EF Core allows building expressions which will translate themselves to LEFT JOIN clauses on the SQL side, when we access navigation properties in a WHERE clause, for example. Would it be possible to also handle the joined tables, and produce the equivalent FOR SYSTEM_TIME clause for these tables as well ? So far, only the 'main' selected table will have this clause written in the resulting SQL query.

If it is not easily implementable/releasable on your side, would you mind showing me briefly how I can achieve such a result using an interceptor ?

I know that EF Core does not allow to produce anything else than AS OF temporal clauses when the query involves JOINs in order to maintain some sort of consistency in the resulting data. In my specific case, I need to issue the temporal queries on a range of dates (ALL, or at least FROM...TO). I assume that you will probably not go and implement a feature that is not even supported by EF Core at the moment (and will probably never be) but if we (I would be glad to contribute to this repo, given some guidelines first ^^) go down this road, I will probably need an interceptor anyway, or some inheritance mechanism to implement my own support in my specific business case.

Thanks

[sdanyliv]

Well, it needs knowledge that joined tables are temporal. Not supported yet by linq2db. It can be complicated task right now but makes sense.

Not sure that just contribution can help, it may need design decisions.

[orty]

Thank you for your answer. FYI, in my case, I had to rely on the .ReplaceService<IModelFinalizedConvention, CustomSqlServerRuntimeModelConvention>() extension method on DbContextOptionsBuilder to add the following logic on model finalization

/// <inheritdoc />
protected override void ProcessEntityTypeAnnotations(
    IDictionary<string, object> annotations,
    IEntityType entityType,
    RuntimeEntityType runtimeEntityType,
    bool runtime)
{
    annotations.TryGetValue(SqlServerAnnotationNames.TemporalPeriodEndColumnName, out var temporalPeriodEndColumnNameAnnotation);
    annotations.TryGetValue(SqlServerAnnotationNames.TemporalPeriodEndPropertyName, out var temporalPeriodEndPropertyNameAnnotation);
    annotations.TryGetValue(SqlServerAnnotationNames.TemporalPeriodStartColumnName, out var temporalPeriodStartColumnNameAnnotation);
    annotations.TryGetValue(SqlServerAnnotationNames.TemporalPeriodStartPropertyName, out var temporalPeriodStartPropertyNameAnnotation);

    base.ProcessEntityTypeAnnotations(
        annotations,
        entityType,
        runtimeEntityType,
        runtime);

    if (!runtime)
    {
        annotations.TryAdd(SqlServerAnnotationNames.TemporalPeriodEndColumnName, temporalPeriodEndColumnNameAnnotation);
        annotations.TryAdd(SqlServerAnnotationNames.TemporalPeriodEndPropertyName, temporalPeriodEndPropertyNameAnnotation);
        annotations.TryAdd(SqlServerAnnotationNames.TemporalPeriodStartColumnName, temporalPeriodStartColumnNameAnnotation);
        annotations.TryAdd(SqlServerAnnotationNames.TemporalPeriodStartPropertyName, temporalPeriodStartPropertyNameAnnotation);
    }
}

This is because, by design, EF Core removes the annotations which specifies the name of the "StartDate" and "EndDate" temporal boundaries column, which can be customized with whatever name when creating the DbContext, and I needed to get the name of the columns dynamically by entity type. These annotations are removed when the app runs for good, and kept only in "runtime" mode (that is, when generating/applying migrations...) I think that the annotation for the IsTemporal flag is kept though, in runtime or not.

[orty]

I did some digging, and I think I found a place where the information about the temporal state of the table could be retrieved from the DbContext, along with the forging of the JOIN clause. Here, in the AssociationHelper class : https://github.com/linq2db/linq2db/blob/3dd90c94977111cb7f0f6995902221249d3944ae/Source/LinqToDB/Linq/Builder/AssociationHelper.cs#L303-L309

Obviously this helper belongs to the base Linq2Db library and this behavior might need a specific implementation for EF Core since it is the very DbContext.Model from EF Core which will describe the kind of temporal table the joined one is.

What do you think about this way of implementing it ?