Support sequence key generation on SQL Server, which is useful for Table-per-concrete-type (TPC) mappings

[Ogglas]

File a bug

Using:

.NET SDK 7.0.100-preview.4 https://dotnet.microsoft.com/en-us/download/dotnet/7.0

Visual Studio 2022 Preview 17.3 https://visualstudio.microsoft.com/vs/preview/

NuGet Microsoft.EntityFrameworkCore 7.0.0-preview.4.22229.2

Code example:

ApplicationDbContext:

using Microsoft.EntityFrameworkCore;

namespace WebApplicationNet7.Data
{
    public class ApplicationDbContext : DbContext
    {
        public DbSet<Blog> Blogs { get; set; }
        public DbSet<RssBlog> RssBlogs { get; set; }

        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
            : base(options)
        {
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Blog>().UseTpcMappingStrategy();
            modelBuilder.Entity<RssBlog>().UseTpcMappingStrategy();
        }
    }

    public class Blog
    {
        public int BlogId { get; set; }
        public string Url { get; set; }
    }

    public class RssBlog : Blog
    {
        public string RssUrl { get; set; }
    }
}

Migration will look like this:

Note that RssBlog is missing .Annotation("SqlServer:Identity", "1, 1").

You will probably get a warning that looks like this:

Microsoft.EntityFrameworkCore.Model.Validation[20609]

The property 'BlogId' on entity type 'Blog' is configured with a database-generated default, however the entity type is mapped to the database using table per concrete class strategy. Make sure that the generated values are unique across all the tables, duplicated values could result in errors or data corruption.

I could not get it to work with either setting

modelBuilder.Entity<RssBlog>().Property(u => u.BlogId).UseIdentityColumn(); or using annotation [DatabaseGenerated(DatabaseGeneratedOption.Identity)].

Include provider and version information

EF Core version: Microsoft.EntityFrameworkCore 7.0.0-preview.4.22229.2 Database provider: Microsoft.EntityFrameworkCore.SqlServer Target framework: .NET 7 Operating system: Windows 10 IDE: Visual Studio 2022 Preview 17.3

[ajcvickers]

@Ogglas It's generally quite hard to use generated key values with TPC. This is because each entity (row) needs to have a unique value across all tables. So if values 1 and 2 are used for Blog rows, and then the first RssBlog is inserted, it will need to use 3. But an Identity column will not do that.

I suspect you can use a database sequence for this, making sure that all tables get their values from the same sequence. I will discuss this with the team.

[ajcvickers]

Notes from triage:

• We could generate a sequence (as we do for HiLo generation) and then set the column default for the key column on every table to use this same sequence.
• This can be used for non-TPC tables as well, but is more useful for TPC since normal Identity columns have issues--see comment above.
• We will not set this by-default without more feedback that this is what people want.

[ajcvickers]

Note also it is easy to configure this manually. Just define a sequence using the ModelBuilder and set it as the column default:

modelBuilder.HasSequence<int>("AnimalIds");

modelBuilder.Entity<Animal>()
    .UseTpcMappingStrategy()
    .Property(e => e.Id).HasDefaultValueSql("NEXT VALUE FOR [AnimalIds]");

Runnable example:

public static class Your
{
    public static string ConnectionString = @"Data Source=(LocalDb)\MSSQLLocalDB;Database=SixOh";
}

public abstract class Animal
{
    public int Id { get; set; }
    public string Species { get; set; }
}

public class Pet : Animal
{
    public string Name  { get; set; }
    public ICollection<Human> Humans { get; } = new List<Human>();
}

public class Cat : Pet
{
    public string Breed { get; set; }
}

public class Dog : Pet
{
    public string Breed { get; set; }
}

public class Human : Animal
{
    public string FirstName  { get; set; }
    public string LastName  { get; set; }
    public ICollection<Pet> Pets { get; } = new List<Pet>();
}

public class FarmAnimal : Animal
{
    public Human Farmer { get; set; }
    public decimal Value { get; set; }
}

public class SomeDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(Your.ConnectionString)
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();

    public DbSet<Animal> Animals { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.HasSequence<int>("AnimalIds");

        modelBuilder.Entity<Animal>()
            .UseTpcMappingStrategy()
            .Property(e => e.Id).HasDefaultValueSql("NEXT VALUE FOR [AnimalIds]");

        modelBuilder.Entity<Pet>();
        modelBuilder.Entity<Cat>();
        modelBuilder.Entity<Dog>();
        modelBuilder.Entity<Human>();
        modelBuilder.Entity<FarmAnimal>();
    }
}

public class Program
{
    public static void Main()
    {
        using (var context = new SomeDbContext())
        {
            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            var arthur = new Human {FirstName = "Arthur", LastName = "Vickers", Species = "Pongo pygmaeus"};
            var wendy = new Human {FirstName = "Wendy", LastName = "Vickers", Species = "Homo sapiens"};
            var johnThePost = new Human {FirstName = "John", LastName = "The Post", Species = "Homo Scottish"};

            context.AddRange(
                new Cat {Name = "Alice", Species = "Felis catus", Humans = { arthur, wendy }},
                new Cat {Name = "Mac", Species = "Felis catus", Humans = { arthur, wendy }},
                new Dog {Name = "Toast", Species = "Canis familiaris", Humans = { arthur, wendy }},
                new FarmAnimal {Value = 100.0m, Species = "Ovis aries", Farmer = johnThePost});

            context.SaveChanges();
        }

        using (var context = new SomeDbContext())
        {
            foreach (var animal in context.Animals)
            {
                Console.WriteLine($"{animal.Id}: {animal.Species}");
            }
        }
    }
}

Output:

warn: 5/31/2022 20:35:06.466 CoreEventId.SensitiveDataLoggingEnabledWarning[10400] (Microsoft.EntityFrameworkCore.Infrastructure)
      Sensitive data logging is enabled. Log entries and exception messages may include sensitive application data; this mode should only be enabled during development.
warn: 5/31/2022 20:35:06.519 RelationalEventId.ForeignKeyTpcPrincipalWarning[20608] (Microsoft.EntityFrameworkCore.Model.Validation)
      The foreign key {'PetsId'} on the entity type 'HumanPet (Dictionary<string, object>)' targeting 'Pet' cannot be represented in the database. 'Pet' is mapped using the table per concrete type meaning that the derived entities will not be present in Pet. If this foreign key on 'HumanPet (Dictionary<string,
object>)' will never reference entities derived from 'Pet' then the foreign key constraint name can be specified explicitly to force it to be created.
warn: 5/31/2022 20:35:06.529 SqlServerEventId.DecimalTypeDefaultWarning[30000] (Microsoft.EntityFrameworkCore.Model.Validation)
      No store type was specified for the decimal property 'Value' on entity type 'FarmAnimal'. This will cause values to be silently truncated if they do not fit in the default precision and scale. Explicitly specify the SQL server column type that can accommodate all the values in 'OnModelCreating' using 'Has
ColumnType', specify precision and scale using 'HasPrecision', or configure a value converter using 'HasConversion'.
info: 5/31/2022 20:35:06.685 CoreEventId.ContextInitialized[10403] (Microsoft.EntityFrameworkCore.Infrastructure)
      Entity Framework Core 7.0.0-preview.6.22276.1 initialized 'SomeDbContext' using provider 'Microsoft.EntityFrameworkCore.SqlServer:7.0.0-preview.6.22276.1' with options: SensitiveDataLoggingEnabled
info: 5/31/2022 20:35:06.966 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (20ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT 1
info: 5/31/2022 20:35:07.017 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (5ms) [Parameters=[], CommandType='Text', CommandTimeout='60']
      IF SERVERPROPERTY('EngineEdition') <> 5
      BEGIN
          ALTER DATABASE [SixOh] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
      END;
info: 5/31/2022 20:35:07.035 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (17ms) [Parameters=[], CommandType='Text', CommandTimeout='60']
      DROP DATABASE [SixOh];
warn: 5/31/2022 20:35:07.049 RelationalEventId.ForeignKeyTpcPrincipalWarning[20608] (Microsoft.EntityFrameworkCore.Model.Validation)
      The foreign key {'PetsId'} on the entity type 'HumanPet (Dictionary<string, object>)' targeting 'Pet' cannot be represented in the database. 'Pet' is mapped using the table per concrete type meaning that the derived entities will not be present in Pet. If this foreign key on 'HumanPet (Dictionary<string,
object>)' will never reference entities derived from 'Pet' then the foreign key constraint name can be specified explicitly to force it to be created.
warn: 5/31/2022 20:35:07.050 SqlServerEventId.DecimalTypeDefaultWarning[30000] (Microsoft.EntityFrameworkCore.Model.Validation)
      No store type was specified for the decimal property 'Value' on entity type 'FarmAnimal'. This will cause values to be silently truncated if they do not fit in the default precision and scale. Explicitly specify the SQL server column type that can accommodate all the values in 'OnModelCreating' using 'Has
ColumnType', specify precision and scale using 'HasPrecision', or configure a value converter using 'HasConversion'.
info: 5/31/2022 20:35:07.164 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (106ms) [Parameters=[], CommandType='Text', CommandTimeout='60']
      CREATE DATABASE [SixOh];
info: 5/31/2022 20:35:07.197 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (33ms) [Parameters=[], CommandType='Text', CommandTimeout='60']
      IF SERVERPROPERTY('EngineEdition') <> 5
      BEGIN
          ALTER DATABASE [SixOh] SET READ_COMMITTED_SNAPSHOT ON;
      END;
info: 5/31/2022 20:35:07.199 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT 1
info: 5/31/2022 20:35:07.307 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE SEQUENCE [AnimalIds] AS int START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE NO CYCLE;
info: 5/31/2022 20:35:07.311 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE [Cat] (
          [Id] int NOT NULL DEFAULT (NEXT VALUE FOR [AnimalIds]),
          [Species] nvarchar(max) NULL,
          [Name] nvarchar(max) NULL,
          [Breed] nvarchar(max) NULL,
          CONSTRAINT [PK_Cat] PRIMARY KEY ([Id])
      );
info: 5/31/2022 20:35:07.312 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE [Dog] (
          [Id] int NOT NULL DEFAULT (NEXT VALUE FOR [AnimalIds]),
          [Species] nvarchar(max) NULL,
          [Name] nvarchar(max) NULL,
          [Breed] nvarchar(max) NULL,
          CONSTRAINT [PK_Dog] PRIMARY KEY ([Id])
      );
info: 5/31/2022 20:35:07.313 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE [Human] (
          [Id] int NOT NULL DEFAULT (NEXT VALUE FOR [AnimalIds]),
          [Species] nvarchar(max) NULL,
          [FirstName] nvarchar(max) NULL,
          [LastName] nvarchar(max) NULL,
          CONSTRAINT [PK_Human] PRIMARY KEY ([Id])
      );
info: 5/31/2022 20:35:07.315 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE [Pet] (
          [Id] int NOT NULL DEFAULT (NEXT VALUE FOR [AnimalIds]),
          [Species] nvarchar(max) NULL,
          [Name] nvarchar(max) NULL,
          CONSTRAINT [PK_Pet] PRIMARY KEY ([Id])
      );
info: 5/31/2022 20:35:07.316 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE [FarmAnimal] (
          [Id] int NOT NULL DEFAULT (NEXT VALUE FOR [AnimalIds]),
          [Species] nvarchar(max) NULL,
          [FarmerId] int NULL,
          [Value] decimal(18,2) NOT NULL,
          CONSTRAINT [PK_FarmAnimal] PRIMARY KEY ([Id]),
          CONSTRAINT [FK_FarmAnimal_Human_FarmerId] FOREIGN KEY ([FarmerId]) REFERENCES [Human] ([Id])
      );
info: 5/31/2022 20:35:07.317 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE [HumanPet] (
          [HumansId] int NOT NULL,
          [PetsId] int NOT NULL,
          CONSTRAINT [PK_HumanPet] PRIMARY KEY ([HumansId], [PetsId]),
          CONSTRAINT [FK_HumanPet_Human_HumansId] FOREIGN KEY ([HumansId]) REFERENCES [Human] ([Id]) ON DELETE CASCADE
      );
info: 5/31/2022 20:35:07.317 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE INDEX [IX_FarmAnimal_FarmerId] ON [FarmAnimal] ([FarmerId]);
info: 5/31/2022 20:35:07.318 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE INDEX [IX_HumanPet_PetsId] ON [HumanPet] ([PetsId]);
info: 5/31/2022 20:35:07.608 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (26ms) [Parameters=[@p0=NULL (Size = 4000), @p1='Alice' (Size = 4000), @p2='Felis catus' (Size = 4000), @p3=NULL (Size = 4000), @p4='Mac' (Size = 4000), @p5='Felis catus' (Size = 4000), @p6=NULL (Size = 4000), @p7='Toast' (Size = 4000), @p8='Canis familiaris' (Size = 4000), @p9='Arthur'
 (Size = 4000), @p10='Vickers' (Size = 4000), @p11='Pongo pygmaeus' (Size = 4000), @p12='Wendy' (Size = 4000), @p13='Vickers' (Size = 4000), @p14='Homo sapiens' (Size = 4000), @p15='John' (Size = 4000), @p16='The Post' (Size = 4000), @p17='Homo Scottish' (Size = 4000)], CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      MERGE [Cat] USING (
      VALUES (@p0, @p1, @p2, 0),
      (@p3, @p4, @p5, 1)) AS i ([Breed], [Name], [Species], _Position) ON 1=0
      WHEN NOT MATCHED THEN
      INSERT ([Breed], [Name], [Species])
      VALUES (i.[Breed], i.[Name], i.[Species])
      OUTPUT INSERTED.[Id], i._Position;
      INSERT INTO [Dog] ([Breed], [Name], [Species])
      OUTPUT INSERTED.[Id]
      VALUES (@p6, @p7, @p8);
      MERGE [Human] USING (
      VALUES (@p9, @p10, @p11, 0),
      (@p12, @p13, @p14, 1),
      (@p15, @p16, @p17, 2)) AS i ([FirstName], [LastName], [Species], _Position) ON 1=0
      WHEN NOT MATCHED THEN
      INSERT ([FirstName], [LastName], [Species])
      VALUES (i.[FirstName], i.[LastName], i.[Species])
      OUTPUT INSERTED.[Id], i._Position;
info: 5/31/2022 20:35:07.645 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (8ms) [Parameters=[@p18='6' (Nullable = true), @p19='Ovis aries' (Size = 4000), @p20='100.0' (Precision = 18) (Scale = 2), @p21='4', @p22='1', @p23='4', @p24='2', @p25='4', @p26='3', @p27='5', @p28='1', @p29='5', @p30='2', @p31='5', @p32='3'], CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      INSERT INTO [FarmAnimal] ([FarmerId], [Species], [Value])
      OUTPUT INSERTED.[Id]
      VALUES (@p18, @p19, @p20);
      INSERT INTO [HumanPet] ([HumansId], [PetsId])
      VALUES (@p21, @p22),
      (@p23, @p24),
      (@p25, @p26),
      (@p27, @p28),
      (@p29, @p30),
      (@p31, @p32);
info: 5/31/2022 20:35:07.684 CoreEventId.ContextInitialized[10403] (Microsoft.EntityFrameworkCore.Infrastructure)
      Entity Framework Core 7.0.0-preview.6.22276.1 initialized 'SomeDbContext' using provider 'Microsoft.EntityFrameworkCore.SqlServer:7.0.0-preview.6.22276.1' with options: SensitiveDataLoggingEnabled
info: 5/31/2022 20:35:07.857 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT [f].[Id], [f].[Species], [f].[FarmerId], [f].[Value], NULL AS [FirstName], NULL AS [LastName], NULL AS [Name], NULL AS [Breed], NULL AS [Breed0], N'FarmAnimal' AS [Discriminator]
      FROM [FarmAnimal] AS [f]
      UNION ALL
      SELECT [h].[Id], [h].[Species], NULL AS [FarmerId], NULL AS [Value], [h].[FirstName], [h].[LastName], NULL AS [Name], NULL AS [Breed], NULL AS [Breed0], N'Human' AS [Discriminator]
      FROM [Human] AS [h]
      UNION ALL
      SELECT [p].[Id], [p].[Species], NULL AS [FarmerId], NULL AS [Value], NULL AS [FirstName], NULL AS [LastName], [p].[Name], NULL AS [Breed], NULL AS [Breed0], N'Pet' AS [Discriminator]
      FROM [Pet] AS [p]
      UNION ALL
      SELECT [c].[Id], [c].[Species], NULL AS [FarmerId], NULL AS [Value], NULL AS [FirstName], NULL AS [LastName], [c].[Name], [c].[Breed], NULL AS [Breed0], N'Cat' AS [Discriminator]
      FROM [Cat] AS [c]
      UNION ALL
      SELECT [d].[Id], [d].[Species], NULL AS [FarmerId], NULL AS [Value], NULL AS [FirstName], NULL AS [LastName], [d].[Name], NULL AS [Breed], [d].[Breed] AS [Breed0], N'Dog' AS [Discriminator]
      FROM [Dog] AS [d]
7: Ovis aries
4: Pongo pygmaeus
5: Homo sapiens
6: Homo Scottish
1: Felis catus
2: Felis catus
3: Canis familiaris

[roji]

Design decision: we'll do this by default, but as a provider-specific feature rather than as a relational one (note that SQLite doesn't support sequences). However, if it makes sense we may introduce a reusable convention into relational which can then be opted-into by different providers.

[roji]

When implementing, pay attention to what happens when the table is renamed (do we rename the associated sequence etc.)

[adiletelf]

As far as I understand when using TPC they are 2 ways to store primary keys: 1) Use GUID and no sequence. 2) Use sequence - the id is fetched from the sequence, those providing unique PK between TPC tables.

I wonder what are consequences of using the approach with sequence. Are there any differences between identity id column and just integer id column (from sequence)?

Microsoft shows the example of key generation:

modelBuilder.Entity<Cat>().ToTable("Cats", tb => tb.Property(e => e.Id).UseIdentityColumn(1, 4));
modelBuilder.Entity<Dog>().ToTable("Dogs", tb => tb.Property(e => e.Id).UseIdentityColumn(2, 4));
modelBuilder.Entity<FarmAnimal>().ToTable("FarmAnimals", tb => tb.Property(e => e.Id).UseIdentityColumn(3, 4));
modelBuilder.Entity<Human>().ToTable("Humans", tb => tb.Property(e => e.Id).UseIdentityColumn(4, 4));

So if I add or remove types from TPC hierarchy will it break the key generation or is the sequence not affected? The first approach with GUID keys seems simpler, so what are the reasons to choose the second approach with sequence other than higher storage requirements for GUID primary keys?

I would be grateful if somebody gave me answers

[ajcvickers]

@adiletelf

So if I add or remove types from TPC hierarchy will it break the key generation or is the sequence not affected?

The example code you show does not use a sequence. Instead, it shows a different mechanism using identity columns. It's not recommended, but is an alternative if, for some reason, you don't want to use the sequence.

The first approach with GUID keys seems simpler, so what are the reasons to choose the second approach with sequence other than higher storage requirements for GUID primary keys?

The sequence approach, which is the default for integer key properties and doesn't require any additional model configuration, is fine, and there really isn't any reason to use GUIDs because to avoid it.

[adiletelf]

Now I am sure that I can use either sequences or identity columns as shown in Microsoft example, thank you for your answer.

[StakhurA]

Can we avoid creating a sequence when we have the TPC approach and want to have a sequence number as a sql identity only in the base class?

NuGet: Microsoft.EntityFrameworkCore Version=7.0.3 Example:

    public class ApplicationDbContext : DbContext
    {
        public DbSet<Blog> Blogs { get; set; }
        public DbSet<ArchivedBlog> ArchivedBlog { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            var blogBuilder = modelBuilder.Entity<Blog>()
                .UseTpcMappingStrategy();
            blogBuilder.Property<long>("SequenceNumber")
                .ValueGeneratedOnAdd();

            var archivedBlogBuilder = modelBuilder.Entity<ArchivedBlog>();
            archivedBlogBuilder.Property<long>("SequenceNumber");
        }
    }

    public class Blog
    {
        public Guid BlogId { get; set; }
        public string Url { get; set; }
    }

    public class ArchivedBlog : Blog
    {
    }

It creates the following migration: So it creates BlogSequence and uses it for both SequenceNumber columns in Blog and ArchivedBlog tables. However, I configured ValueGeneratedOnAdd only for the Blog entity. Is it possible to avoid creating a sequence "BlogSequence", so that we have SequenceNumber as ("SqlServer:Identity", "1, 1") in Blog and SequenceNumber as "bigint" in ArchivedBlog? My intention is: (it can be achieved by introducing a base class for example BlogBase with all Blog's properties and inheriting this base class by Blog and ArchivedBlog classes. However, I need to have a class hierarchy which is described in the example)

[roji]

Can we avoid creating a sequence when we have the TBH approach and want to have a sequence number as a shadow property only in the base class?

You mean TPC right?

All entity types in a hierarchy must have distinct key (ID) values; if you happen to have the same key value across two tables participating in a hierarchy, EF will not be able to function correctly (it cannot track the two instances as they have the same key).

As long as you make sure that you never have two rows with the same key across all your TPC tables, EF doesn't care which mechanism you use to achieve that (sequence or other). The easiest way to do that is to simply have a single sequence for the multiple tables - that automatically ensures key uniqueness without lots of complexity. However, you could have two identity columns - for Blog and ArchiveBlog - and make sure the values never overlap (e.g. start the ArchiveBlog identity at some very high value).

However, is there a particular reason why you're trying to avoid having a sequence?

[StakhurA]

Hi @roji thanks for the quick response. yes, indeed, I meant TPC.

In our case, we want to copy rows from the Blog table to the ArchivedBlog table with original values from the Blog table. So we don't need to generate and increment the SequenceNumber value in ArchivedBlog, it is just a value from the Blog table. However, in the base Blog class, we need to have SequenceNumber as SqlServer.Identity(1,1).

[roji]

@StakhurA if you can guarantee that the only rows in ArchiveBlog are copied across from Blog (and that these rows are deleted from Blog), then that indeed takes care of ID uniqueness. At that point you can indeed just use a regular identity column in Blog, and not have database-generated keys at all in ArchiveBlog (since you'll always be inserting the ID with the values copied from Blog).

[StakhurA]

@roji yes, that was my intention to use a regular identity column (SequenceNumber) in Blog entity, and configure the SequenceNumber column in ArchivedBlog so that it should not be a database-generated. however, as it is shown in the example, if I use TpcMappingStrategy for the Blog entity and configure SequenceNumber property as an identity column in Blog entity (parent), then in ArchivedBlog entity (child) it will be configured in the same way, even if I don't configure SequenceNumber as an identity column there. As result, it creates a single sequence for both tables, which is used to guarantee SequenceNumber uniqueness in a hierarchy.

[roji]

@StakhurA can you please open a new issue with a minimal, runnable code sample that shows the problem?

[StakhurA]

@roji yes, sure. https://github.com/dotnet/efcore/issues/30520

[yuosif1286]

![Uploading sql server - Create sequence based on the value of another column - Database Administrators Stack Exchange - Google Chrome 2_5_2024 3_09_45 PM.png…]()

Hi everyone, how can i Implement Sequences Dependens on other forighen key wirh this examble https://dba.stackexchange.com/questions/251778/create-sequence-based-on-the-value-of-another-column