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