I’m trying to create a new function in an existing project to retrieve data from the database, but I’m seeing inconsistent results compared to one of the existing functions and I’m not sure what I’ve done differently.
I’ve renamed the classes but tried to keep the same spirit of the relations.
Existing classes
public class Person
{
public int Id;
public string Name;
public int? ParentId;
public virtual Person Parent;
}
public class Family
{
public int Id;
public string Name;
public virtual ICollection<FamilyPerson> FamilyMembers;
}
public class FamilyPerson
{
public int Id;
public int FamilyId;
public virtual Family Family;
public int PersonId;
public virtual Person Person;
}
public class CoursePerson
{
public int Id;
public int CourseId;
public virtual Course Course;
public int PersonId;
public virtual Person Person;
}
The database contains a table for each of the above classes.
The database contains a table Persons which has multiple persons. When a Person gets added to the database, if they have a parent, they get created with the link to their parent, who can have a link to their parent, going back three or four generations or potentially more.
A Family contains a collection of FamilyPerson which connects one family to multiple Persons.
I tried to create a get request to get the Family from the database, including the FamilyMembers collection and including each Person, along with their Parents, Parent of Parent and so on until reaching the Person who has no parent.
Family family = await _context.Familys
.Include(family => family.FamilyMembers)
.ThenInclude(familyMember => familyMember.Person)
.ThenInclude(person => person.Parent)
.Where(family => family.Name == familyName)
.FirstOrDefaultAsync();
When I use this request, I am able to get the data for the Family, FamilyMembers, Person and Parent of person, but not the parent of the parent when they do exist.
I could copy paste
.ThenInclude(person => person.Parent)
for as many times as I expect the generations to go back, but I’m looking for a more dynamic solution.
There is an existing service call for Courses that retrieves a list of CoursePerson relations that is similar.
List<CoursePerson> coursePersons = await _context.CoursePersons
.Include(coursePerson => coursePerson.Person)
.ThenInclude(person => person.Parent)
.Where(coursePerson => coursePerson.CourseId == courseId).ToListAsync();
return coursePersons;
and this call will include all of the parent generations until reaching the top of the tree.
Both requests have lazy loading disabled, I tried using lazy loading but it would retrieve too much data and slowed the service down.
I’m not sure why my new request only includes parents explicitly asked for, whereas the existing service retrieves all generations
user26707598 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1
I can suggest defining conveniency method to easily include arbitrary level of ancestry:
public static class LinqExtensions
{
public static IQueryable<TEntity> IncludeAncestryLevel<TEntity>(
this IIncludableQueryable<TEntity, Person> query,
int ancestryLevel)
where TEntity : class
{
for (int i = 0; i < ancestryLevel; i++)
{
query = query.ThenInclude(x => x.Parent);
}
return query.AsQueryable();
}
}
Then the usage is like this:
List<CoursePerson> coursePersons = await _context.CoursePersons
.Include(coursePerson => coursePerson.Person)
// Here we are fetching one level of ancestry. You can use greater
// number to fetch grandparents, etc.
.IncludeAncestryLevel(1)
.Where(coursePerson => coursePerson.CourseId == courseId)
.ToListAsync();