Querying many-to-many related data in .NET Core 2.x

.net-core ef-core-2.2 entity-framework-core


These are sample entities:

public class Person
    public int Id { get; set; }
    public bool IsCool { get; set; }
    public List<PersonCommunity> Communities { get; set; }

public class Community
    public int Id { get; set; }
    public bool IsPopular { get; set; }
    public List<PersonCommunity> People { get; set; }

public class PersonCommunity
    public int PersonId { get; set; }
    public Person Person { get; set; }
    public int CommunityId { get; set; }
    public Community Community { get; set; }

This is how they would be configured in ApplicationDbContext:

public DbSet<Person> People { get; set; }

public DbSet<Community> Communities { get; set; }

public DbSet<PersonCommunity> PersonCommunities { get; set; }

protected override void OnModelCreating(ModelBuilder modelBuilder)

        .HasKey(e => new { e.PersonId, e.CommunityId });

        .HasOne(e => e.Person)
        .WithMany(e => e.Communities)
        .HasForeignKey(e => e.PersonId);

        .HasOne(e => e.Community)
        .WithMany(e => e.People)
        .HasForeignKey(e => e.CommunityId);

Now I want to take:

  • Person
  • By given Id


  • This person has to be Cool (IsCool = true)
  • This person has to come from at least one Community which is not popular (IsPopular = false)

In all other cases, the query should return null, even if the person with a given Id exists in the database. So querying just ctx.People.FirstOrDefault(p => p.Id == id) is out of the table.

I want to make it in the most optimised and efficient way, especially without loading any unnecessary data to the program's memory.

Tip: Originally, I have to query more than 2 mln users assigned to over 10k groups with a many-to-many relationship, by certain flags. And I can't just use SQL. It has to be EF Core.

I found plenty of similar questions that solve this problem either partly or either without meeting the efficiency requirement, which is critical here.

2/26/2019 4:55:52 PM

Popular Answer

After reading the whole comment section under the question, I am posting the answer On behalf of Ivan Stoev.

Your query should be as follows:

Person person = _context.Peoples.FirstOrDefault(p => p.Id == id && p.IsCool &&
                                 p.Communities.Any(pc => !pc.Community.IsPopular));
2/26/2019 2:42:24 PM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow