Tabel temporal untuk SQL Server/Azure SQL

Tabel temporal SQL Server secara otomatis melacak semua data yang pernah disimpan dalam tabel, bahkan setelah data tersebut diperbarui atau dihapus. Ini dicapai dengan membuat "tabel riwayat" paralel tempat data historis bertanda waktu disimpan setiap kali perubahan dilakukan pada tabel utama. Ini memungkinkan data historis untuk dikueri, seperti untuk audit, atau dipulihkan, seperti untuk pemulihan setelah mutasi atau penghapusan yang tidak disengaja.

EF Core mendukung:

• Pembuatan tabel temporal menggunakan Migrasi Inti EF
• Transformasi tabel yang ada menjadi tabel temporal, sekali lagi menggunakan Migrasi
• Mengkueri data historis
• Memulihkan data dari beberapa titik di masa lalu

Mengonfigurasi tabel temporal

Pembuat model dapat digunakan untuk mengonfigurasi tabel sebagai temporal. Contohnya:

modelBuilder
    .Entity<Employee>()
    .ToTable("Employees", b => b.IsTemporal());

Saat menggunakan EF Core untuk membuat database, tabel baru dikonfigurasi sebagai tabel temporal dengan default SQL Server untuk tanda waktu dan tabel riwayat. Misalnya, pertimbangkan Employee jenis entitas:

public class Employee
{
    public Guid EmployeeId { get; set; }
    public string Name { get; set; }
    public string Position { get; set; }
    public string Department { get; set; }
    public string Address { get; set; }
    public decimal AnnualSalary { get; set; }
}

Tabel temporal yang dibuat terlihat seperti ini:

DECLARE @historyTableSchema sysname = SCHEMA_NAME()
EXEC(N'CREATE TABLE [Employees] (
    [EmployeeId] uniqueidentifier NOT NULL,
    [Name] nvarchar(100) NULL,
    [Position] nvarchar(100) NULL,
    [Department] nvarchar(100) NULL,
    [Address] nvarchar(1024) NULL,
    [AnnualSalary] decimal(10,2) NOT NULL,
    [PeriodEnd] datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
    [PeriodStart] datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
    CONSTRAINT [PK_Employees] PRIMARY KEY ([EmployeeId]),
    PERIOD FOR SYSTEM_TIME([PeriodStart], [PeriodEnd])
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [' + @historyTableSchema + N'].[EmployeeHistory]))');

Perhatikan bahwa SQL Server membuat dua kolom tersembunyi datetime2 yang disebut PeriodEnd dan PeriodStart. "Kolom periode" ini mewakili rentang waktu ketika data dalam baris tersebut ada. Secara default, kolom ini dipetakan ke properti bayangan dalam model EF Core, memungkinkannya digunakan dalam kueri seperti yang ditunjukkan nanti. Dimulai dengan EF Core 11, kolom periode juga dapat dipetakan ke properti CLR pada tipe entitas Anda.

Perhatikan juga bahwa tabel riwayat terkait yang disebut EmployeeHistory dibuat secara otomatis. Nama kolom periode dan tabel riwayat dapat diubah dengan konfigurasi tambahan pada pembangun model. Contohnya:

modelBuilder
    .Entity<Employee>()
    .ToTable(
        "Employees",
        b => b.IsTemporal(
            b =>
            {
                b.HasPeriodStart("ValidFrom");
                b.HasPeriodEnd("ValidTo");
                b.UseHistoryTable("EmployeeHistoricalData");
            }));

Ini tercermin dalam tabel yang dibuat oleh SQL Server:

DECLARE @historyTableSchema sysname = SCHEMA_NAME()
EXEC(N'CREATE TABLE [Employees] (
    [EmployeeId] uniqueidentifier NOT NULL,
    [Name] nvarchar(100) NULL,
    [Position] nvarchar(100) NULL,
    [Department] nvarchar(100) NULL,
    [Address] nvarchar(1024) NULL,
    [AnnualSalary] decimal(10,2) NOT NULL,
    [ValidFrom] datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
    [ValidTo] datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
    CONSTRAINT [PK_Employees] PRIMARY KEY ([EmployeeId]),
    PERIOD FOR SYSTEM_TIME([ValidFrom], [ValidTo])
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [' + @historyTableSchema + N'].[EmployeeHistoricalData]))');

Menggunakan tabel temporal

Sebagian besar waktu, tabel temporal digunakan sama seperti tabel lainnya. Artinya, kolom periode dan data historis ditangani secara transparan oleh SQL Server sehingga aplikasi dapat mengabaikannya. Misalnya, entitas baru dapat disimpan ke database dengan cara normal:

context.AddRange(
    new Employee
    {
        Name = "Pinky Pie",
        Address = "Sugarcube Corner, Ponyville, Equestria",
        Department = "DevDiv",
        Position = "Party Organizer",
        AnnualSalary = 100.0m
    },
    new Employee
    {
        Name = "Rainbow Dash",
        Address = "Cloudominium, Ponyville, Equestria",
        Department = "DevDiv",
        Position = "Ponyville weather patrol",
        AnnualSalary = 900.0m
    },
    new Employee
    {
        Name = "Fluttershy",
        Address = "Everfree Forest, Equestria",
        Department = "DevDiv",
        Position = "Animal caretaker",
        AnnualSalary = 30.0m
    });

await context.SaveChangesAsync();

Data ini kemudian dapat dikueri, diperbarui, dan dihapus dengan cara normal. Contohnya:

var employee = await context.Employees.SingleAsync(e => e.Name == "Rainbow Dash");
context.Remove(employee);
await context.SaveChangesAsync();

Selain itu, setelah kueri pelacakan normal, nilai dari kolom periode data saat ini dapat diakses dari entitas yang dilacak. Jika kolom periode dipetakan ke properti CLR, Anda dapat mengaksesnya langsung di entitas; jika tidak, gunakan EF.Property untuk mengaksesnya sebagai properti bayangan. Contohnya:

var employees = await context.Employees.ToListAsync();
foreach (var employee in employees)
{
    var employeeEntry = context.Entry(employee);
    var validFrom = employeeEntry.Property<DateTime>("ValidFrom").CurrentValue;
    var validTo = employeeEntry.Property<DateTime>("ValidTo").CurrentValue;

    Console.WriteLine($"  Employee {employee.Name} valid from {validFrom} to {validTo}");
}

Cetakan ini:

Starting data:
  Employee Pinky Pie valid from 8/26/2021 4:38:58 PM to 12/31/9999 11:59:59 PM
  Employee Rainbow Dash valid from 8/26/2021 4:38:58 PM to 12/31/9999 11:59:59 PM
  Employee Fluttershy valid from 8/26/2021 4:38:58 PM to 12/31/9999 11:59:59 PM

Perhatikan bahwa ValidTo kolom (secara default disebut PeriodEnd) berisi datetime2 nilai maks. Ini selalu terjadi untuk baris saat ini dalam tabel. Kolom ValidFrom (secara default dinamakan PeriodStart) berisi waktu UTC ketika baris disisipkan.

Mengkueri data historis

EF Core mendukung kueri yang menyertakan data historis melalui beberapa operator kueri khusus:

• TemporalAsOf: Mengembalikan baris yang aktif (saat ini) pada waktu UTC yang diberikan. Ini adalah satu baris dari tabel atau tabel riwayat saat ini untuk kunci primer tertentu.
• TemporalAll: Mengembalikan semua baris dalam data historis. Ini biasanya banyak baris dari tabel riwayat dan/atau tabel saat ini untuk kunci primer tertentu.
• TemporalFromTo: Mengembalikan semua baris yang aktif di antara dua waktu UTC tertentu. Ini mungkin banyak baris dari tabel riwayat dan/atau tabel saat ini untuk kunci primer tertentu.
• TemporalBetween: Sama seperti TemporalFromTo, kecuali bahwa baris disertakan yang menjadi aktif di batas atas.
• TemporalContainedIn: Mengembalikan semua baris yang mulai aktif dan berakhir aktif di antara dua waktu UTC yang diberikan. Ini mungkin banyak baris dari tabel riwayat dan/atau tabel saat ini untuk kunci primer tertentu.

Misalnya, setelah membuat beberapa pembaruan dan menghapus data kami, kita dapat menjalankan kueri menggunakan TemporalAll untuk melihat data historis:

var history = await context
    .Employees
    .TemporalAll()
    .Where(e => e.Name == "Rainbow Dash")
    .OrderBy(e => EF.Property<DateTime>(e, "ValidFrom"))
    .Select(
        e => new
        {
            Employee = e,
            ValidFrom = EF.Property<DateTime>(e, "ValidFrom"),
            ValidTo = EF.Property<DateTime>(e, "ValidTo")
        })
    .ToListAsync();

foreach (var pointInTime in history)
{
    Console.WriteLine(
        $"  Employee {pointInTime.Employee.Name} was '{pointInTime.Employee.Position}' from {pointInTime.ValidFrom} to {pointInTime.ValidTo}");
}

Perhatikan bagaimana EF. Metode properti dapat digunakan untuk mengakses nilai dari kolom periode. Ini digunakan dalam OrderBy klausul untuk mengurutkan data, lalu dalam proyeksi untuk menyertakan nilai-nilai ini dalam data yang dikembalikan. Jika kolom periode dipetakan ke properti CLR, Anda bisa mereferensikannya langsung dalam kueri alih-alih menggunakan EF.Property.

Kueri ini mengembalikan data berikut:

Historical data for Rainbow Dash:
  Employee Rainbow Dash was 'Ponyville weather patrol' from 8/26/2021 4:38:58 PM to 8/26/2021 4:40:29 PM
  Employee Rainbow Dash was 'Wonderbolt Trainee' from 8/26/2021 4:40:29 PM to 8/26/2021 4:41:59 PM
  Employee Rainbow Dash was 'Wonderbolt Reservist' from 8/26/2021 4:41:59 PM to 8/26/2021 4:43:29 PM
  Employee Rainbow Dash was 'Wonderbolt' from 8/26/2021 4:43:29 PM to 8/26/2021 4:44:59 PM

Perhatikan bahwa baris terakhir yang dikembalikan tidak aktif lagi pada 26/8/2021 16:44:59. Ini karena baris untuk Rainbow Dash dihapus dari tabel utama pada saat itu. Kita akan melihat nanti bagaimana data ini dapat dipulihkan.

Kueri serupa dapat ditulis menggunakan TemporalFromTo, , TemporalBetweenatau TemporalContainedIn. Contohnya:

var history = await context
    .Employees
    .TemporalBetween(timeStamp2, timeStamp3)
    .Where(e => e.Name == "Rainbow Dash")
    .OrderBy(e => EF.Property<DateTime>(e, "ValidFrom"))
    .Select(
        e => new
        {
            Employee = e,
            ValidFrom = EF.Property<DateTime>(e, "ValidFrom"),
            ValidTo = EF.Property<DateTime>(e, "ValidTo")
        })
    .ToListAsync();

Kueri ini mengembalikan baris berikut:

Historical data for Rainbow Dash between 8/26/2021 4:41:14 PM and 8/26/2021 4:42:44 PM:
  Employee Rainbow Dash was 'Wonderbolt Trainee' from 8/26/2021 4:40:29 PM to 8/26/2021 4:41:59 PM
  Employee Rainbow Dash was 'Wonderbolt Reservist' from 8/26/2021 4:41:59 PM to 8/26/2021 4:43:29 PM

Memulihkan data historis

Seperti disebutkan di atas, Rainbow Dash dihapus dari Employees tabel. Ini jelas merupakan kesalahan, jadi mari kita kembali ke titik waktu dan memulihkan baris yang hilang dari waktu itu.

var employee = await context
    .Employees
    .TemporalAsOf(timeStamp2)
    .SingleAsync(e => e.Name == "Rainbow Dash");

context.Add(employee);
await context.SaveChangesAsync();

Kueri ini mengembalikan satu baris untuk Rainbow Dash seperti pada waktu UTC yang diberikan. Semua kueri yang menggunakan operator temporal tidak dilacak secara default, sehingga entitas yang dikembalikan di sini tidak dilacak. Ini masuk akal, karena saat ini tidak ada di tabel utama. Untuk memasukkan kembali entitas ke dalam tabel utama, kita cukup menandainya sebagai Added dan kemudian memanggil SaveChanges.

Setelah menyisipkan ulang baris Rainbow Dash, mengkueri data historis menunjukkan bahwa baris dipulihkan seperti pada waktu UTC yang diberikan:

Historical data for Rainbow Dash:
  Employee Rainbow Dash was 'Ponyville weather patrol' from 8/26/2021 4:38:58 PM to 8/26/2021 4:40:29 PM
  Employee Rainbow Dash was 'Wonderbolt Trainee' from 8/26/2021 4:40:29 PM to 8/26/2021 4:41:59 PM
  Employee Rainbow Dash was 'Wonderbolt Reservist' from 8/26/2021 4:41:59 PM to 8/26/2021 4:43:29 PM
  Employee Rainbow Dash was 'Wonderbolt' from 8/26/2021 4:43:29 PM to 8/26/2021 4:44:59 PM
  Employee Rainbow Dash was 'Wonderbolt Trainee' from 8/26/2021 4:44:59 PM to 12/31/9999 11:59:59 PM

Memetakan kolom periode ke properti CLR

Secara default, kolom periode dalam tabel temporal dipetakan ke properti shadow dalam model EF Core, yang berarti tidak perlu ada pada jenis entitas .NET Anda. Dimulai dengan EF Core 11, Anda dapat memetakan kolom periode ke properti CLR reguler pada jenis entitas Anda, yang memungkinkan Anda mengakses nilainya secara langsung.

Untuk melakukan ini, tambahkan DateTime properti untuk periode mulai dan berakhir ke jenis entitas Anda:

public class Employee
{
    public Guid EmployeeId { get; set; }
    public string Name { get; set; }
    public string Position { get; set; }
    public string Department { get; set; }
    public string Address { get; set; }
    public decimal AnnualSalary { get; set; }
    public DateTime PeriodStart { get; set; }
    public DateTime PeriodEnd { get; set; }
}

Kemudian konfigurasikan tabel temporal untuk menggunakan properti ini melalui ekspresi lambda:

modelBuilder
    .Entity<Employee>()
    .ToTable(
        "Employees",
        b => b.IsTemporal(
            b =>
            {
                b.HasPeriodStart(e => e.PeriodStart);
                b.HasPeriodEnd(e => e.PeriodEnd);
            }));

Saat kolom periode dipetakan ke properti CLR, Anda dapat mengakses nilainya langsung pada entitas alih-alih menggunakan EF.Property:

var history = context
    .Employees
    .TemporalAll()
    .Where(e => e.Name == "Rainbow Dash")
    .OrderBy(e => e.PeriodStart)
    .Select(
        e => new
        {
            Employee = e,
            e.PeriodStart,
            e.PeriodEnd
        })
    .ToList();