1

I'm a relatively new programmer and I'm in the middle of creating Entities for Entity Framework Core for a project I'm currently on, but I'm having difficulty figuring out how to setup 1 specific entity named "Group".

The "Group" entity can contain zero to more groups as parents of it, and it can contain zero to more groups as its children.

i tried writing it the following way with the group class:

    public class Group
    {
        public int GroupID { get; set; }
        
        //Other properties here

        public ICollection<SubGroup>? ParentGroups { get; set; }
        public ICollection<SubGroup>? ChildGroups { get; set; }
    }

with the SubGroup class looking like this:

    public class SubGroup
    {
        public int ParentGroupID { get; set; }
        public int ChildGroupID { get; set; }

        public Group ParentGroup { get; set; }
        public Group ChildGroup { get; set; }
    }

alongside this piece of fluent API:

modelBuilder.Entity<SubGroup>().HasKey(sg => new { sg.ParentGroupID, sg.ChildGroupID });
modelBuilder.Entity<Group>().HasMany(g => g.ParentGroups).WithOne(sg => sg.ParentGroup).HasForeignKey(g => g.ParentGroupID).OnDelete(DeleteBehavior.Restrict);
modelBuilder.Entity<Group>().HasMany(g => g.ChildGroups).WithOne(sg => sg.ChildGroup).HasForeignKey(g => g.ChildGroupID).OnDelete(DeleteBehavior.Restrict);

The thought process in my head is that subgroups should show which groups are children/parents of which groups. While i was able to generate a migration and update the database, i'm not entirely sure if this is how you're supposed to do it, so i've also thought about doing it the following way:

    public class Group
    {
        public int GroupID { get; set; }

        //Other properties here

        public ICollection<Group>? ParentGroups { get; set; }
        public ICollection<Group>? ChildGroups { get; set; }
    }

alongside the following fluent API:

modelBuilder.Entity<Group>().HasMany(g => g.ChildGroups).WithMany(sg => sg.ParentGroups);

i want to ask anyone who's made this kind of relation before if i'm right or wrong in either case, and how it should be written. I hope anyone could help me here Thanks in advance

EDIT: I completely forgot to mention in the first case that the only reason I could update the database there was because I added .OnDelete(DeleteBehavior.Restrict) to the end of the fluent API because it kept warning me with this:

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

I want the group table rows to cascade and delete all subgroup relation rows associated with the deleted group without deleting the other end of the relation but don't know how in this case

  • I'm not sure what is your question. I would use the first approach and it looks correct (at a glance). What went wrong? – heringer Jan 21 '21 at 14:26
  • @heringer i don't know if either case is the way you could make a database with zero or more children of itself and zero or more parents of itself. In the first case, the purpose of the SubGroup class (maybe badly named for it's purpose) is purely to indicate which groups are children/parents of which groups. I am unsure if the first case could even work properly since I had to put the foreign key relation from subgroup to group to restrict to even be able to update the database, but I want the rows in subgroup associated with a deleted group to also be deleted without deleting the other end – Docuventurer Jan 22 '21 at 08:51
  • I tried and it looks ok (EF Core 5). Take a look at [this running example](https://dotnetfiddle.net/4Z7m6Q) at fiddle and tell me if I missed anything. – heringer Jan 22 '21 at 12:33
  • @heringer I tried reenacting your example but I get the error message: ```Introducing FOREIGN KEY constraint 'FK_SubGroup_Group_ParentGroupGroupID' on table 'SubGroup' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Could not create constraint or index. See previous errors.```. Note that the foreign key constraint name is because of the autogenerated name from EF naming conventions since you seem to have not included those in your example and made a new primary key too. The error is still the usual for me – Docuventurer Jan 25 '21 at 09:06
  • @heringer i'm using the nugget Microsoft.EntityFrameworkCore v 5.01 (i'm guessing that's EF Core 5 for v5.xx). The 2 int properties in SubGroup from my first case is supposed to be a composite primary key to keep the relationships unique among the groups too. – Docuventurer Jan 25 '21 at 09:18
  • @heringer Another thing I noticed is that in your example, you use an in-memory database, but i'm trying to get this up on an SQL server through migrations with the Update-Database command – Docuventurer Jan 25 '21 at 09:38
  • Yeah, you are right and it makes sense: when I changed from in-memory to sqlserver, it complained about the potential cycles. I tried to disable one side of the cascade, but then it is just not cascading. I will try again soon. Meanwhile, you get some insight from the official documentation. Take a look at the session ["Database cascade limitations"](https://learn.microsoft.com/en-us/ef/core/saving/cascade-delete#where-cascading-behaviors-happen). One possible workaround would be to deal with this case with code instead of using database cascading, but I hope there is another way. – heringer Jan 25 '21 at 11:51
  • Thank you for the help. i'll see if i'm lucky to find something – Docuventurer Jan 26 '21 at 08:22
  • @heringer After having read the documentation you provided and tried out a few things, I have decided to go with ```DeleteBehavior.ClientCascade``` on the group-subgroup relation. I did some simple deletion unit testing and at first glance, it seems to work with the purpose of my project. Unless you found something better to suggest in the meanwhile, i'll go with this. Once again, thanks for the help. – Docuventurer Feb 05 '21 at 10:05
  • Nice. I haven't use this option yet. It is good to know that you have tried it with success. I will try when I get in a similar case. Thanks for the advice. And I don't have a better idea. – heringer Feb 05 '21 at 12:20

0 Answers0