Consider the following two entities*:
public class Parent
{
public int Id { get; set; }
public string Name { get; set; }
public virtual Child PrimaryChild { get; set; }
public virtual Child SecondaryChild { get; set; }
}
public class Child
{
public int Id { get; set; }
public string Name { get; set; }
// Foreign keys to be added here or
// in the other class (that's what I'm trying to figure out)
}
As the code shows, a parent needs to have a primary child and a secondary child; and (preferably) when the parent is deleted, both children should be deleted as well.
Is it even possible to represent such relationship with EF?
If the parent were to have only one child, I could easily use a one-to-one relationship (1 to 0..1) by turning the primary key of Child into both PK and FK. Obviously, that doesn't work with the current situation because, for one, a primary key cannot have multiple values.
This feels like it's a common enough situation that it should have a well-known solution. However, after checking countless posts, I still can't find one. The closest thing I could find is this answer but it doesn't work because:
It creates * to 0..1 relationships.
When I ignored that fact and tried to execute the following code:
var c1 = new Child { Name = "C1" };
var c2 = new Child { Name = "C2" };
context.Parents.Add(new Parent { Name = "P1", PrimaryChild = c1, SecondaryChild = c2 });
...it threw a DbUpdateException with the message:
Unable to determine a valid ordering for dependent operations. Dependencies may exist due to foreign key constraints, model requirements, or store-generated values.
How can this problem be solved?
* This is a simplified example. In the real life scenario, there are relationships between the parent and other child entities with two or more references to each.
I came up with a hacky solution but I'm still hoping for a better one.
Instead of one-to-one relationships, I created a good old one-to-many relationship (Parent has many of Child) with a property in the Child class to store the type (primary/secondary), and created unmapped properties in the Parent class to replace the original navigation properties.
Here's what the code would look like for the example the question:
Child class:
public enum ChildType { Primary, Secondary }
public class Child
{
public int Id { get; set; }
public string Name { get; set; }
public ChildType ChildType { get; set; }
[ForeignKey("Parent")]
public int ParentId { get; set; }
public virtual Parent Parent { get; set; }
}
Parent class:
public class Parent
{
public Parent()
{
Children = new HashSet<Child>();
}
public int Id { get; set; }
public string Name { get; set; }
public virtual ICollection<Child> Children { get; set; }
[NotMapped]
public Child PrimaryChild
{
get
{
return Children.SingleOrDefault(c => c.ChildType == ChildType.Primary);
}
set
{
var existingChild =
Children.SingleOrDefault(c => c.ChildType == ChildType.Primary);
if (existingChild != null) Children.Remove(existingChild);
Children.Add(value);
}
}
[NotMapped]
public Child SecondaryChild
{
get
{
return Children.SingleOrDefault(c => c.ChildType == ChildType.Secondary);
}
set
{
var existingChild =
Children.SingleOrDefault(c => c.ChildType == ChildType.Secondary);
if (existingChild != null) Children.Remove(existingChild);
Children.Add(value);
}
}
}
Usage:
var c1 = new Child { Name = "C1", ChildType = ChildType.Primary };
var c2 = new Child { Name = "C2", ChildType = ChildType.Secondary };
context.Parents.Add(new Parent { Name = "P1", PrimaryChild = c1, SecondaryChild = c2 });
Obviously, this doesn't enforce the minimum or maximum number of children that the parent can have or what ChildType they must be. It's the best I could come up with though.
There are two ways to create such relationships:
The first option very easily restricts a parent to two children and makes it very easy to distinguish between the primary and secondary child.
The second option, you would need to add a type to the child objects if you have the need to distinguish primary from secondary. Using the parent's Id and the type as a unique index would allow you to limit the children to a single primary and a single secondary child per parent.
Now as far as when the parent is deleted, if you have cascading deletes for the relationship it will happen automatically. I know that in second option, the simple one-to-many, that cascading deletes will work as expected. The first option, basically two one-to-one relationships, you can set up cascading deletes, but I would suspect that you'll need to ensure it is a one-way cascade. e.g. if the parent is deleted the children are too, but if a child is deleted the parent should remain.
I would lean towards the children having the foreign keys and a type. So something along these lines:
public class Parent
{
public int Id { get; set; }
public string Name { get; set; }
public virtual ICollection<Child> Children { get; set; }
}
public class Child
{
public int Id { get; set; }
public string Name { get; set; }
[Index("IX_Child_Parent_Type", 1, IsUnique = true)]
public int ParentId { get; set; }
public virtual Parent Parent { get; set; }
[Index("IX_Child_Parent_Type", 2, IsUnique = true)]
public int ChildTypeId { get; set; }
public virtual ChildType Type { get; set; }
}
public class ChildType
{
public int Id { get; set; }
public string Name { get; set; }
}
The other option would look like this:
public class Parent
{
public int Id { get; set; }
public string Name { get; set; }
public virtual Child PrimaryChild { get; set; }
public virtual Child SecondaryChild { get; set; }
}
public class Child
{
public int Id { get; set; }
public string Name { get; set; }
}
Try this method,
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace ConsoleApp2
{
// this class will represent a person, a parent or a child since they have the same attributes
class Person
{
protected int _id;
protected string _name;
public int Id { get => _id; }
public string Name { get => _name; }
// Any object wether it's a parent or a child must be instantiated with those properties
public Person(int id, string name)
{
this._id = id;
this._name = name;
}
}
// this represents a parent which is a persone but have two persons reprenseting it's children. the children can't be instanciated alone.
class Parent : Person
{
private Person _primaryChild;
private Person _secondaryChild;
public Person PrimaryChild { get => _primaryChild; }
public Person SecondaryChild { get => _secondaryChild; }
// this creates the parent with it's children. one the parent dispose its children will be deleted.
public Parent(Person parent, Person primaryChild, Person secondaryChild) : base( parent.Id, parent.Name)
{
// this primaryChild enforce that a parent must have two different children to represents a primary and a secondry child.
if(primaryChild.Id != secondaryChild.Id)
{
this._id = parent.Id;
this._name = parent.Name;
this._primaryChild = primaryChild;
this._secondaryChild = secondaryChild;
}
else
{
throw new Exception("Children must not be tweens.");
}
}
}
class Program
{
static void Main(string[] args)
{
// instanciating a person does'nt represent an actor in the process. A parent must be instianciated.
try
{
// creating a new parent with its related children
var parent = new Parent(new Person(1, "Parent"), new Person(1, "ChildOne"), new Person(2, "ChildTwo"));
// diplaying children names
Console.WriteLine($"Primary Child is {parent.PrimaryChild.Name}. Secondary Child is {parent.SecondaryChild.Name}.");
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
}
}