Your second approach relies on the EF navigation property fixup process. The problem is though that every
query.Include(q => q.ItemNavN).Load();
statement will also include all the master record data along with the related entity data.
Using the same basic idea, one potential improvement could be to execute one Load
per each navigation property, replacing the Include
with either Select
(for references) or SelectMany
(for collections) – something similar to how EF Core processes the Include
s internally.
Taking your second approach example, you could try the following and compare the performance:
var query = ctx.Filters.Where(x => x.SessionId == id)
.Join(ctx.Items, i => i.ItemId, fs => fs.Id, (f, fs) => fs);
query.Select(x => x.ItemNav1).Load();
query.Select(x => x.ItemNav2).Load();
query.Select(x => x.ItemNav3).Load();
query.Select(x => x.ItemNav4).Load();
query.Select(x => x.ItemNav5).Load();
query.Select(x => x.ItemNav6).Load();
var result = query.ToList();
// here all the navigation properties should be populated