How to define a EF 6 Parent Child Relation with variable?

Question

I try to setup Entity Framework to define a parent child relation with variable for the relation. An Item is composed by a ratio of other Items. How can I design this with Entity Framework 6.

Lets say my Parent item id a pound cake and my Child are Egg, Flour, Sugar and Butter. For pound cake the ratio of each Child is 1. Let's keep this simple.

    public class Item
    {
        public Guid Id { get; set; }
        public string Name { get; set; }
    }

    public class Composition
    {
        public Guid Id { get; set; }

        public Guid ParentId { get; set; } // Id of Parent Item

        public Guid ChildId { get; set; } //  Id of Child Item

        public int Ratio { get; set; } // Number of Child that compose the parent.
    }

My initial DbContext is

    public class TestContext : DbContext
    {
        public DbSet<Item> Items { get; set; }
    }

On migration creation with Add-Migration Initial. The system generate this migration code:

        public override void Up()
        {
            CreateTable(
                "dbo.Compositions",
                c => new
                    {
                        Id = c.Guid(nullable: false),
                        ParentId = c.Guid(nullable: false),
                        ChildId = c.Guid(nullable: false),
                        Ratio = c.Int(nullable: false),
                    })
                .PrimaryKey(t => t.Id);

            CreateTable(
                "dbo.Items",
                c => new
                    {
                        Id = c.Guid(nullable: false),
                        Name = c.String(),
                    })
                .PrimaryKey(t => t.Id);

        }

The migration created my 2 entities but they have no relation. So I cannot navigate easily from my item table to all my compositions. I use Linpad and LinqPad do not create the navigation property. This is normal, I never say to my POCO taht ParentId and ChildId must be Id existing into my Item table.

To create the constraint in my database and be able to navigate I think I must add the navigation property and link it to foreign key. I will try several solution here and comment.

Solution 1 - Just add navigation properties

    public class Composition
    {
        public Guid Id { get; set; }

        public Guid ParentId { get; set; } // Id of Parent Item

        [ForeignKey(nameof(ParentId))]
        public Item Parent { get; set; }

        public Guid ChildId { get; set; } //  Id of Child Item

        [ForeignKey(nameof(ChildId))]
        public Item Child { get; set; }

        public int Ratio { get; set; } // Number of Child that compose the parent.
    }

On migration creation with Add-Migration Initial. The system generate this migration code:

        public override void Up()
        {
            CreateTable(
                "dbo.Compositions",
                c => new
                    {
                        Id = c.Guid(nullable: false),
                        ParentId = c.Guid(nullable: false),
                        ChildId = c.Guid(nullable: false),
                        Ratio = c.Int(nullable: false),
                    })
                .PrimaryKey(t => t.Id)
                .ForeignKey("dbo.Items", t => t.ChildId, cascadeDelete: true)
                .ForeignKey("dbo.Items", t => t.ParentId, cascadeDelete: true)
                .Index(t => t.ParentId)
                .Index(t => t.ChildId);

            CreateTable(
                "dbo.Items",
                c => new
                    {
                        Id = c.Guid(nullable: false),
                        Name = c.String(),
                    })
                .PrimaryKey(t => t.Id);

        }

But why the cascadeDelete to true? I don't want to delete all my tree on each item deletion. I know I can define Child or ChildId nullable and Parent or ParentId nullable but I cannot accept that. In my Composition table Parent and Child cannot be null. This is not logic I create a link with a value on one side and null on the other side. I can delete the composition link but if I create it then both side must exist.

Also, I cannot Udpate-Database this version because

Introducing FOREIGN KEY constraint 'FK_dbo.Compositions_dbo.Items_ParentId' on table 'Compositions' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

Solution 2 - Using Fluent API

I take my solution 1 and I try to add missing information to help the system in my OnModelCreating() override

    public class TestContext : DbContext
    {
        public DbSet<Item> Items { get; set; }
        public DbSet<Composition> Compositions { get; set; }

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

            modelBuilder.Entity<Composition>()
                .HasRequired(c => c.Parent)
                // And I have no idea how to do here
        }
    }

Solution 3 - Composition collection on Item

    public class Item
    {
        public Guid Id { get; set; }

        public string Name { get; set; }

        public ICollection<Composition> Compositions { get; set; }
    }

    public class Composition
    {
        public Guid Id { get; set; }

        public Guid ParentId { get; set; } // Id of Parent Item

        //[ForeignKey(nameof(ParentId))]
        //public Item Parent { get; set; }

        public Guid ChildId { get; set; } //  Id of Child Item

        [ForeignKey(nameof(ChildId))]
        public Item Child { get; set; }

        public int Ratio { get; set; } // Number of Child that compose the parent.
    }

On migration creation with Add-Migration Initial. The system generate this migration code:

public override void Up()
{
    CreateTable(
        "dbo.Compositions",
        c => new
            {
                Id = c.Guid(nullable: false),
                ParentId = c.Guid(nullable: false),
                ChildId = c.Guid(nullable: false),
                Ratio = c.Int(nullable: false),
            })
        .PrimaryKey(t => t.Id)
        .ForeignKey("dbo.Items", t => t.ChildId, cascadeDelete: true)
        .Index(t => t.ChildId);

    CreateTable(
        "dbo.Items",
        c => new
            {
                Id = c.Guid(nullable: false),
                Name = c.String(),
            })
        .PrimaryKey(t => t.Id);

}

Seems better but still I have a cascade delete to true.

Solution 4 - bring parent and child compositions on Item

public class Item
{
    public Guid Id { get; set; }

    public string Name { get; set; }

    public ICollection<Composition> ChildCompositions { get; set; }

    public ICollection<Composition> ParentCompositions { get; set; }
}

public class Composition
{
    public Guid Id { get; set; }

    public Guid ParentId { get; set; } // Id of Parent Item

    [ForeignKey(nameof(ParentId))]
    public Item Parent { get; set; }

    public Guid ChildId { get; set; } //  Id of Child Item

    [ForeignKey(nameof(ChildId))]
    public Item Child { get; set; }

    public int Ratio { get; set; } // Number of Child that compose the parent.
}

The migration script is then

        public override void Up()
        {
            CreateTable(
                "dbo.Compositions",
                c => new
                    {
                        Id = c.Guid(nullable: false),
                        ParentId = c.Guid(nullable: false),
                        ChildId = c.Guid(nullable: false),
                        Ratio = c.Int(nullable: false),
                        Item_Id = c.Guid(),
                        Item_Id1 = c.Guid(),
                    })
                .PrimaryKey(t => t.Id)
                .ForeignKey("dbo.Items", t => t.Item_Id)
                .ForeignKey("dbo.Items", t => t.Item_Id1)
                .ForeignKey("dbo.Items", t => t.ChildId, cascadeDelete: true)
                .ForeignKey("dbo.Items", t => t.ParentId, cascadeDelete: true)
                .Index(t => t.ParentId)
                .Index(t => t.ChildId)
                .Index(t => t.Item_Id)
                .Index(t => t.Item_Id1);

            CreateTable(
                "dbo.Items",
                c => new
                    {
                        Id = c.Guid(nullable: false),
                        Name = c.String(),
                    })
                .PrimaryKey(t => t.Id);

        }

Logic. The systen cannot know my named ParentComposition collection must be linked to ParentId foreign key and the ChildComposition collection must be linked to the ChildId foreign key? So the system create new foreign keys.

On Update-Database I get error

Introducing FOREIGN KEY constraint 'FK_dbo.Compositions_dbo.Items_ParentId' on table 'Compositions' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

Solution 5 - Back again to Fluent API

But I can navigate through properties now so maybe I will find what I'm looking for:

public class Item
    {
        public Guid Id { get; set; }

        public string Name { get; set; }

        public ICollection<Composition> ParentCompositions { get; set; }

        public ICollection<Composition> ChildCompositions { get; set; }
    }

    public class Composition
    {
        public Guid Id { get; set; }

        public Guid ParentId { get; set; } // Id of Parent Item

        [ForeignKey(nameof(ParentId))]
        public Item Parent { get; set; }

        public Guid ChildId { get; set; } //  Id of Child Item

        [ForeignKey(nameof(ChildId))]
        public Item Child { get; set; }

        public int Ratio { get; set; } // Number of Child that compose the parent.
    }

    public class TestContext : DbContext
    {
        public DbSet<Item> Items { get; set; }
        public DbSet<Composition> Compositions { get; set; }

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

            modelBuilder.Entity<Item>()
                .HasMany(c => c.ChildCompositions)
                .WithRequired(c => c.Parent)
                //.HasForeignKey(c => c.ParentId)
                .WillCascadeOnDelete(false);

            modelBuilder.Entity<Item>()
                .HasMany(c => c.ParentCompositions)
                .WithRequired(c => c.Child)
                //.HasForeignKey(c => c.ChildId)
                .WillCascadeOnDelete(false);
        }
    }

Gives me the migration script:

        public override void Up()
        {
            CreateTable(
                "dbo.Compositions",
                c => new
                    {
                        Id = c.Guid(nullable: false),
                        ParentId = c.Guid(nullable: false),
                        ChildId = c.Guid(nullable: false),
                        Ratio = c.Int(nullable: false),
                    })
                .PrimaryKey(t => t.Id)
                .ForeignKey("dbo.Items", t => t.ParentId)
                .ForeignKey("dbo.Items", t => t.ChildId)
                .Index(t => t.ParentId)
                .Index(t => t.ChildId);

            CreateTable(
                "dbo.Items",
                c => new
                    {
                        Id = c.Guid(nullable: false),
                        Name = c.String(),
                    })
                .PrimaryKey(t => t.Id);

        }

And I can update my database with this one. But, wierd, when I test he result in LinqPad it seems there is a inversion between ChildCompositions list and ParentCompositions list.

I tried to change that in my Fluent API without success.

Could you post a snippet from the migration script? - I guess you could adapt in manually. The delete cascade is not controlled by data annotations but only by the fluent API call OnDelete(), where the behavior can be set. — lzydrmr

lzydrmr lzydrmr · Accepted Answer · 2020-03-20T09:58:26

A solution could be to remove the ForeignKey attributes but instead adding the relationship through the model builder:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Composition>().HasOne<Item>(x => x.ChildItem).WithMany().OnDelete(DeleteBehavior.Restrict);
    modelBuilder.Entity<Composition>().HasOne<Item>(x => x.ParentItem).WithMany().OnDelete(DeleteBehavior.Restrict);
}

Thereby, you can explicitly set the delete cascade behavior.

I tried it by adding a Composition entity and two Items, and then deleting the Composition entity. The two Items are remaining in the database. I think that's the way it is intended to be.

How to define a EF 6 Parent Child Relation with variable?

1 Answers