C#•2y ago

❔ Querying Many to Many EF Relationships

What is the best way to query Many to Many relationships? I'm using Ardalis.Specification package and this is my current workaround to get a Job's list of Contacts. It feels like there is something simpler staring at me 🤣
Content: I'm using JobContact in either side of this relationship.

public sealed class GetJobContactsSpecification  : Specification<Job, List<Contact>>
{
    public GetJobContactsSpecification(Guid jobId, string userId)
    {
        Query
            .Select(x => x.Contacts.ToList())
            .Include(x => x.Contacts)
              .ThenInclude(x => x.Companies)
            .Include(x => x.Contacts)
              .ThenInclude(x => x.Phones)
            .Include(x => x.Contacts)
              .ThenInclude(x => x.Emails)
            .AsSplitQuery()
            .AsNoTracking()
            .Where(x => x.Id == jobId && x.OwnerId == userId);
    }
}

public sealed class GetJobContactsSpecification  : Specification<Job, List<Contact>>
{
    public GetJobContactsSpecification(Guid jobId, string userId)
    {
        Query
            .Select(x => x.Contacts.ToList())
            .Include(x => x.Contacts)
              .ThenInclude(x => x.Companies)
            .Include(x => x.Contacts)
              .ThenInclude(x => x.Phones)
            .Include(x => x.Contacts)
              .ThenInclude(x => x.Emails)
            .AsSplitQuery()
            .AsNoTracking()
            .Where(x => x.Id == jobId && x.OwnerId == userId);
    }
}

public class Job : Entity
{
    // Database Relationship Properties
    public List<JobContact> JobContacts { get; set; }

    // Rest of properties....
}

public class Job : Entity
{
    // Database Relationship Properties
    public List<JobContact> JobContacts { get; set; }

    // Rest of properties....
}

public class Contact : Entity
{
    // Database Relationship Properties
    public List<JobContact> JobContacts { get; set; }

    // Rest of properties....
    
}

public class Contact : Entity
{
    // Database Relationship Properties
    public List<JobContact> JobContacts { get; set; }

    // Rest of properties....
    
}

public class JobContact
{
    public Guid JobId { get; set; }
    public Job Job { get; set; }

    public Guid ContactId { get; set; }
    public Contact Contact { get; set; }

}

public class JobContact
{
    public Guid JobId { get; set; }
    public Job Job { get; set; }

    public Guid ContactId { get; set; }
    public Contact Contact { get; set; }

}

2 Replies

NickolakiOP•2y ago

UPDATE: I've gone with this:

public sealed class GetJobContactsSpecification  : Specification<Contact>
{
    public GetJobContactsSpecification(Guid jobId, string userId)
    {
        Query
            .Include(x => x.Companies)
            .Include(x => x.Phones)
            .Include(x => x.Emails)
            .AsSplitQuery()    
            .AsNoTracking()
            .Where(contact=> contact.Jobs.Any(job => job.Id == jobId && job.OwnerId == userId));
    }
}

public sealed class GetJobContactsSpecification  : Specification<Contact>
{
    public GetJobContactsSpecification(Guid jobId, string userId)
    {
        Query
            .Include(x => x.Companies)
            .Include(x => x.Phones)
            .Include(x => x.Emails)
            .AsSplitQuery()    
            .AsNoTracking()
            .Where(contact=> contact.Jobs.Any(job => job.Id == jobId && job.OwnerId == userId));
    }
}

However, I've previously had performance issues with using .Any() 😭

Accord•2y ago

Looks like nothing has happened here. I will mark this as stale and this post will be archived until there is new activity.

Gaming

Programming

❔ Querying Many to Many EF Relationships