Invalid reference between parent and child entities

[Uriil]

We are using EF Core 8.0.6 and observe strange reference assignment when trying to save hierarchy of objects. We have next structure: Entity, Child Entity and CustomAttribute. Entity has multiple CustomAttributes and multiple ChildEntities. ChildEntity has multiple CustomAttributes. CustomAttribute for both Entity and ChildEntity is the same class. Whenever we save hierarchy of objects EF Core merges CustomAttributes for Entity and FIRST(literally first) ChildEntity. Please see code below and result

internal class Program
{
    static async Task Main(string[] args)
    {
        await using var context = new MyContext();
        var newEntity = new Entity
        {
            Name = "EntityName",
            CustomAttributes = new Collection<CustomAttribute>
            {
                new CustomAttribute{ Value = 1, Type = "Entity" },
            },
            ChildEntities =
            [
                new ChildEntity
                {
                    Name = "ChildEntity1",
                    CustomAttributes = new Collection<CustomAttribute>
                    {
                        new CustomAttribute { Value = 1000, Type = "ChildEntity" },
                    },
                },
                new ChildEntity
                {
                    Name = "ChildEntity2",
                    CustomAttributes = new Collection<CustomAttribute>
                    {
                        new CustomAttribute { Value = 10000, Type = "ChildEntity" },
                    },
                }
            ]
        };

        context.Entities.Add(newEntity);
        await context.SaveChangesAsync();

        Console.WriteLine("Hello, World!");
    }
}

public class Entity
{
    public int Id { get; set; }
    public string Name { get; set; }
    public Collection<ChildEntity> ChildEntities { get; set; }
    public Collection<CustomAttribute> CustomAttributes { get; set; }
}

public class ChildEntity
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int ParentId { get; set; }
    public Entity Entity { get; set; }

    public Collection<CustomAttribute> CustomAttributes { get; set; }
}

public class CustomAttribute
{
    public int Id { get; set; }
    public int ReferenceId { get; set; }
    public int Value { get; set; }
    public string Type { get; set; }
}

public class MyContext : DbContext
{
    public DbSet<Entity> Entities { get; set; }
    public DbSet<ChildEntity> ChildEntities { get; set; }
    public DbSet<CustomAttribute> CustomAttributes { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder options)
    {
        options.LogTo(Console.WriteLine).EnableSensitiveDataLogging();
        options.UseSqlServer("Server=localhost;Database=Test;Integrated Security=true;TrustServerCertificate=True");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<Entity>().ToTable("Entity");
        modelBuilder.Entity<Entity>().HasKey(p => p.Id);

        modelBuilder.Entity<ChildEntity>().ToTable("ChildEntity");
        modelBuilder.Entity<ChildEntity>().HasKey(p => p.Id);

        modelBuilder.Entity<CustomAttribute>().ToTable("CustomAttribute");
        modelBuilder.Entity<CustomAttribute>().HasKey(p => p.Id);

        modelBuilder.Entity<Entity>().HasMany(p => p.ChildEntities).WithOne(p => p.Entity).HasForeignKey(ss => ss.ParentId);

        modelBuilder.Entity<ChildEntity>().HasMany(p => p.CustomAttributes).WithOne().HasForeignKey(ss => ss.ReferenceId).HasPrincipalKey(p => p.Id);
        modelBuilder.Entity<Entity>().HasMany(p => p.CustomAttributes).WithOne().HasForeignKey(ss => ss.ReferenceId).HasPrincipalKey(p => p.Id);
    }
}

And result in database:

Entity and ChildEntity tables are good, problem in CustomAttribute. There is corruption in CustomAttribute for first child entity(record with Id 1 on above screenshot), it has value from first ChildEntity, but id of parent Entity

What's more, problem occurs on adding entity to context, not on saving it, looks like Entity and FIRST ChildEntity reference same merged collection of CustomAttributes:

Expected results

• Save all objects properly
• Throw exception if given setup is not possible

Workarounds which work

• Save Entity and ChildEntity separately
• Implement inheritance for CustomAttributes to separate classes for Entity and ChildEntity

Include provider and version information

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

[ajcvickers]

The issue here is that the ReferenceId property is being forced into being a foreign key for two different relationships. @AndriySvyryd Given that you assigned this to me, I assume you are saying this is a valid model and fixup should work correctly?

[AndriySvyryd]

@AndriySvyryd Given that you assigned this to me, I assume you are saying this is a valid model and fixup should work correctly?

No, it just means that I think you would be a good choice to investigate this. We can still decide that this isn't a supported scenario.

[ajcvickers]

@AndriySvyryd Do you think we should allow creating a table where the same FK is constrained to two different FKs on two different tables?

  Table: CustomAttribute
    PK_CustomAttribute {'Id'} PrimaryKey
    EntityTypeMappings:
      CustomAttribute - CustomAttribute
    Columns:
      Id (int) NonNullable)
      ReferenceId (int) NonNullable)
      Type (nvarchar(max)) NonNullable)
      Value (int) NonNullable)
    ForeignKeyConstraints:
      FK_CustomAttribute_ChildEntity_ReferenceId CustomAttribute {'ReferenceId'} -> ChildEntity {'Id'} Cascade
      FK_CustomAttribute_Entity_ReferenceId CustomAttribute {'ReferenceId'} -> Entity {'Id'} Cascade
    Indexes:
      IX_CustomAttribute_ReferenceId {'ReferenceId'}

If so, we'll have to handle this in change tracking, which would be non-trivial.

[AndriySvyryd]

Two FKs on the same property pointing to different tables means that there should be a principal row in each and therefore both Entity and ChildEntity for a given key value will have the same collection of attributes. The behavior described in the first comment is consistent with this, though the database state is not, that data could only have been inserted if the FKs were not enforced.

Having said that, I don't think this behavior is useful, unless the principals are in the same hierarchy using TPT, but in that case only the base type would have the collection. So, in conclusion we should just throw in model validation for this.