Discriminator columns are created in Snapshot for wrongly configured TPC mapping strategy

[AlexDelPab]

Bug explanation

Assume the following sample:

abstract class Animal {
   public Food Food { get; set; }
   public int Id { get; set; }
}
class Cat : Animal {}
class Dog : Animal {}

class Food {
  public List<Animal> Animals { get; set; }
}

By convention, EF tools would create the following tables for the abovementioned hierarchy: Animals, Cats, Dogs, and Foods, meaning table-per-hierarchy (TPH).

If I want to create the tables as table-per-concrete-type (TPC), I can explicitly ignore the abstract class with the following line modelBuilder.Ignore<Animal>();

When creating the first migration with the Ignore call via dotnet ef migrations add Initial, EF is confused about mixing TPH and TPC by adding a Discriminator value in the Snapshot file for the concrete classes which looks like this

modelBuilder.Entity("Cat", b =>
                {
                    b.Property<int>("Id")
                        .ValueGeneratedOnAdd()
                        .HasColumnType("int");

                    SqlServerPropertyBuilderExtensions.UseIdentityColumn(b.Property<int>("Id"));

                  b.ToTable("Cats");

                    b.HasDiscriminator().HasValue("Cat"); // <-- this line should not be here
                });

When a second migration is created, the discriminator is dropped.

protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropColumn(
                name: "Discriminator",
                table: "Cats");

            migrationBuilder.DropColumn(
                name: "Discriminator",
                table: "Dogs");
        }

After calling dotnet ef database update, an exception is thrown because the discriminator column does not exist.

Using project '/Users/user/Downloads/EfCoreInheritanceSample/EfCoreInheritanceSample/EfCoreInheritanceSample.csproj'.                                                  1 х  5s  13:57:19 ▓▒░
Using startup project '/Users/user/Downloads/EfCoreInheritanceSample/EfCoreInheritanceSample/EfCoreInheritanceSample.csproj'.
Writing '/Users/user/Downloads/EfCoreInheritanceSample/EfCoreInheritanceSample/obj/EfCoreInheritanceSample.csproj.EntityFrameworkCore.targets'...
dotnet msbuild /target:GetEFProjectMetadata /property:EFProjectMetadataFile=/var/folders/0x/5kkhnp1x3wndfz1fj7xr1qph0000gn/T/tmp3rdHHw.tmp /verbosity:quiet /nologo /Users/user/Downloads/EfCoreInheritanceSample/EfCoreInheritanceSample/EfCoreInheritanceSample.csproj
Writing '/Users/user/Downloads/EfCoreInheritanceSample/EfCoreInheritanceSample/obj/EfCoreInheritanceSample.csproj.EntityFrameworkCore.targets'...
dotnet msbuild /target:GetEFProjectMetadata /property:EFProjectMetadataFile=/var/folders/0x/5kkhnp1x3wndfz1fj7xr1qph0000gn/T/tmpClAEnm.tmp /verbosity:quiet /nologo /Users/user/Downloads/EfCoreInheritanceSample/EfCoreInheritanceSample/EfCoreInheritanceSample.csproj
Build started...
dotnet build /Users/user/Downloads/EfCoreInheritanceSample/EfCoreInheritanceSample/EfCoreInheritanceSample.csproj /verbosity:quiet /nologo /p:PublishAot=false
/Users/user/Downloads/EfCoreInheritanceSample/EfCoreInheritanceSample/Entities.cs(15,19): warning CS8618: Non-nullable property 'Color' must contain a non-null value when exiting constructor. Consider declaring the property as nullable. [/Users/user/Downloads/EfCoreInheritanceSample/EfCoreInheritanceSample/EfCoreInheritanceSample.csproj]
/Users/user/Downloads/EfCoreInheritanceSample/EfCoreInheritanceSample/Entities.cs(7,19): warning CS8618: Non-nullable property 'Name' must contain a non-null value when exiting constructor. Consider declaring the property as nullable. [/Users/user/Downloads/EfCoreInheritanceSample/EfCoreInheritanceSample/EfCoreInheritanceSample.csproj]
/Users/user/Downloads/EfCoreInheritanceSample/EfCoreInheritanceSample/Entities.cs(26,19): warning CS8618: Non-nullable property 'Name' must contain a non-null value when exiting constructor. Consider declaring the property as nullable. [/Users/user/Downloads/EfCoreInheritanceSample/EfCoreInheritanceSample/EfCoreInheritanceSample.csproj]

Build succeeded.

/Users/user/Downloads/EfCoreInheritanceSample/EfCoreInheritanceSample/Entities.cs(15,19): warning CS8618: Non-nullable property 'Color' must contain a non-null value when exiting constructor. Consider declaring the property as nullable. [/Users/user/Downloads/EfCoreInheritanceSample/EfCoreInheritanceSample/EfCoreInheritanceSample.csproj]
/Users/user/Downloads/EfCoreInheritanceSample/EfCoreInheritanceSample/Entities.cs(7,19): warning CS8618: Non-nullable property 'Name' must contain a non-null value when exiting constructor. Consider declaring the property as nullable. [/Users/user/Downloads/EfCoreInheritanceSample/EfCoreInheritanceSample/EfCoreInheritanceSample.csproj]
/Users/user/Downloads/EfCoreInheritanceSample/EfCoreInheritanceSample/Entities.cs(26,19): warning CS8618: Non-nullable property 'Name' must contain a non-null value when exiting constructor. Consider declaring the property as nullable. [/Users/user/Downloads/EfCoreInheritanceSample/EfCoreInheritanceSample/EfCoreInheritanceSample.csproj]
    3 Warning(s)
    0 Error(s)

Time Elapsed 00:00:00.80
Build succeeded.
dotnet exec --depsfile /Users/user/Downloads/EfCoreInheritanceSample/EfCoreInheritanceSample/bin/Debug/net8.0/EfCoreInheritanceSample.deps.json --additionalprobingpath /Users/user/.nuget/packages --runtimeconfig /Users/user/Downloads/EfCoreInheritanceSample/EfCoreInheritanceSample/bin/Debug/net8.0/EfCoreInheritanceSample.runtimeconfig.json /Users/user/.nuget/packages/dotnet-ef/8.0.4/tools/net8.0/any/tools/netcoreapp2.0/any/ef.dll database update --assembly /Users/user/Downloads/EfCoreInheritanceSample/EfCoreInheritanceSample/bin/Debug/net8.0/EfCoreInheritanceSample.dll --project /Users/user/Downloads/EfCoreInheritanceSample/EfCoreInheritanceSample/EfCoreInheritanceSample.csproj --startup-assembly /Users/user/Downloads/EfCoreInheritanceSample/EfCoreInheritanceSample/bin/Debug/net8.0/EfCoreInheritanceSample.dll --startup-project /Users/user/Downloads/EfCoreInheritanceSample/EfCoreInheritanceSample/EfCoreInheritanceSample.csproj --project-dir /Users/user/Downloads/EfCoreInheritanceSample/EfCoreInheritanceSample/ --root-namespace EfCoreInheritanceSample --language C# --framework net8.0 --nullable --working-dir /Users/user/Downloads/EfCoreInheritanceSample/EfCoreInheritanceSample --verbose
Using assembly 'EfCoreInheritanceSample'.
Using startup assembly 'EfCoreInheritanceSample'.
Using application base '/Users/user/Downloads/EfCoreInheritanceSample/EfCoreInheritanceSample/bin/Debug/net8.0'.
Using working directory '/Users/user/Downloads/EfCoreInheritanceSample/EfCoreInheritanceSample'.
Using root namespace 'EfCoreInheritanceSample'.
Using project directory '/Users/user/Downloads/EfCoreInheritanceSample/EfCoreInheritanceSample/'.
Remaining arguments: .
Finding DbContext classes...
Finding IDesignTimeDbContextFactory implementations...
Finding application service provider in assembly 'EfCoreInheritanceSample'...
Finding Microsoft.Extensions.Hosting service provider...
No static method 'CreateHostBuilder(string[])' was found on class 'Program'.
No application service provider was found.
Finding DbContext classes in the project...
Found DbContext 'DataContext'.
Using context 'DataContext'.
Finding design-time services referenced by assembly 'EfCoreInheritanceSample'...
Finding design-time services referenced by assembly 'EfCoreInheritanceSample'...
No referenced design-time services were found.
Finding design-time services for provider 'Microsoft.EntityFrameworkCore.SqlServer'...
Using design-time services from provider 'Microsoft.EntityFrameworkCore.SqlServer'.
Finding IDesignTimeServices implementations in assembly 'EfCoreInheritanceSample'...
No design-time services were found.
Creating DbConnection.
Created DbConnection. (17ms).
Migrating using database 'EfCoreInheritanceSample' on server 'localhost'.
Opening connection to database 'EfCoreInheritanceSample' on server 'localhost'.
Opened connection to database 'EfCoreInheritanceSample' on server 'localhost'.
Creating DbCommand for 'ExecuteNonQuery'.
Created DbCommand for 'ExecuteNonQuery' (1ms).
Initialized DbCommand for 'ExecuteNonQuery' (3ms).
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT 1
Executed DbCommand (14ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT 1
Closing connection to database 'EfCoreInheritanceSample' on server 'localhost'.
Closed connection to database 'EfCoreInheritanceSample' on server 'localhost' (1ms).
Creating DbCommand for 'ExecuteScalar'.
Created DbCommand for 'ExecuteScalar' (0ms).
Initialized DbCommand for 'ExecuteScalar' (0ms).
Opening connection to database 'EfCoreInheritanceSample' on server 'localhost'.
Opened connection to database 'EfCoreInheritanceSample' on server 'localhost'.
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT OBJECT_ID(N'[__EFMigrationsHistory]');
Executed DbCommand (8ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT OBJECT_ID(N'[__EFMigrationsHistory]');
Closing connection to database 'EfCoreInheritanceSample' on server 'localhost'.
Closed connection to database 'EfCoreInheritanceSample' on server 'localhost' (0ms).
Opening connection to database 'EfCoreInheritanceSample' on server 'localhost'.
Opened connection to database 'EfCoreInheritanceSample' on server 'localhost'.
Creating DbCommand for 'ExecuteNonQuery'.
Created DbCommand for 'ExecuteNonQuery' (0ms).
Initialized DbCommand for 'ExecuteNonQuery' (0ms).
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT 1
Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT 1
Closing connection to database 'EfCoreInheritanceSample' on server 'localhost'.
Closed connection to database 'EfCoreInheritanceSample' on server 'localhost' (0ms).
Creating DbCommand for 'ExecuteScalar'.
Created DbCommand for 'ExecuteScalar' (0ms).
Initialized DbCommand for 'ExecuteScalar' (0ms).
Opening connection to database 'EfCoreInheritanceSample' on server 'localhost'.
Opened connection to database 'EfCoreInheritanceSample' on server 'localhost'.
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT OBJECT_ID(N'[__EFMigrationsHistory]');
Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT OBJECT_ID(N'[__EFMigrationsHistory]');
Closing connection to database 'EfCoreInheritanceSample' on server 'localhost'.
Closed connection to database 'EfCoreInheritanceSample' on server 'localhost' (0ms).
Opening connection to database 'EfCoreInheritanceSample' on server 'localhost'.
Opened connection to database 'EfCoreInheritanceSample' on server 'localhost'.
Creating DbCommand for 'ExecuteReader'.
Created DbCommand for 'ExecuteReader' (0ms).
Initialized DbCommand for 'ExecuteReader' (0ms).
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [MigrationId], [ProductVersion]
FROM [__EFMigrationsHistory]
ORDER BY [MigrationId];
Executed DbCommand (4ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [MigrationId], [ProductVersion]
FROM [__EFMigrationsHistory]
ORDER BY [MigrationId];
Closing data reader to 'EfCoreInheritanceSample' on server 'localhost'.
A data reader for 'EfCoreInheritanceSample' on server 'localhost' is being disposed after spending 0ms reading results.
Closing connection to database 'EfCoreInheritanceSample' on server 'localhost'.
Closed connection to database 'EfCoreInheritanceSample' on server 'localhost' (0ms).
Applying migration '20240424115147_Test'.
Opening connection to database 'EfCoreInheritanceSample' on server 'localhost'.
Opened connection to database 'EfCoreInheritanceSample' on server 'localhost'.
Beginning transaction with isolation level 'Unspecified'.
Began transaction with isolation level 'ReadCommitted'.
Creating DbCommand for 'ExecuteNonQuery'.
Created DbCommand for 'ExecuteNonQuery' (0ms).
Initialized DbCommand for 'ExecuteNonQuery' (0ms).
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
DECLARE @var0 sysname;
SELECT @var0 = [d].[name]
FROM [sys].[default_constraints] [d]
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[B]') AND [c].[name] = N'Discriminator');
IF @var0 IS NOT NULL EXEC(N'ALTER TABLE [B] DROP CONSTRAINT [' + @var0 + '];');
ALTER TABLE [B] DROP COLUMN [Discriminator];
Failed executing DbCommand (17ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
DECLARE @var0 sysname;
SELECT @var0 = [d].[name]
FROM [sys].[default_constraints] [d]
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[B]') AND [c].[name] = N'Discriminator');
IF @var0 IS NOT NULL EXEC(N'ALTER TABLE [B] DROP CONSTRAINT [' + @var0 + '];');
ALTER TABLE [B] DROP COLUMN [Discriminator];
Disposing transaction.
Closing connection to database 'EfCoreInheritanceSample' on server 'localhost'.
Closed connection to database 'EfCoreInheritanceSample' on server 'localhost' (0ms).
'DataContext' disposed.
Disposing connection to database 'EfCoreInheritanceSample' on server 'localhost'.
Disposed connection to database '' on server '' (0ms).
Microsoft.Data.SqlClient.SqlException (0x80131904): ALTER TABLE DROP COLUMN failed because column 'Discriminator' does not exist in table 'B'.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean isAsync, Int32 timeout, Boolean asyncWrite)
   at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
ClientConnectionId:16ac73a4-0190-4d93-afd6-9766002f407a
Error Number:4924,State:1,Class:16
ALTER TABLE DROP COLUMN failed because column 'Discriminator' does not exist in table 'B'.

Reproduction steps

1. Download the attached ZIP folder containing the .NET 8 sample
2. Delete the migrations folder
3. Create an initial migration via dotnet ef migrations add Initial
4. Check the snapshot file where the discriminator column is added to the tables of the concrete classes
5. Again create a migration via dotnet ef migrations add Second
6. Check the migration file where the discriminator column is dropped
7. Run the migrations via dotnet ef database update

Seems to address a maybe similar problem like #20410

Include provider and version information

EF Core version: 8.0.4
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 8.0
Operating system: MacOS Sonoma 14.2.1
IDE: JetBrains Rider 2024.1
EfCoreInheritanceSample.zip

[ajcvickers]

Still repros on latest; regression from 7.