Jiveman Jiveman - 1 year ago 74
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)

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

Rob Rob
Answer Source

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));
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download