Jiveman Jiveman - 2 months ago 11
C# Question

How to write an efficient LINQ query for a self-referential many-to-many relationship?

I have the following model, representing a self-referential many-to-many relationship between Person and "Assistant" (which is just another Person):

public class Person
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public virtual ICollection<Person> Assistants { get; set; }
public virtual ICollection<Person> AssistantTo { get; set;
}


But I'm having trouble wrapping my mind around how to query for a specific assistant efficiently with LINQ. I know I can do something like this:

public Person GetAssistant(int assistedPersonId, int assistantId)
{
var assistedPerson = _context.People.Where(p => p.Id == assistedPersonId)
.Include(a => a.Assistants)
.FirstOrDefault();

return assistedPerson.Assistants.FirstOrDefault(a => a.Id == assistantId);
}


How would I achieve that with one LINQ call? In pseudo-code, I'm looking for something like:

public Person GetAssistant(int assistedPersonId, int assistantId)
{
return = _context.People.Where(
PERSONID == assistedPersonId AND
PERSON HAS AN ASSISTANT WITH assistantId)
;
}

Rob Rob
Answer

Since an assistant is also a person, we can load it directly without having to get it via the AssistantTo row.

The query simply becomes:

_context.People.FirstOrDefault(p => p.Id == assistantId);

However, since we're also including business logic (that we want to make sure this assistant is actually an assistant to the correct person) - we can restrict the query:

_context.People.FirstOrDefault(p => p.Id == assistantId && 
                        p.AssistantTo.Any(pa => pa.Id == assistedPersonId));