How to enable append only ledger tables with EF core 6 code first

user_1360119 0
Viewed 119

I am using EF code first to create my migrations for an azure sql DB. I would like to start using "update" and "append only" ledger tables. I know i can set the whole DB to be update ledger, so that when I create new tables in the usual way, they will be automatically update ledger tables with a history table. But I would like to instead have the option of choosing which tables are update and which as append only. I can't find any recourses on how I would go about this. One of the tables I want to be append only would in fact be my MigrationsHistory table. Is there a way to do this with EF core?

--- Update [clarification] ---

I might now have been clear enough. Above.

What i am looking for is on how i would archive the required result using entity framework code first.

In other words using sql i could do something like this:

CREATE SCHEMA [AccessControl];
GO
CREATE TABLE [AccessControl].[KeyCardEvents]
(
  [ID] INT NOT NULL,
  [Timestamp] Datetime2 NOT NULL
)
WITH (LEDGER = ON (APPEND_ONLY = ON));

To get a table that would be append only. But if i want to create the model first like this:

public class AccessControl
{
    public Int Id { get; set; }
    public DateTime Timestamp{ get; set; }
}

Is there perhaps an attribute i could add to the class so that EF knows to make it an append only table? Or is there another way that can be done?

Also, please note that i also am curious how to make the __EFMigationsHistory table be an append only table. This table gets created the first time a migration is run on a database.

entity-framework-core azure-sql-database c#
2 Answers

There seems to be no support for Azure Sql Database Ledger tables in EF Core Code First at the moment.

Even looking at the current in-development code for EF 7 (the next EF Core version at the time of writing) SqlServerMigrationsSqlGenerator.cs class, the Generate method overload with the following signature

protected override void Generate(
        CreateTableOperation operation,
        IModel? model,
        MigrationCommandListBuilder builder,
        bool terminate = true)

is only able to fill in the "table options" (that will be provided in the WITH clause as a part of the generated CREATE TABLE statement) with:

  • The SYSTEM_VERSIONING = ON option alone
  • And/or the MEMORY_OPTIMIZED = ON option

The first corresponds to, eg:

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

While the second one corresponds to, eg:

modelBuilder.Entity<Blog>().IsMemoryOptimized();

Unfortunately there is currently no mention of Ledger tables support neither in EF Core 7.0 Plan, nor in EF Core Issues in GitHub..

user_8227350

Create and use append-only ledger table

We can create an append-only ledger table, by providing the LEDGER = ON and APPEND_ONLY = ON options.

Syntax for append-only ledger table:

CREATE TABLE [schemaName].[tableName] 
( 
 -- Required fields
) 
WITH (LEDGER = ON (APPEND_ONLY = ON));

For the above case, we can add a new value to our append-only ledger table.

If we try to update the above table by modifying the any column's value, we will get an error message by saying that updates are not allowed for the append-only ledger table 'tableName'.

Create an updatable ledger table

We can create an update ledger table, by providing the SYSTEM_VERSIONING = ON LEDGER = ON and options.

Syntax for update ledger table:

CREATE TABLE [schemaName].[tableName] 
( 
 -- Required fields
) 
WITH ( SYSTEM_VERSIONING = ON (HISTORY_TABLE = [schemaName].[tableNameHistory]), LEDGER = ON );

Here, if you already enabled the LEDGER on database then it's optional on table level and check these Permissions. The history and ledger view will be created by default when we created the update ledger table. Here, we can insert values and update the values. To check the complete history of changes goes through the view instead of history table.

user_189357750
Related