Add SQL Ledger support

[Tealons]

In our project we want to add the Ledger feature to several tables that have high integrity requirements. We are planning to use the built-in Ledger support of MSSQL: https://learn.microsoft.com/en-us/sql/relational-databases/security/ledger/ledger-overview?view=sql-server-ver16

The problem is that because this feature is missing from EF Core, we need to create our own custom queries for migrations and exclude these tables from the EF Core migrations.

Could you please consider adding support for the Ledger feature which will enabled to create normal EF Core migrations for our tables.

[ErikEJ]

Would database model first work for you?

[Tealons]

It's not what I was hoping for, but I will take anything to have a more integrated situation :)

[roji]

Note the similarity with temporal tables (docs).

In any case, at least the main part of this would purely be a migration feature (from the docs: "Ledger and the historical data are managed transparently, offering protection without any application changes.").

As with most such advanced migration features, it should be possible to use raw SQL in migrations to get the desired table definition (either by adding ALTER TABLE statements, or by replacing the CreateTable operation by a CREAT TABLE). While this isn't as nice as having EF model everything, it's unfeasible for EF to cover the DDL for every possible database feature.

[Tealons]

In this case the migration is what I'm looking for. I tried it with a raw SQL, but combining it with the versioning did not work (right away).

[roji]

combining it with the versioning did not work

Which versioning are you referring to? Note that EF wouldn't be able to do anything that you're not capable of doing yourself in raw SQL - that's why there's also relatively little value in adding support for such features in migrations.

[Tealons]

I added .ToTable("Cars", b => b.IsTemporal());. This generates a lot in the migrations. I tried to combine this with the ledger, but failed at it. But then again, I did not try very hard yet. Maybe removing IsTemporal is the solution, but I don't know if that will remove the temporal Linq query capabilities?

[roji]

@Tealons from these docs, it sounds like it should be possible for the same table to be both temporal and ledger-backed... But that's something you'll need to figure out - I suggest looking for help in SQL Server forums (at this point it's no longer an EF-related question).

[roji]

Putting this in the backlog to gather user feedback.

[Tealons]

@roji: Indeed this is possible. I was just looking for a way via EF Core to create migrations for both temporal and ledger enabled tables. Now I use the b => b.IsTemporal() method were EF creates al the necessary migrations and makes temporal queries possible. I can also do this via raw sql, but I'm not sure how this would work in combination with the temporal option I now have enabled. But I will do some research next week and when I have something that works, I will also post it here.

[the-programmer]

As suggested in here #33226 (comment). For me a "code first" model would be preferred.
However since I need to implement this for a project, instructions for creating a database first model are also welcome.

[ErikEJ]

@the-programmer Is anything special needed for the database first scenario? Have you tried reverse engineering a ledger table?

[the-programmer]

The only thing special is that it has to be combined with a code first model. So far I haven't tried anything, but reverse engineering is one of the topics I have to look into.
Also I would prefer a way to add the ledger table in a migration. However a manual SQL command in there is fine for me.

[the-programmer]

Ok, So I added the table manually

CREATE TABLE [Measurements]
   (
	  Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
	  [ImportantFloat] FLOAT NOT NULL,
	  [ImportantNumber] BIGINT NOT NULL, 
          [Timestamp] DatetimeOffset NOT NULL
   )
   WITH (LEDGER = ON (APPEND_ONLY = ON));

And the reverse engineering also worked (using EF Core Power Tools).
With this I now have the following
Model

public class Measurement
{
    public int Id { get; set; }
    public double ImportantFloat { get; set; }
    public long ImportantNumber { get; set; }
    public DateTimeOffset Timestamp { get; set; }
    public long LedgerStartTransactionId { get; set; }
    public long LedgerStartSequenceNumber { get; set; }
}

DbContext

public virtual DbSet<Measurement> Measurements { get; set; }

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    //....
    modelBuilder.Entity<Measurement>(entity =>
    {
        entity.HasKey(e => e.Id).HasName("PK____3214EC075AA505CF");
        entity.Property(e => e.LedgerStartSequenceNumber).HasColumnName("ledger_start_sequence_number");
        entity.Property(e => e.LedgerStartTransactionId).HasColumnName("ledger_start_transaction_id");
    });
}

(Yes, it has some manual edits)

However if I now do a "add-migration" the Measurements table gets added to my migration chain. I'm not 100% sure what to do with this migration. Can I just update it so the resulting migration to something like the following or what is best?

migrationBuilder.Sql(@"CREATE TABLE [Measurements]
   (
	  Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
	  [ImportantFloat] FLOAT NOT NULL,
	  [ImportantNumber] BIGINT NOT NULL, 
          [Timestamp] DatetimeOffset NOT NULL
   )
   WITH (LEDGER = ON (APPEND_ONLY = ON));");

[roji]

@the-programmer you haven't shown the actual CreateTable migration created when doing add-migration. But yeah, you can simply replace that with the CREATE TABLE SQL above. A safe way would be to generate the SQL migration script from the original migration (which EF created), and then simply copy that SQL back into the migration as the replacement, adding the WITH (LEDGER... ).

[the-programmer]

@roji, I didn't think the CreateTable was required since that was "automatic".

But anyway, hor reference for someone else who might need this in the future (until ledger support is officially there).

• Add the table manually via SQL.
• Use reverse engineering to grab the full model from the database. (I used the option to create a 2nd context file)
• (if required) move the model "OnModelCreating" code to your dbContext. (Ensure the PRIMARY KEY is set correctly, otherwise you might have to update the ModelSnapshot).
• Run add-migration.
• Edit the migration so it looks like below (note, as you can see I updated the PRIMARY KEY part since we have a constant name this way.)
• Delete the ledger table from the database.
• Run update-database so the code in the migration runs.
• Done? (if not, let me know)

    public partial class AddLedger: Migration
    {
        /// <inheritdoc />
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.Sql(@"CREATE TABLE [Measurements]
                                   (
	                              [Id] INT NOT NULL IDENTITY,
	                              [ImportantFloat ] FLOAT NOT NULL,
	                              [ImportantNumber] BIGINT NOT NULL, 
                                      [Timestamp] DatetimeOffset NOT NULL,
	                              CONSTRAINT [PK_Measurements] PRIMARY KEY ([Id]),
                                   )
                                   WITH (LEDGER = ON (APPEND_ONLY = ON));");
        }

        /// <inheritdoc />
        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropTable(
                name: "Measurements");
        }
    }