Хранение истории изменений

Начиная с версии 6.0 Entity Framework Core позволяет воспользоваться возможностями SQL Server по хранению истории изменений в таблице. То есть SQL Server может автоматически отслеживать все операции с данными в таблице благодаря созданию параллельной таблицы, которая хранит историю изменений. Благодаря этому мы можем извлечить те ранее измененные данные. Это может быть полезно для изучения истории изменений или восстановления данных в случае каких-то поломок или нежелательного изменения.

Допустим, у нас будет следующая сущность:

public class User
{
    public int Id { get; set; }
    public string? Name { get; set; }
}

Определим следующий контекст данных:

using Microsoft.EntityFrameworkCore;

public class ApplicationContext : DbContext
{
    public DbSet<User> Users { get; set; } = null!;

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=helloappdb;Trusted_Connection=True;");
    }
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder
            .Entity<User>()
            .ToTable("Users", t => t.IsTemporal());
    }
}

Для установки таблицы как временной в методе ToTable() в качестве второго параметра - делегата, который настраивает таблицу, передается выражение t => t.IsTemporal(). Метод IsTemporal() указывает, что для таблицы будет храниться история изменений.

В итоге на уровне MS SQL Server будет создана следующая таблица:

CREATE TABLE [dbo].[Users] (
    [Id]          INT                                                IDENTITY (1, 1) NOT NULL,
    [Name]        NVARCHAR (MAX)                                     NULL,
    [PeriodEnd]   DATETIME2 (7) GENERATED ALWAYS AS ROW END HIDDEN   NOT NULL,
    [PeriodStart] DATETIME2 (7) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    PERIOD FOR SYSTEM_TIME ([PeriodStart], [PeriodEnd])
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE=[dbo].[UsersHistory], DATA_CONSISTENCY_CHECK=ON));

Здесь мы можем заметить, что SQL Server создает два дополнительных столбца PeriodEnd и PeriodStart, которые имеют тип datetime2. Эти столбцы устанавливают временной интервал, в течение которого существует строка.

Кроме того, здесь генерируется ассоциированная таблица "UsersHistory" для хранения истории изменений.

С подобной таблицей мы можем работать как с обычной таблицей. Например, добавление и получение данных:

using (ApplicationContext db = new ApplicationContext())
{
	// пересоздаем бд
    db.Database.EnsureDeleted();
    db.Database.EnsureCreated();

    User tom = new User { Name = "Tom" };

    db.Users.Add(tom);
    db.SaveChanges();
}
using (ApplicationContext db = new ApplicationContext())
{
    // получаем объекты из бд
    var users = db.Users.ToList();
    foreach (User u in users)
    {
        Console.WriteLine(u.Name);
    }
}

Теперь посмотрим, что произойдет, если мы изменим данные:

using (ApplicationContext db = new ApplicationContext())
{
    // изменение
    var user = db.Users.FirstOrDefault(u => u.Name == "Tom");
    if (user != null)
    {
        user.Name = "Bob";
        // сохраняем изменения
        db.SaveChanges();

        // еще раз изменяем
        user.Name = "Sam";
        // сохраняем изменения
        db.SaveChanges();

        Console.WriteLine(user.Name);
    }
}

Итак, в примере ранее был создан объект User с именем "Tom", здесь же мы последовательно меняем его имя сначала на "Bob", а потом на "Sam".

Теперь откроем таблицу, которая хранит историю изменений:

В таблицы истории изменений можно увидеть, что она хранит промежуточные состояния измененного объекта User.

Получение данных из таблицы истории

Рассмотрим, как мы можем получить данные из таблицы истории. Прежде всего мы можем получить из таблицы Users начальную и конечную дату интервала, в течение которого существует строка в таблице:

using (ApplicationContext db = new ApplicationContext())
{
    // изменение
    var user = db.Users.FirstOrDefault(u => u.Name == "Sam");
    if (user != null)
    {
        var userEntry = db.Entry(user);
        var createdAt = userEntry.Property<DateTime>("PeriodStart").CurrentValue;
        var deletedAt = userEntry.Property<DateTime>("PeriodEnd").CurrentValue;
        Console.WriteLine($"пользователь {user.Name}");
        Console.WriteLine($"Дата создания: {createdAt}"); 
        Console.WriteLine($"Дата удаления {deletedAt}");
    }
}

Хотя в сущности User для столбцов PeriodEnd и PeriodStart нет соответствующих свойств, тем не менее мы можем сначала получить отслеживаемый объект через

var userEntry = db.Entry(user);

А затем через метод Property() получить значение столбцов в виде значения DateTime:

var createdAt = userEntry.Property<DateTime>("PeriodStart").CurrentValue;
var deletedAt = userEntry.Property<DateTime>("PeriodEnd").CurrentValue;

Стоит отметить, что поскольку строка еще существует, то столбец "PeriodEnd" будет содержать максимально возможное значение.

Кроме того, EF Core поддерживает ряд методов, которые позволяют получать данные истории:

• TemporalAsOf: возвращает строки, в которых хранится состояние объекта на определенную дату. Для каждого объекта возвращается одна строка.

• TemporalAll: возвращает все строки.

• TemporalFromTo: возвращает все строки, в которых сохранено состояние объекта между двумя датами.

• TemporalBetween: аналогично предыдущему варианту за тем исключением, что также возвращается строки с состоянием объекта непоседствнно в момент конечной даты.

• TemporalContainedIn: возвращает все строки с состоянием объекта, который существовал непосредственно на момент начальной и конечной дат.

Чтобы задействовать эти методы, необходимо подключить пространство имен using Microsoft.EntityFrameworkCore;

Например, получим всю историю по ранее измененому пользователю:

using Microsoft.EntityFrameworkCore;

using (ApplicationContext db = new ApplicationContext())
{
    var history = db.Users.TemporalAll()
                .Where(u => u.Id == 1)
				.OrderBy(e => EF.Property<DateTime>(e, "PeriodStart"))
                .Select(u => new 
                {
                    User = u,
                    Start = EF.Property<DateTime>(u, "PeriodStart"),
                    End = EF.Property<DateTime>(u, "PeriodEnd")
                }).ToList();
    Console.WriteLine("История по пользователю с id=1");
    foreach (var item in history)
    {
        Console.WriteLine($"{item.User.Name} from {item.Start} to {item.End}");
    }
}

Консольный вывод в моем случае:

История по пользователю с id=1
Tom from 24.11.2021 12:48:48 to 24.11.2021 13:13:57
Bob from 24.11.2021 13:13:57 to 24.11.2021 13:13:59
Sam from 24.11.2021 13:13:59 to 31.12.9999 23:59:59

Для методов TemporalFromTo, TemporalBetween и TemporalContainedIn передаются начальная и конечная даты

var history = db.Users
        .TemporalBetween(
                    new DateTime(2021, 11, 24, 12,48, 48), 
                    new DateTime(2021, 11, 24, 14, 35, 15))
        .Where(u => u.Id == 1)
        .OrderBy(e => EF.Property<DateTime>(e, "PeriodStart"))
        .Select(u => new 
        {
			User = u,
            Start = EF.Property<DateTime>(u, "PeriodStart"),
            End = EF.Property<DateTime>(u, "PeriodEnd")
        }).ToList();

Для метода TemporalAsOf() передается одна дата:

var history = db.Users
        .TemporalAsOf(new DateTime(2021, 11, 24, 12,48, 48))
        .Where(u => u.Id == 1)
		.ToList();

Восстановление данных

Получив состояние с помощью одного из выше описанных методов, мы можем сохранить данное состояние в бд:

// удаляем пользователя
using (ApplicationContext db = new ApplicationContext())
{
    var sam = db.Users.Find(1);
    if (sam != null)
    {
        db.Users.Remove(sam);
        db.SaveChanges();
        Console.WriteLine("Пользователь удален");
    }
}

// восстанавливаем пользователя
using (ApplicationContext db = new ApplicationContext())
{
    // получаем состояние удаленного пользователя
    var user = db.Users.TemporalAsOf(new DateTime(2021, 11, 24, 13, 0, 0))
                .FirstOrDefault(u => u.Id == 1);

    if (user != null)
    {
		// запускаем транзакцию  
        using (var transaction = db.Database.BeginTransaction())
        {
			// отключаем автоматогенерацию идентификатор по добавлению
            db.Database.ExecuteSqlRaw("SET IDENTITY_INSERT [dbo].[Users] ON");
            db.Users.Add(user);
            db.SaveChanges();
			// включаем автогенерацию идентификаторов обратно
            db.Database.ExecuteSqlRaw("SET IDENTITY_INSERT [dbo].[Users] OFF");
            db.Database.CommitTransaction();
			Console.WriteLine("Пользователь восстановлен");
        }
    }
}
// проверяем наличие пользователя
using (ApplicationContext db = new ApplicationContext())
{
    var tom = db.Users.Find(1);
    if (tom != null)
    {
        Console.WriteLine($"Пользователь {tom.Name}");
    }
}

Данный код делится на ти части. Вначале удаляем пользователя с id = 1:

 db.Users.Remove(sam);
db.SaveChanges();

После этого объект User удален из таблицы Users. Однако в таблице истории UseHistoryTable данные о нем остались.

Во второй части кода восстанавливаем пользователя:

var user = db.Users.TemporalAsOf(new DateTime(2021, 11, 24, 13, 0, 0))
                .FirstOrDefault(u => u.Id == 1);

То есть в данном случае я извлекаю из таблицы с историей состояние объекта с id =1 на момент 24/11/2021 13:00:00. В этот момент времени у удаленного пользователя свойство Name было равно.

Полученный объект также будет представлять класс User и поэтому мы его можем обратно добавить в таблицу:

db.Users.Add(user);
db.SaveChanges();

Однако в данном случае мы можем столкнуться с проблемой: для таблицы автоматически генерируется значение для свойства-иденификатора Id. А востановленный и заново добавляемый объект уже имет Id. В этом случае запускаем транзакцию, в которой выполняем три действия:

using (var transaction = db.Database.BeginTransaction())
{
	db.Database.ExecuteSqlRaw("SET IDENTITY_INSERT [dbo].[Users] ON");
	db.Users.Add(user);
	db.SaveChanges();
	db.Database.ExecuteSqlRaw("SET IDENTITY_INSERT [dbo].[Users] OFF");
	db.Database.CommitTransaction();
}

Сначала отключаем автогенерацию иденификаторов для таблицы Users:

db.Database.ExecuteSqlRaw("SET IDENTITY_INSERT [dbo].[Users] ON");

После этого мы сможем добавить объект с уже установленным Id:

db.Users.Add(user);

После добавления объекта заново включаем автогенерацию идентификаторов

db.Database.ExecuteSqlRaw("SET IDENTITY_INSERT [dbo].[Users] OFF");

Но естественно, если используются дугую способы генерации id, то подобные махинации с отключением/включением автогенерации иденификаторов могут не понадобиться.

В третьей части кода проверяем наличие восстановленного объекта:

 var tom = db.Users.Find(1);

Настройка таблицы истории

Entity Framework также позволяет настроить таблицу, которая будет хранить историю изменений:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
	modelBuilder
        .Entity<User>()
        .ToTable("Users", t => t.IsTemporal(
            h =>
            {
                h.HasPeriodStart("CreatedAt");
                h.HasPeriodEnd("DeletedAt");
                h.UseHistoryTable("PeopleDataHistory");
        }));
    }

В данном случае таблица для хранения истории будет называться PeopleDataHistory, а столбцы, которые задают временной интервал, - "CreatedAt" и "DeletedAt".