EF Core Issue: Incorrect Save Order with TPC Inheritance

[luigi68]

Bug description

IssueOnSave.zip

EF Core Issue: Incorrect Save Order with TPC Inheritance

Issue Description

When using Entity Framework Core with Table-Per-Concrete-type (TPC) inheritance strategy, the framework does not respect parent-child relationships during save operations. Specifically, child entities are being saved before their parent entities, causing foreign key constraint violations.

Environment

• EF Core version: 9.0.4
• Database provider: SQL Server
• Inheritance strategy: TPC (Table-Per-Concrete-type)

Observed Behavior

When saving a parent-child relationship where both entities are tracked by the context, EF Core attempts to save the child entities before their parents, leading to foreign key constraint violations.

SQL Server Profiler confirms that INSERT statements for child entities are being executed before INSERT statements for parent entities they depend on.

Expected Behavior

EF Core should analyze the entity graph and dependency relationships, then generate a correct save order that respects parent-child relationships. Parent entities should be saved before their children to satisfy foreign key constraints.

Code Sample

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace EFModel
{
    public abstract class BaseEntity
    {
        public decimal DEPTID { get; set; }

        public DateTime LASTUPDATED { get; set; } = DateTime.Now.Date;
    }

    public abstract class AMOS_ADDRESS : BaseEntity
    {
        [Key]
        public decimal ADDRESSID { get; set; }

        public string? CODE { get; set; }

        public string? ALPHACODE { get; set; }

        public decimal? ADDRESSCATEGORYID { get; set; }

        [ForeignKey("ADDRESSCATEGORYID")]
        public virtual AMOS_ADDRESSCATEGORY? ADDRESSCATEGORY { get; set; }

        public virtual List<AMOS_ADDRESSCONTACT> CONTACTS { get; set; }
    }

    public abstract class AMOS_ADDRESSCONTACT : BaseEntity
    {
        [Key]
        public decimal ADDRESSCONTACTID { get; set; }

        [ForeignKey("ADDRESSID")]
        public virtual AMOS_ADDRESS ADDRESS { get; set; }

        public decimal ADDRESSID { get; set; }

        public string FIRSTNAME { get; set; }

        public string LASTNAME { get; set; }

        public string? EMAIL { get; set; }

        public string? PHONE { get; set; }
    }

    public abstract class AMOS_ADDRESSCATEGORY : BaseEntity
    {
        [Key]
        public decimal ADDRESSCATEGORYID { get; set; }

        public string? DESCRIPTION { get; set; }

        public virtual ICollection<AMOS_ADDRESS> ADDRESSES { get; set; }
    }

    public abstract class AMOS_ACCOUNTCODE : BaseEntity
    {
        [Key]
        public decimal ACCOUNTCODEID
        {
            get;
            set;
        }

        public string CODE
        {
            get;
            set;
        }

        public decimal? ACCOUNTCODECATEGORYID
        {
            get;
            set;
        }

        [ForeignKey("ACCOUNTCODECATEGORYID")]
        public virtual AMOS_ACCOUNTCODECATEGORY ACCOUNTCODECATEGORY
        {
            get;
            set;
        }
    }

    public abstract class AMOS_ACCOUNTCODECATEGORY : BaseEntity
    {
        [Key]
        public decimal ACCOUNTCODECATEGORYID
        {
            get;
            set;
        }

        public decimal SEQUENCENO
        {
            get;
            set;
        }

        public decimal CATEGORYTYPE
        {
            get;
            set;
        }
    }

    public class ADDRESS : AMOS_ADDRESS
    { }

    public class ADDRESSCONTACT : AMOS_ADDRESSCONTACT
    { }

    public class ADDRESSCATEGORY : AMOS_ADDRESSCATEGORY
    { }

    public class ACCOUNTCODE : AMOS_ACCOUNTCODE
    { }

    public class ACCOUNTCODECATEGORY : AMOS_ACCOUNTCODECATEGORY
    { }
}```

## Reproduction Steps

1. Create a new category and address in the same SaveChanges call:

```csharp
using EFModel;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;
using Microsoft.EntityFrameworkCore.Metadata.Conventions;

namespace IssueOnSave
{
    public static class Tests
    {
        public static void Run()
        {
            var modelBuilder = new ModelBuilder(SqlServerConventionSetBuilder.Build());

            modelBuilder.HasDefaultSchema("dbo");

            modelBuilder.Entity<AMOS_ADDRESSCATEGORY>().UseTpcMappingStrategy();
            modelBuilder.Entity<ADDRESSCATEGORY>().ToTable("ADDRESSCATEGORY", "dbo");

            modelBuilder.Entity<AMOS_ACCOUNTCODECATEGORY>().UseTpcMappingStrategy();
            modelBuilder.Entity<ACCOUNTCODECATEGORY>().ToTable("ACCOUNTCODECATEGORY", "dbo");

            modelBuilder.Entity<AMOS_ACCOUNTCODE>().UseTpcMappingStrategy();
            modelBuilder.Entity<ACCOUNTCODE>().ToTable("ACCOUNTCODE", "dbo");

            modelBuilder.Entity<AMOS_ADDRESS>().UseTpcMappingStrategy();
            modelBuilder.Entity<ADDRESS>().ToTable("ADDRESS", "dbo");

            modelBuilder.Entity<AMOS_ADDRESSCONTACT>().UseTpcMappingStrategy();
            modelBuilder.Entity<ADDRESSCONTACT>().ToTable("ADDRESSCONTACT", "dbo");

            var optionsBuilder = new DbContextOptionsBuilder();
            optionsBuilder.UseSqlServer("Server=PCHome2;Database=testdb;User Id=sa;Password=sa;TrustServerCertificate=True;");
            optionsBuilder.UseLazyLoadingProxies(true);
            var model = modelBuilder.FinalizeModel();
            optionsBuilder.UseModel(model);

            using (var dbContext = new DbContext(optionsBuilder.Options))
            {
                using (var tr = dbContext.Database.BeginTransaction())
                {
                    var fks = dbContext.GetForeignKeys<ADDRESS>();
                    // The fks contains correctly the foreign keys defined for ADDRESS
                    dbContext.Set<ADDRESS>().Add(new ADDRESS() { ADDRESSCATEGORYID = null, ADDRESSID = 9999997, CODE = "CODE3", DEPTID = 1 });
                    dbContext.Set<ADDRESS>().Add(new ADDRESS() { ADDRESSCATEGORYID = 9999999, ADDRESSID = 9999998, CODE = "CODE", DEPTID = 1 });
                    dbContext.Set<ADDRESS>().Add(new ADDRESS() { ADDRESSCATEGORYID = 9999999, ADDRESSID = 9999999, CODE = "CODE2", DEPTID = 1 });

                    dbContext.Set<ACCOUNTCODECATEGORY>().Add(new ACCOUNTCODECATEGORY() { ACCOUNTCODECATEGORYID = 9999999, CATEGORYTYPE = 1, DEPTID = 1, SEQUENCENO = 1 });
                    dbContext.Set<ACCOUNTCODE>().Add(new ACCOUNTCODE() { ACCOUNTCODECATEGORYID = 9999999, ACCOUNTCODEID = 999999, CODE = "CODEABC", DEPTID = 1 });

                    dbContext.Set<ADDRESSCATEGORY>().Add(new ADDRESSCATEGORY() { ADDRESSCATEGORYID = 9999998, DESCRIPTION = "1ABCD", DEPTID = 1 });
                    dbContext.Set<ADDRESSCATEGORY>().Add(new ADDRESSCATEGORY() { ADDRESSCATEGORYID = 9999999, DESCRIPTION = "2ABCD", DEPTID = 1 });

                    dbContext.SaveChanges();
                    tr.Rollback();
                }
            }
        }

        public static IEnumerable<IForeignKey> GetForeignKeys<T>(this DbContext context)
        {
            var table = context.Model.FindEntityType(typeof(T));
            return table.GetForeignKeys();
        }
    }
}```

## Additional Information

This issue could be related to usage of TPC inheritance.

## SQL Script for Test Database

```sql
CREATE TABLE [dbo].[AccountCodeCategory](
	[AccountCodeCategoryID] [numeric](12, 0) NOT NULL,
	[SequenceNo] [numeric](2, 0) NOT NULL,
	[CategoryType] [numeric](1, 0) NOT NULL,
	[DeptID] [numeric](12, 0) NOT NULL,
	[ExportMarker] [numeric](1, 0) NOT NULL,
	[LastUpdated] [datetime] NOT NULL
 CONSTRAINT [PK_ACCOUNTCODECATEGORY] PRIMARY KEY CLUSTERED 
(
	[AccountCodeCategoryID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[AccountCodeCategory] ADD  DEFAULT ((1)) FOR [CategoryType]
GO

ALTER TABLE [dbo].[AccountCodeCategory] ADD  DEFAULT ((1)) FOR [ExportMarker]
GO

ALTER TABLE [dbo].[AccountCodeCategory] ADD  DEFAULT (getdate()) FOR [LastUpdated]
GO

CREATE TABLE [dbo].[AccountCode](
	[AccountCodeID] [numeric](12, 0) NOT NULL,
	[AccountCodeCategoryID] [numeric](12, 0) NOT NULL,
	[Code] [nvarchar](20) NOT NULL,
	[DeptID] [numeric](12, 0) NOT NULL,
	[ExportMarker] [numeric](1, 0) NOT NULL,
	[LastUpdated] [datetime] NOT NULL
 CONSTRAINT [PK_ACCOUNTCODE] PRIMARY KEY CLUSTERED 
(
	[AccountCodeID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[AccountCode] ADD  DEFAULT ((1)) FOR [ExportMarker]
GO

ALTER TABLE [dbo].[AccountCode] ADD  DEFAULT (getdate()) FOR [LastUpdated]
GO

ALTER TABLE [dbo].[AccountCode]  WITH CHECK ADD  CONSTRAINT [AccountCode_Category] FOREIGN KEY([AccountCodeCategoryID])
REFERENCES [dbo].[AccountCodeCategory] ([AccountCodeCategoryID])
GO

ALTER TABLE [dbo].[AccountCode] CHECK CONSTRAINT [AccountCode_Category]
GO

CREATE TABLE [dbo].[AddressCategory](
	[AddressCategoryID] [numeric](12, 0) NOT NULL,
	[Description] [nvarchar](30) NOT NULL,
	[DeptID] [numeric](12, 0) NOT NULL,
	[ExportMarker] [numeric](1, 0) NOT NULL,
	[LastUpdated] [datetime] NOT NULL
 CONSTRAINT [PK_ADDRESSCATEGORY] PRIMARY KEY CLUSTERED 
(
	[AddressCategoryID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[AddressCategory] ADD  DEFAULT ((1)) FOR [ExportMarker]
GO

ALTER TABLE [dbo].[AddressCategory] ADD  DEFAULT (getdate()) FOR [LastUpdated]
GO

CREATE TABLE [dbo].[Address](
	[AddressID] [numeric](12, 0) NOT NULL,
	[Code] [nvarchar](15) NOT NULL,
	[AlphaCode] [nvarchar](100) NULL,
	[AddressCategoryID] [numeric](12, 0) NULL,
	[DeptID] [numeric](12, 0) NOT NULL,
	[ExportMarker] [numeric](1, 0) NOT NULL,
	[LastUpdated] [datetime] NOT NULL
 CONSTRAINT [PK_ADDRESS] PRIMARY KEY CLUSTERED 
(
	[AddressID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Address] ADD  DEFAULT ((1)) FOR [ExportMarker]
GO

ALTER TABLE [dbo].[Address] ADD  DEFAULT (getdate()) FOR [LastUpdated]
GO

ALTER TABLE [dbo].[Address]  WITH CHECK ADD  CONSTRAINT [Address_AddressCategory] FOREIGN KEY([AddressCategoryID])
REFERENCES [dbo].[AddressCategory] ([AddressCategoryID])
GO

ALTER TABLE [dbo].[Address] CHECK CONSTRAINT [Address_AddressCategory]
GO

CREATE TABLE [dbo].[AddressContact](
	[AddressContactID] [numeric](12, 0) NOT NULL,
	[AddressID] [numeric](12, 0) NOT NULL,
	[LastName] [nvarchar](100) NOT NULL,
	[FirstName] [nvarchar](40) NOT NULL,
	[Email] [nvarchar](100) NULL,
	[Phone] [nvarchar](100) NULL,
	[DeptID] [numeric](12, 0) NOT NULL,
	[ExportMarker] [numeric](1, 0) NOT NULL,
	[LastUpdated] [datetime] NOT NULL
 CONSTRAINT [PK_ADDRESSCONTACT] PRIMARY KEY CLUSTERED 
(
	[AddressContactID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[AddressContact] ADD  DEFAULT ((1)) FOR [ExportMarker]
GO

ALTER TABLE [dbo].[AddressContact] ADD  DEFAULT (getdate()) FOR [LastUpdated]
GO

ALTER TABLE [dbo].[AddressContact]  WITH CHECK ADD  CONSTRAINT [AddressContact_Address] FOREIGN KEY([AddressID])
REFERENCES [dbo].[Address] ([AddressID])
GO

ALTER TABLE [dbo].[AddressContact] CHECK CONSTRAINT [AddressContact_Address]
GO

Your code

Reproducible code is attached

Stack traces

Verbose output

EF Core version

9.0.4

Database provider

No response

Target framework

.NET 8

Operating system

No response

IDE

Visual Studio 2022 17.13.6

[maumar]

I am unable to reproduce this. The repro code succeeds for me (no FK constraint violations are observed).

[luigi68]

@maumar
Using SQL Server profiler I paste below the statements submitted by my test application.
As you can see the Address is inserted before Address category and Account code before Account code category.
Please can you check the statements processed by your database?

sql exec sp_executesql N'SET NOCOUNT ON; INSERT INTO [dbo].[ACCOUNTCODE] ([ACCOUNTCODEID], [ACCOUNTCODECATEGORYID], [CODE], [DEPTID], [LASTUPDATED]) VALUES (@p0, @p1, @p2, @p3, @p4); INSERT INTO [dbo].[ACCOUNTCODECATEGORY] ([ACCOUNTCODECATEGORYID], [CATEGORYTYPE], [DEPTID], [LASTUPDATED], [SEQUENCENO]) VALUES (@p5, @p6, @p7, @p8, @p9); INSERT INTO [dbo].[ADDRESS] ([ADDRESSID], [ADDRESSCATEGORYID], [ALPHACODE], [CODE], [DEPTID], [LASTUPDATED]) VALUES (@p10, @p11, @p12, @p13, @p14, @p15), (@p16, @p17, @p18, @p19, @p20, @p21), (@p22, @p23, @p24, @p25, @p26, @p27); INSERT INTO [dbo].[ADDRESSCATEGORY] ([ADDRESSCATEGORYID], [DEPTID], [DESCRIPTION], [LASTUPDATED]) VALUES (@p28, @p29, @p30, @p31), (@p32, @p33, @p34, @p35); ',N'@p0 decimal(18,2),@p1 decimal(18,2),@p2 nvarchar(4000),@p3 decimal(18,2),@p4 datetime2(7),@p5 decimal(18,2),@p6 decimal(18,2),@p7 decimal(18,2),@p8 datetime2(7),@p9 decimal(18,2),@p10 decimal(18,2),@p11 decimal(18,2),@p12 nvarchar(4000),@p13 nvarchar(4000),@p14 decimal(18,2),@p15 datetime2(7),@p16 decimal(18,2),@p17 decimal(18,2),@p18 nvarchar(4000),@p19 nvarchar(4000),@p20 decimal(18,2),@p21 datetime2(7),@p22 decimal(18,2),@p23 decimal(18,2),@p24 nvarchar(4000),@p25 nvarchar(4000),@p26 decimal(18,2),@p27 datetime2(7),@p28 decimal(18,2),@p29 decimal(18,2),@p30 nvarchar(4000),@p31 datetime2(7),@p32 decimal(18,2),@p33 decimal(18,2),@p34 nvarchar(4000),@p35 datetime2(7)',@p0=999999.00,@p1=9999999.00,@p2=N'CODEABC',@p3=1.00,@p4='2025-04-23 00:00:00',@p5=9999999.00,@p6=1.00,@p7=1.00,@p8='2025-04-23 00:00:00',@p9=1.00,@p10=9999997.00,@p11=NULL,@p12=NULL,@p13=N'CODE3',@p14=1.00,@p15='2025-04-23 00:00:00',@p16=9999998.00,@p17=9999999.00,@p18=NULL,@p19=N'CODE',@p20=1.00,@p21='2025-04-23 00:00:00',@p22=9999999.00,@p23=9999999.00,@p24=NULL,@p25=N'CODE2',@p26=1.00,@p27='2025-04-23 00:00:00',@p28=9999998.00,@p29=1.00,@p30=N'1ABCD',@p31='2025-04-23 00:00:00',@p32=9999999.00,@p33=1.00,@p34=N'2ABCD',@p35='2025-04-23 00:00:00'

[maumar]

thanks for clarification @luigi68 I'm able to reproduce this now. SaveChanges was wrapped in a transaction that was rolled back. I see constraint violations when the transaction was removed and the data was actually committed to the database.

Also repros on main

[briangtn]

Same problem here, is there a clean workaround ?