Foreign key from root parent not set in child entity nested at third level.

[shashank2490]

Bug

When trying to set foreign key in child entity at third level in nested objects, the Root parent foreign key is not set there. Error thrown by system is "FOREIGN KEY constraint failed" . But when I add the third level child as collection in root (commented code), it works fine. So the question now is, is this the only way to do it, or there is some better way?. Ideally the way should have been the one which is not working.

Code

using Microsoft.EntityFrameworkCore;
using System.ComponentModel.DataAnnotations.Schema;

public class Parent
{
    public int Id { get; set; }
    public string Name { get; set; }
    public ICollection<Child> Childs { get; set; }
    public ICollection<ChildLog> ChildLogs { get; set; }
}

public class Child
{
    public int Id { get; set; }
    public string Name { get; set; }
    [ForeignKey("Parent")]
    public int ParentId { get; set; }
    public ICollection<ChildLog> Logs { get; set; }
    public Parent Parent { get; set; }
}

public class ChildLog
{
    public int Id { get; set; }
    public DateTime On { get; set; }
    public string Note { get; set; }
    [ForeignKey("Child")]
    public int ChildId { get; set; }
    public string Name { get; set; }
    [ForeignKey("Parent")]
    public int ParentId { get; set; }
    public Child Child { get; set; }
    public Parent Parent { get; set; }
}
public class DemoDBContext : DbContext
{
    public DbSet<Parent> Parent { get; set; }
    public DbSet<Child> Child { get; set; }
    public DbSet<ChildLog> ChildLog { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .UseSqlite("Data Source=DemoDb.db");
    }
}
internal class Program
{
    static void Main(string[] args)
    {
        using (var context = new DemoDBContext())
        {
            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            ChildLog childLog = new ChildLog
            {
                Name = "Child Log",
                Note = "Note"
            };

            Child child = new Child
            {
                Name = "Child",
                Logs = new List<ChildLog>
                {
                    childLog
                }
            };

            Parent parent = new Parent
            {
                Name = "parent",
                Childs = new List<Child>
                {
                    child
                },
                //Uncomment below lines to make this work
                //ChildLogs = new List<ChildLog>
                //{
                //    childLog
                //}
            };


            context.Parent.Add(parent);
            context.SaveChanges();
        }

    }
}

Stack traces

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.
 ---> Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 19: 'FOREIGN KEY constraint failed'.
   at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)
   --- End of inner exception stack trace ---
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(IEnumerable`1 commandBatches, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Storage.RelationalDatabase.SaveChanges(IList`1 entries)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IList`1 entriesToSave)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(StateManager stateManager, Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.<>c.<SaveChanges>b__104_0(DbContext _, ValueTuple`2 t)
   at Microsoft.EntityFrameworkCore.Storage.NonRetryingExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChanges()
   at Program.Main(String[] args) in ~\EF Parent Child Demo\EF Parent Child Demo\Program.cs:line 89

Include verbose output

The change tracker shows the following output

Child {Id: -2147482647} Added FK {ParentId: -2147482647}
ChildLog {Id: -2147482647} Added FK {ChildId: -2147482647} FK {ParentId: 0}
Parent {Id: -2147482647} Added

Include provider and version information

EF Core version: 6.0.8
Database provider: (e.g. Microsoft.EntityFrameworkCore.SqlServer)SQLLite
Target framework: (e.g. .NET 5.0) .NET 6.0
Operating system: Windows 10
IDE: (e.g. Visual Studio 2019 16.3) Visual Studio Professional 2022 (64-bit) - Version 17.1.3

Question

So the question now is, is this the only way to do it, or there is some better way?. Ideally the way should have been the one which is not working.

[ajcvickers]

The relationship between ChildLog and Parent is not being set to anything. There is nothing that inherently couples the relationship between ChildLog and Parent and the relationship between Child and Parent; they independent of each other.

[shashank2490]

Sorry I may sound novice but I did not get what do you mean when you say "Relationship is not being set to anything". I have set Parent as foreign key in both Child and ChildLog. Further I have defined the navigation properties. Even when i define 1:many realtionships like this

protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Child>()
            .HasOne(a => a.Parent).WithMany(a => a.Childs);

        modelBuilder.Entity<ChildLog>()
            .HasOne(a => a.Parent).WithMany(a => a.ChildLogs);

        modelBuilder.Entity<ChildLog>()
            .HasOne(a => a.Child).WithMany(a => a.Logs);
    }

it does not work.
And if they really are independent of each other, then how come Child has Foreign Key id properly set (see changetracker log) and how this all thing workks when I add ChildLog's in Parent object (commented piece of code)?

[ajcvickers]

These are the relationships in the model:

1. One Parent to many Child; FK is Child.ParentId; Navigations are Parent.Childs and Child.Parent
2. One Parent to many ChildLog; FK is ChildLog.ParentId; Navigations are Parent.ChildLogs and ChildLog.Parent
3. One Child to many ChildLog; FK is Child.ChildId; Navigations are Child.Logs and ChildLog.Child

This:

Child child = new Child
{
    Name = "Child",
    Logs = new List<ChildLog>
    {
        childLog
    }
};

relates a Child to a ChildLog using relationship 3. above.

This:

Parent parent = new Parent
{
    Name = "parent",
    Childs = new List<Child>
    {
        child
    },
};

relates a Parent to a Child using relationship 1. above.

Without uncommenting the code, nothing uses relationship 2. to relate a Parent to a ChildLog.

[shashank2490]

So now I have two part questions -
Can i say that if i want to add data in Aggregate kind of model, I have to use the commented code approach, or is there a better way for doing that?

If not, can we not have this feature of adding data in drill down kind of way, beacuse this approach seems not fit. We have to structure the data out of heirarchy for navigations. For e.g. if data is coming through API , we have to structure that in this way to create it?

[ajcvickers]

@shashank2490 A typical aggregate structure has references from the parent to its children, and these children to their subchildren, and so on. For example:

public class Parent
{
    public int Id { get; set; }
    public ICollection<Level1> Level1s { get; set; }
}

public class Level1
{
    public int Id { get; set; }
    public int ParentId { get; set; }
    public Parent Parent { get; set; }
    public ICollection<Level2> Level2s { get; set; }
}

public class Level2
{
    public int Id { get; set; }
    public int Level1Id { get; set; }
    public Level1 Level1 { get; set; }
}

Or:

public class Parent
{
    public int Id { get; set; }
    public ICollection<Level1> Level1s { get; set; }
}

public class Level1
{
    public int Id { get; set; }
    public int ParentId { get; set; }
    public Parent Parent { get; set; }
    public int Level2Id { get; set; }
    public Level2 Level2 { get; set; }
}

public class Level2
{
    public int Id { get; set; }
    public ICollection<Level1> Level1s { get; set; }
}

It's not common to have references directly between the parent and the subchilden--e.g. between Parent and Level2 here. You can, of course, do so, but then the semantics of that relationship will rarely be coupled with the semantics of the relationships between the parent and the first level, or the fist level and the second level. Typically if you want to navigate from Parent to Level2, then that would be Parent.Level1.Level2.

[smitpatel]

Duplicate of #21673

This is Skip navigation traversing collection. Unlikely that we will support it (any time soon).