Ledger/temporal support

[redBitBucketHed]

Hi:

Does (or will?) Datasync work with temporal tables and/or SQL Ledger?

Thanx...!

[MysterDru]

@redBitBucketHed I'm currently using the data sync toolkit on the server with SQL server temporal tables. It doesn't work out of the box, but you can create get the data you need from a temporal table & its history to set all the necessary properties on the ITableData interface.

Here is a sample table & view that can be created in SQL to make this work. This is just an example. Our actual implementation was more complex, but this is the basics.

I ended up creating a view in SQL that maps to the necessary ITableData properties as getting the query project to work efficiently in Entity Framework Core was a PITA and resulted in less than ideal queries. I found that the ROW_NUMBER() partitioning was key to get this to work correctly. Once the view is created, just query that and map it to your response object for the repository.

CREATE TABLE [MyTemporalTable]
(
    Id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT(NEWID()),
    --
    -- Other Columns Added Here 
    --
    PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo]),
    [ValidFrom] DATETIME2 GENERATED ALWAYS AS ROW START DEFAULT SYSUTCDATETIME(),
    [ValidTo] DATETIME2 GENERATED ALWAYS AS ROW END DEFAULT CAST('9999-12-31 23:59:59.9999999' AS DATETIME2),
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [MyTemporalTableHistory]));
GO

-- For simplicy, we created a view to cleanly organize the temporal query and manage 
-- the partioning logic of the data 
CREATE VIEW [MyTemporalTableView]
AS
    WITH
        -- Define "constant" for max date time comparisons used in queries, so this isn't repeated in multiple places
        MaxDateTime
        AS
        (
            SELECT CAST('9999-12-31 23:59:59.9999999' AS DATETIME2) AS MaxDateTime
        ),

        -- Get the most recent records for the PrimaryKey (Id) from the temporal table
        -- This will return the active/current records in the main table, as the most recent deleted record from history
        -- The ROW_NUMBER() function is used to find record that represents the deletion relative to the Primary Key
        --   Depending on implementation, the rownumber partioning can be adjusted to include other columns, but always order by ValidTo DESC
        MostRecentDataRecords
        AS
        (
            SELECT
                *,
                ROW_NUMBER() OVER (
                PARTITION BY Id
                ORDER BY ValidTo DESC
            ) AS rn

            FROM MyTemporalTable FOR SYSTEM_TIME ALL
        ),
        DataSyncResult
        AS
        (
            SELECT
                -- I noticed some issues with casing in the toolkit when using GUIDs, so forcing to lowercase from the DB view helps
                LOWER(m.Id) AS [Id],

                -- If the ValidTo is the MaxDateTime then this record has NOT been deleted, so use ValidFrom as UpdatedAt
                -- Otherwise, use ValidTo as UpdatedAt
                IIF(m.ValidTo = [x].MaxDateTime, m.ValidFrom, m.ValidTo) AS [UpdatedAt],

                -- If the ValidTo is the MaxDateTime then this record has NOT been deleted, so set Deleted to 0
                IIF(m.ValidTo = [x].MaxDateTime, 0, 1) as [Deleted],

                -- Create a "calculated" binary version from the ValidFrom and ValidTo dates
                -- Every time the record changes, these timestamps change, so the binary version will also change
                -- This could also be a standard concurrency column in SQL, but this works well enough
                CONVERT(BINARY(9), m.ValidFrom) + CONVERT(BINARY(9), m.ValidTo) as [Version]

            FROM MostRecentDataRecords m
                JOIN MaxDateTime [x] ON 1 = 1
            WHERE m.rn = 1
        )

    SELECT *
    FROM DataSyncResult

public class MyTemporalTableView : ITableData
{
    public string Id { get; set; }

    public bool Deleted { get; set;}

    public DateTime UpdatedAt { get; set; }

    public byte[] Version { get; set;}
    // Other properties can be added here as needed
}

public class MyTemporalTableRepository : IRepository<MyTemporalTableView>
{
    private readonly DbContext _context;

    public MyTemporalTableRepository(DbContext context)
    {
        _context = context;
    }

    public ValueTask<IEnumerable<MyTemporalTableView>> AsQueryableAsync(CancellationToken cancellationToken = default)
    {
        return ValueTask.FromResult(_context.Set<MyTemporalTableView>().AsNotTracking());
    }

    public async Task<MyTemporalTableView> GetByIdAsync(string id)
    {
        if (string.IsNullOrEmpty(id) || !Guid.TryParse(id, out guid))
        {
            throw new HttpException((int) HttpStatusCode.BadRequest, "Invalid or missing ID");
        }

        var found = await _context.Set<MyTemporalTableView>().FindAsync(id.ToLower(), cancellationToken);

        return found ?? throw new HttpException((int)HttpStatusCode.NotFound);
    }

    // Additional repository methods implemented here
}

[redBitBucketHed]

👍 Thankyou, @MysterDru.