Issue while scaffolding columns that are updated by a sequence

[PrgOrcl]

Summary- On scaffolding an existing table using the Sql Server efcore provider , in the model generated, the columns that are populated based on a given sequence are not having the "UseSequence()" fluent api appended to the property. I am part of a team which builds an efcore provider and we are facing this problem too.

To reproduce this scenario , you may use the below scripts to generate a table consisting of a primary key column(id), an IDENTITY column (identity_column) and an integer column associated with a sequence through a trigger(sequence_column). Script for the table, sequence and trigger-

create table my_table (
id int NOT NULL PRIMARY KEY,
sequence_column int,
identity_column int identity(1,1) not null);

create sequence my_sequence as int
start with 1
increment by 1;

create trigger my_trigger 
on my_table
after insert
as 
begin 
declare @newVal int;
select @newVal = next value for my_sequence;
update my_table set sequence_column=@newVal where sequence_column is null;
end;

On scaffolding the above schema in our application, the following OnModelCreating method is generated :

 protected override void OnModelCreating(ModelBuilder modelBuilder)
 {
     modelBuilder.Entity<MyTable>(entity =>
     {
         entity.HasKey(e => e.Id).HasName("PK__my_table__3213E83FFF40F99A");

         entity.ToTable("my_table", tb => tb.HasTrigger("my_trigger"));

         entity.Property(e => e.Id)
             .ValueGeneratedNever()
             .HasColumnName("id");
         entity.Property(e => e.IdentityColumn)
             .ValueGeneratedOnAdd()
             .HasColumnName("identity_column");
         entity.Property(e => e.SequenceColumn).HasColumnName("sequence_column");
     });
     modelBuilder.HasSequence<int>("my_sequence");

     OnModelCreatingPartial(modelBuilder);
 }

I then run my application to insert rows into the table:

    static void Main(string[] args)
    {

      var context = new MyLocalSqlServerContext();

      var obj = new MyTable()                          //creating an object to insert into table
      {
        Id = 12,                               // only providing primary key column as other two would be generated
      };

      context.MyTables.Add(obj);
      context.SaveChanges();
      Console.WriteLine(obj.Id + " :" + obj.IdentityColumn + " :" + obj.SequenceColumn);     // you may print out the generated values for reference

    }

The following script is generated and run by Sql Server efcore provider -

dbug: 8/19/2024 20:58:03.674 RelationalEventId.CommandExecuting[20100] (Microsoft.EntityFrameworkCore.Database.Command)
      Executing DbCommand [Parameters=[@p0='?' (DbType = Int32), @p1='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      INSERT INTO [my_table] ([id], [sequence_column])
      VALUES (@p0, @p1);
      SELECT [identity_column]
      FROM [my_table]
      WHERE @@ROWCOUNT = 1 AND [id] = @p0;

Also, the values we print from our application are as follows-

12 :1 :

As you can see, the sequence_column value is null here.

However, if we check the db -

We see that the value is generated on the db side.

This is an inconsistency. Our scaffolded code seems to not associate the sequence_column property with my_sequence. We want to associate the sequence and columns in such cases. In our provider, we have overridden the DatabaseModelFactory.Create() method. It internally fetches the metadata of the db objects like sequences, tables, columns etc. This information is passed on the the "Relational" layer using the DatabaseModel class instance. However there is no mechanism available within the DatabaseModel instance to associate a DatabaseColumn with a DatabaseSequence. Could you please let us know if during the scaffolding process is there a way to associate DatabaseColumn objects with sequnce if the corressponding column is having its value generated using a sequence?

---

                            Part-2

I would like to use the above model that is generated by scaffolding to generate the above schema in another DB using migrations. The following migration is generated :-

 public partial class initial1 : Migration
 {
     /// <inheritdoc />
     protected override void Up(MigrationBuilder migrationBuilder)
     {
         migrationBuilder.CreateSequence<int>(
             name: "my_sequence");

         migrationBuilder.CreateTable(
             name: "my_table",
             columns: table => new
             {
                 id = table.Column<int>(type: "int", nullable: false),
                 sequence_column = table.Column<int>(type: "int", nullable: true),
                 identity_column = table.Column<int>(type: "int", nullable: false)
                     .Annotation("SqlServer:Identity", "1, 1")
             },
             constraints: table =>
             {
                 table.PrimaryKey("PK__my_table__3213E83FFF40F99A", x => x.id);
             });
     }

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

         migrationBuilder.DropSequence(
             name: "my_sequence");
     }

i run the Update-Database command. i then run my application to insert rows into the table through efcore :

    static void Main(string[] args)
    {
      var context = new MyLocalSqlServerContext();

      var obj = new MyTable()                          //creating an object to insert into table
      {
        Id = 12,                               // only providing primary key column as other two would be generated
      };

      context.MyTables.Add(obj);
      context.SaveChanges();
      Console.WriteLine(obj.Id + " :" + obj.IdentityColumn + " :" + obj.SequenceColumn);     // you may print out the generated values for reference

    }

Here is the output script -

dbug: 8/19/2024 22:12:21.827 RelationalEventId.CommandExecuting[20100] info: 8/19/2024 22:12:21.889 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (64ms) [Parameters=[@p0='?' (DbType = Int32), @p1='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      INSERT INTO [my_table] ([id], [sequence_column])
      VALUES (@p0, @p1);
      SELECT [identity_column]
      FROM [my_table]
      WHERE @@ROWCOUNT = 1 AND [id] = @p0;

The table looks as such -

As you can see, the sequence_column value isn't generated as the sequence_column property wasn't associated with my_sequence in the scaffolded code.

However, if i manually add the useSequence() method to sequence_column property as below :

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<MyTable>(entity =>
        {
            entity.HasKey(e => e.Id).HasName("PK__my_table__3213E83FFF40F99A");

            entity.ToTable("my_table", tb => tb.HasTrigger("my_trigger"));

            entity.Property(e => e.Id)
                .ValueGeneratedNever()
                .HasColumnName("id");
            entity.Property(e => e.IdentityColumn)
                .ValueGeneratedOnAdd()
                .HasColumnName("identity_column");
            entity.Property(e => e.SequenceColumn).HasColumnName("sequence_column").UseSequence("my_sequence");
        });
        modelBuilder.HasSequence<int>("my_sequence");

        OnModelCreatingPartial(modelBuilder);
    }

Then we are able to generate the sequence_column value from the sequence as intended (p.s.- change the Id propety value in our application for every insert as it primary key and needs to be unique )-

info: 8/19/2024 22:23:13.176 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (43ms) [Parameters=[@p0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      INSERT INTO [my_table] ([id])
      VALUES (@p0);
      SELECT [identity_column], [sequence_column]
      FROM [my_table]
      WHERE @@ROWCOUNT = 1 AND [id] = @p0;

The database shows the sequence column isn't null for id=14 :-

We are having to manually add the UseSequence() fluent api to associate a property or column with a sequence. Is there a way to get it associated during scaffolding without us having to do it manually?

EF Core version: 8 Database provider: (e.g. Microsoft.EntityFrameworkCore.SqlServer) Target framework: (e.g. .NET 8.0) Operating system: IDE: (e.g. Visual Studio 2022 17.4)

[PrgOrcl]

@ajcvickers

[roji]

There's a lot going on up there, but right of the bat, are you expecting EF to somehow understand that there's a trigger in the database which updates sequence_column, and scaffold a model that takes that into account? If so, that's not something EF can do - triggers are arbitrary SQL functions which EF cannot possibly parse and make sense of.

create trigger my_trigger 
on my_table
after insert
as 
begin 
declare @newVal int;
select @newVal = next value for my_sequence;
update my_table set sequence_column=@newVal where sequence_column is null;
end;

If I've misunderstood, can you briefly summarize what your expectation here is?

[PrgOrcl]

There's a lot going on up there, but right of the bat, are you expecting EF to somehow understand that there's a trigger in the database which updates sequence_column, and scaffold a model that takes that into account? If so, that's not something EF can do - triggers are arbitrary SQL functions which EF cannot possibly parse and make sense of.

create trigger my_trigger 
on my_table
after insert
as 
begin 
declare @newVal int;
select @newVal = next value for my_sequence;
update my_table set sequence_column=@newVal where sequence_column is null;
end;

If I've misunderstood, can you briefly summarize what your expectation here is?

Hi @roji , No , we don't require EF (relational layer) to parse or understand the said triggers. That will be done by our provider. Our provider can determine if a particular column in a table is updated by a sequence or not. But how do I pass on this information to the EF (relational layer) that the column is updated by a sequence. The classes in the relational layer of ef don't have any fields where we can provide this information. Is there a way to achieve this?

[ajcvickers]

@PrgOrcl There is nothing I am aware of currently in EF Core that will scaffold columns using sequences. If the column has a default constraint that uses the sequence, then you will get that in the database model, and then you could interpret that and make a UseSequence call in a customized template. If you need more information from the database than is available in the column definition, then this probably isn't currently possible.