Using EF Core 8, code-first, I have two entities, Student and Guardian. What I want is to have an simple way to differentiate between custodial and non-custodial relationships.
This is my Student class:
public class Student : Entity
{
public int Id { get; set; }
// ... etc
public virtual ICollection<Guardian> CustodialGuardians { get; set; }
public virtual ICollection<Guardian> NonCustodialGuardians { get; set; }
}
And this is the Guardian class:
public class Guardian : Entity
{
public int Id { get; set; }
// ... etc
public virtual ICollection<Student> CustodialGuardianOf { get; set; }
public virtual ICollection<Student> NonCustodialGuardianOf { get; set; }
}
I then created a simple join entity:
public class CustodialStudentGuardian
{
public virtual Guardian ParentGuardian { get; set; } = default!;
public virtual Student Student { get; set; } = default!;
public bool Custodial { get; set; } = default!;
}
And the associated configuration:
modelBuilder.Entity<Student>(e =>
e.HasMany(s => s.CustodialGuardians)
.WithMany(s => s.CustodialGuardianOf)
.UsingEntity<CustodialStudentGuardian>(c =>
{
c.HasOne(c => c.Student).WithOne();
c.HasOne(c => c.ParentGuardian).WithOne();
c.HasDiscriminator(d => d.Custodial).HasValue(true);
});
});
I repeat the above configuration for noncustodial where the value is set to false.
This isn't working, but I think maybe I'm close. I'm not even attached to doing it this way - I'd just like a way to be able to have a distinction between a custodial and non custodial parent.
I'd prefer to have a single property and a query filter or something on the entity, rather than having to add a property for both custodial and non-custodial. Like I could query if parent is custodial rather than asking if the student has any custodial parents and is this parent one of them.
I know that I could get this done with extension methods and/or linq queries, but I was hoping I could get it done by convention using relationships.
I'm still somewhat new to C# and EF Core, so forgive any incorrect terminology.
To have two (or more) relationships between an entity and another you will either need separate linking tables or separate FKs in the linking table, or implement the many-to-many as a single relationship where the differentiation about custodial is done after the fact.
Even though you are essentially using relationships between the three tables, (Student, Guardian, and the single linking table StudentGuardian) the FKs of StudentId and GuardianId in the StudentGuardian Table can only represent ONE relationship. (Custodial or NonCustodial) If you two separate tables for CustodialStudentGuardian and NonCustodialStudentGuardian then this relationship can be enforced in the database. (Otherwise creating Custodial and non-custodial FKs in a single table may work as well)
The main question though would be do you really benefit from separating guardian - student relationships by custodial vs. non-custodial? If you do split then you always need to query across both Custodial and NonCustodial relationships to build a view of guardians for a student or students for a guardian. You cannot easily just see the guardians for a student or students for a guardian, it would always involve union-ing results from the two collections. Personally this doesn't seem like a good idea, I would think to just have a simple many-to-many relationship between students and guardians using the StudentGuardian entity to track which relationships are custodial or not.
Consider the following (Got rid of the base class for clarity):
public class Student {
public int Id {get;set;}
public virtual ICollection<StudentGuardian> StudentGuardians {get; protected set;} = new [];
}
public class Guardian {
public int Id {get;set;}
public virtual ICollection<StudentGuardian> StudentGuardians {get; protected set;} = new [];
}
public class StudentGuardian
{
public int StudentId { get; set; }
public int GuardianId { get; set; }
public bool Custodial {get; set;}
[ForeignKey(nameof(StudentId))]
public virtual Student Student { get; set; } = default!;
[ForeignKey(nameof(GuardianId))]
public virtual Guardian Guardian { get; set; } = default!;
}
You will need a configuration on the StudentGuardian for the composite PK if using StudentId + GuardianId.
Now this sets up the base many-to-many relationship.
modelBuilder.Entity<Student>(e =>
e.HasMany(s => s.StudentGuardians)
.WithOne(s => s.Student)
});
modelBuilder.Entity<Guardian>(e =>
e.HasMany(s => s.StudentGuardians)
.WithOne(s => s.Guardian)
});
Now to get to the desired relationship sub-sets, going back to the entity declarations:
public class Student {
public int Id {get;set;}
public virtual ICollection<StudentGuardian> StudentGuardians {get; protected set;} = new [];
[NotMapped]
public IReadOnlyCollection<Guardian> CustodialGuardians => StudentGuardians.Where(x => x.Custodial)
.Select(x => x.Student)
.ToList()
.AsReadOnly();
[NotMapped]
public IReadOnlyCollection<Guardian> NonCustodialGuardians => StudentGuardians.Where(x => !x.Custodial)
.Select(x => x.Student)
.ToList()
.AsReadOnly();
}
public class Guardian {
public int Id {get;set;}
public virtual ICollection<StudentGuardian> StudentGuardians {get; protected set;} = new [];
[NotMapped]
public IReadOnlyCollection<Student> StudentsCustodial => StudentGuardians.Where(x => x.Custodial)
.Select(x => x.Student)
.ToList()
.AsReadOnly();
[NotMapped]
public IReadOnlyCollection<Student> StudentsNonCustodial => StudentGuardians.Where(x => !x.Custodial)
.Select(x => x.Student)
.ToList()
.AsReadOnly();
}
I mark the targeted collections as ReadOnly to discourage the expectation to use these to do things like add/remove associations. That has to be done from the raw StudentGuardians.
There are a few caveats to this approach. Firstly, to use the Students / Guardians accessors your queries need to eager load (Include) the StudentGuardians, or have lazy loading available. You also cannot use those accessors in query expressions. For instance the following will not work:
var students = Context.Students
.Where(s => s.CustodialGuardians.Any(g => g.Id == guardianId))
.ToList();
... this isn't legal as "CustodialGuardians" isn't available by EF which is why we have to mark them as [NotMapped]. Any such querying has to be done through the many-to-many relationship StudentGuardians:
var students = Context.Students
.Where(s => s.StudentGuardians.Any(g => g.Custodial && g.GuardianId == guardianId))
.ToList();
The [NotMapped] collection helpers are completely optional as you can fairly easily query all guardians for a student for both custodial and non-custodial through the StudentGuardians. (Which is safe for Linq-to-EF without worrying about collections that cannot be used in queries)