Click here to Skip to main content
15,901,666 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have run into a circular reference when designing a database to store the data behind cascading drop downs. I am creating an object with properties that depend on each other the relationships seem like they would inevitable result in a circular reference.

For example I want to add a new piece of equipment to the database. The equipment has a type and sub-type. I pick the type then pick a sub-type that is filtered by that type. The new equipment then has a reference to the Type and Sub-Type which also have a relationship. So I end up with a triangular/circular reference (I would include an image but I cannot figure that out).

In this situation do people normally have the Equipment references only the SubType?
That would solve this instance since the Sub-Types only have one Type, but what if it was a many-to-many relationship between Type and Sub-Type?
What if the cascade has more than two choices that depend on each other? (maybe with cars Make, Model, trim, engine)

What I have tried:

Using C# and EF to create my database my (simplified) objects look like:
C#
public class Equipment
{
       [Key]
       public Guid EquipmentId { get; set; }
       public string Description{ get; set; }

       /***** Relationship and Navigation *****/
       public Guid EquipmentTypeId { get; set; }
       public EquipmentType EquiptmentType { get; set; }
       public Guid EquipmentSubTypeId { get; set; }
       public EquipmentSubType EquipmentSubType { get; set; }
}

public class EquipmentType
{
        [Key]
        public Guid EquipmentTypeId { get; set; }
        public string Description{ get; set; }

        /***** Relationship and Navigation *****/
        public ICollection Equipment { get; set; }
        public ICollection EquipmentSubType { get; set; }
}

public class EquipmentSubType
{
        [Key]
        public Guid EquipmentSubTypeId { get; set; }
        public string Description{ get; set; }

        /***** Relationship and Navigation *****/
        public Guid EquipmentTypeId { get; set; }
        public EquipmentType EquipmentType { get; set; }
        public ICollection Equipment { get; set; }
}
Posted
Updated 20-Dec-16 8:25am
v4
Comments
ZurdoDev 20-Dec-16 13:34pm    
I'm not really sure what you are asking. There is nothing wrong with doing self referencing in a database or in code. You just have to decide what data you need to store and then store it.

1 solution

In this specific example, since a sub-type always has a single type, and a piece of equipment always has a sub-type, then it would make sense to omit the relationship between equipment and type.

If the sub-type was optional, or there was a many-to-many relationship between sub-type and type, then you obviously need to keep the equipment/type relationship.

The only problem you'll run into is if you try to enable cascading delete on all three relationships. SQL will refuse to create the foreign key constraint, telling you that this would cause cycles or multiple cascade paths. To resolve this, all you need to do is disable cascading delete on the equipment/type relationship.

Entity Framework Fluent API - Relationships - Enabling Cascade Delete[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900