Efficiently select all many-to-many pairs

Question

Seems like a simple query to do but the resulting SQL has something like one query per row (3000+ lines long).

I have a many-to-many association with no intermediate entity type. I am using LazyLoading.

Entities are something like this:

public class Entity1 {
   ...
   public virtual ICollection<Entity2> Entity2s { get; } = new List<Entity2>();
}

public class Entity2 {
   ...
   public virtual ICollection<Entity1> Entity1s { get; } = new List<Entity1>();
}

...

entity1Builder
.HasMany(e1 => e1.Entity2s)
.WithMany(e2 => e2.Entity1s);   

And then the query is like:

from e1 in Entity1s
from e2 in e1.Entity2s
select new {
   Entity1 = e1,
   Entity2 = e2
}

Other forms I've tried have the same result:

from e1 in Entity1s
from e2 in Entity2s
where e1.Entity2s.Contains(e2)
select new {
   Entity1 = e1,
   Entity2 = e2
}

Entity1s
.SelectMany(e1 =>
   e1
   .Entity2s
   .Select(e2 => new {
      Entity1 = e1,
      Entity2 = e2
   })
)

Is it not possible to do this in such a way that it does a single join between the two tables (or well, two joins between the three tables, but just once)? Even just Entity1s.SelectMany(e1 => e1.Entity2s) has the same problem of O(n) queries.

Theoretically the SQL should be able to look something like this:

SELECT *
FROM JoinTable jt
INNER JOIN Entity1s e1s ON jt.Entity1ID = e1s.ID
INNER JOIN Entity2s e2s ON jt.Entity2ID = e2s.ID
...

Instead it seems to do an INNER JOIN between Entity1s and Entity2s directly, but then enumerates the results and does a lookup query for every single result. So that's actually O(n x m) queries (size of E1 times size of E2). (Actually it seems like it's doing even more than that, eagerly loading nav properties that I haven't specified.

I've now also tried using an intermediate entity, but it still has the same results.

JoinEntity
.Select(j => new {
   j.Entity1,
   j.Entity2
})

I think what is happening is that it is "eager" loading the entities one by one due to the anonymous projection that uses the entity references. I'm pretty sure in older EF versions you used to be able to project this way and it would populate the entities and properties from the results of a main query, not each one individually. If for example, I just project to fields of the entities, I DO get a nice single query instead:

JoinEntity
.Select(j => new {
   j.Entity1.Property1,
   j.Entity2.Property2
})

but I would like to get whole entities out of this. I'm pretty sure you used to be able to do this, but maybe I'm wrong.

Another variant, same results:

from j in JoinEntity
join e1 in Entity1s on j.Entity1ID equals e1.ID
join e2 in Entity2s on j.Entity2ID equals e2.ID
select new { e1, e2 }

Answer 1

Have you considered using a simple LINQ query that makes use of EF's Include statement.

I would suggest the following:

var e1ande2pairs = await dbContext.Entity1
    .Include(a => a.Entity2s)
    .ThenInclude(a => a.Entity1s)
    .SelectMany(a => a.Entity2s, (entity1, entity2) => new { Entity1 = entity1, Entity2 = entity2 })
    .ToListAsync();

This should result in a single round-trip to the SQL Server and should return the results that you're looking for.