Given a simple model where a parent class has a list of children.

Why is it that when I add two ParentClass items to my database, where both share a common child, that results in only one of them actually keeping the child?

Example: see also on github: https://github.com/LewxX/EntitityFrameworkWithChildCollectionsIssue/blob/main/EntitityFrameworkWithChildCollectionsIssueTests/UnitTest1.cs

// Create DB
using TestDb testDb = new();

// define data
var sharedChild = new TestSubData("Shared", 1);

var entry1 = new TestDataWithListOfChildren("Max1", 
    new List<TestSubData> {
        sharedChild,
        new TestSubData("unique #1",2)
    });

var entry2 = new TestDataWithListOfChildren("Max2",
    new List<TestSubData> {
        sharedChild,
        new TestSubData("unique #2",3)
    });

testDb.Add(entry1);

// just to test and to make sure the data is there in the first place
await testDb.SaveChangesAsync();

var data1 = testDb.TestDataWithListOfChildren
                  .Include(x => x.Children).ToArray();

Assert.That(data1[0].Children.Count, Is.EqualTo(2));    // everything still good

testDb.Add(entry2);

// Save DB
await testDb.SaveChangesAsync();

Read the data and assert:

Assert.That(testDb.TestDataWithListOfChildren.Count(), Is.EqualTo(2)); 
Assert.That(testDb.TestSubData.Count(), Is.EqualTo(3));

// Issue: why is the existing data modified when adding another entry
Assert.That(data1[0].Children.Count, Is.EqualTo(2)); // fails: actual result = 1; why is the shared child gone

var data2 = testDb.TestDataWithListOfChildren
                  .Include(x => x.Children).ToArray();

Assert.That(data2[0].Children.Count, Is.EqualTo(2)); // fails: actual result = 1; why is the shared child gone
Assert.That(data2[1].Children.Count, Is.EqualTo(2));

The data:

record TestDataWithListOfChildren(string Name, IList<TestSubData> Children, long Id = default)
{
    public TestDataWithListOfChildren() : this(default, default) { }
};

record TestDataWithTwoChildren(string Name, TestSubData Child1, TestSubData Child2, long Id = default)
{
    public TestDataWithTwoChildren() : this(default, default, default) { }
};
class TestDb : DbContext
{
    public DbSet<TestDataWithListOfChildren> TestDataWithListOfChildren { get; set; } = default!;
    public DbSet<TestSubData> TestSubData { get; set; } = default!;

    public TestDb() : base(new DbContextOptionsBuilder().UseInMemoryDatabase("testDb", new InMemoryDatabaseRoot()).Options) 
    { }
}

When I do the same but with dedicated child properties (not as a list), then they keep their children (see github example)

CodePudding user response：

This is because EF generates one-to-many relationship between TestDataWithListOfChildren and TestSubData i.e. TestSubData can belong only to one TestDataWithListOfChildren, so if you switch to SQLite you will see that

testDb.Add(entry2);
// Save DB
await testDb.SaveChangesAsync();

Will produce do something like this:

Executed DbCommand (0ms) [Parameters=[@p2='?' (DbType = Int64), @p1='?' (DbType = Int64)], CommandType='Text', CommandTimeout='30']
UPDATE "TestSubData" SET "TestDataWithListOfChildrenId" = @p1
WHERE "Id" = @p2;
SELECT changes();

And TestSubData table is:

CREATE TABLE "TestSubData" (
    "Id" INTEGER NOT NULL CONSTRAINT "PK_TestSubData" PRIMARY KEY AUTOINCREMENT,
    "Name" TEXT NOT NULL,
    "TestDataWithListOfChildrenId" INTEGER NULL,
    CONSTRAINT "FK_TestSubData_TestDataWithListOfChildren_TestDataWithListOfChildrenId" FOREIGN KEY ("TestDataWithListOfChildrenId") REFERENCES "TestDataWithListOfChildren" ("Id")
)

And since both entries are tracked you will see the changes.

You can fix this test by changing the relation to many-2-many. For exmaple:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<TestDataWithListOfChildren>()
        .HasMany(t => t.Children)
        .WithMany(t => t.ParentTestDatas);
} 

record TestSubData(string Name, long Id = default)
{
    // for DB records we need a default constructor without parameters
    public TestSubData() : this(default) { }

    public ICollection<TestDataWithListOfChildren> ParentTestDatas = new List<TestDataWithListOfChildren>();
};