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)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Entity<PersonCommunity>()
.HasKey(e => new { e.PersonId, e.CommunityId });
modelBuilder.Entity<PersonCommunity>()
.HasOne(e => e.Person)
.WithMany(e => e.Communities)
.HasForeignKey(e => e.PersonId);
modelBuilder.Entity<PersonCommunity>()
.HasOne(e => e.Community)
.WithMany(e => e.People)
.HasForeignKey(e => e.CommunityId);
}
Now I want to take:
But:
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.
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));