EF Core - many queries sent to database for subquery

Question

Using EF Core 2.2.2, I have a table in my database which is used to store notes for many other tables. In other words, it's sortof like a detail table in a master-detail relationship, but with multiple master tables. Consider this simplified EF Model:

public class Person
{
  public Guid PersonID { get; set; }
  public string Name { set; set; }
}

public class InvoiceItem
{
  public Guid InvoiceItemID { get; set; }
  public Guid InvoiceID { get; set; }
  public string Description { get; set; }
}

public class Invoice
{
  public Guid InvoiceID { get; set; }
  public int InvoiceNumber { get; set; }

  public List<Item> Items { get; set; }
}

public class Notes
{
  public Guid NoteID { get; set; }
  public Guid NoteParentID { get; set; }
  public DateTime NoteDate { get; set; }
  public string Note { get; set; }
}

In this case, Notes can store Person notes or Invoice notes (or InvoiceItem notes, though let's just say that the UI doesn't support that).

I have query methods set up like this:

public IQueryable<PersonDTO> GetPersonQuery()
{
  return from p in Context.People
             select new PersonDTO
             {
               PersonID = p.PersonID,
               Name = p.Name
             };
}

public List<PersonDTO> GetPeople()
{
  return (from p in GetPersonQuery()
              return p).ToList();
}

public IQueryable<InvoiceDTO> GetInvoiceQuery()
{
  return from p in Context.Invoices
             select new InvoiceDTO
             {
               InvoiceID = p.InvoiceID,
               InvoiceNumber = p.InvoiceNumber
             };
}

public List<InvoiceDTO> GetInvoices()
{
  return (from i in GetInvoiceQuery()
              return i).ToList();
}

These all work as expected. Now, let's say I add InvoiceItems to the Invoice query, like this:

public IQueryable<InvoiceDTO> GetInvoiceQuery()
{
  return from p in Context.Invoices
             select new InvoiceDTO
             {
               InvoiceID = p.InvoiceID,
               InvoiceNumber = p.InvoiceNumber,
               Items = (from ii in p.Items
                             select new ItemDTO
                             {
                               ItemID = ii.ItemID,
                               Description = ii.Description
                             }).ToList()
             };
}

That also works great, and issues just a couple queries. However, the following:

public IQueryable<InvoiceDTO> GetInvoiceQuery()
{
  return from p in Context.Invoices
             select new InvoiceDTO
             {
               InvoiceID = p.InvoiceID,
               InvoiceNumber = p.InvoiceNumber,
               Items = (from ii in p.Items
                             select new ItemDTO
                             {
                               ItemID = ii.ItemID,
                               Description = ii.Description
                             }).ToList(),
              Notes = (from n in Context.Notes
                             where i.InvoiceID = n.NoteParentID
                             select new NoteDTO
                             {
                               NoteID = n.NoteID,
                               Note = n.Note
                             }).ToList(),
             };
}

sends a separate query to the Note table for each Invoice row in the Invoice table. So, if there are 1,000 invoices in the Invoice table, this is sending something like 1,001 queries to the database.

It appears that the Items subquery does not have the same issue because there is an explicit relationship between Invoices and Items, whereas there isn't a specific relationship between Invoices and Notes (because not all notes are related to invoices).

Is there a way to rewrite that final query, such that it will not send a separate note query for every invoice in the table?

Answer 1

The problem is indeed the correlated subquery versus collection navigation property. EF Core query translator still has issues processing such subqueries, which are in fact logical collection navigation properties and should have been processed in a similar fashion.

Interestingly, simulating collection navigation property with intermediate projection (let operator in LINQ query syntax) seems to fix the issue:

var query =
    from i in Context.Invoices
    let i_Notes = Context.Notes.Where(n => i.InvoiceID == n.NoteParentID) // <--
    select new InvoiceDTO
    {
        InvoiceID = i.InvoiceID,
        InvoiceNumber = i.InvoiceNumber,
        Items = (from ii in i.Items
                 select new ItemDTO
                 {
                     ItemID = ii.ItemID,
                     Description = ii.Description
                 }).ToList(),
        Notes = (from n in i_Notes // <--
                 select new NoteDTO
                 {
                     NoteID = n.NoteID,
                     Note = n.Note
                 }).ToList(),
    };