SQL Server one-to-many relationship issuing one query per main table record when projecting

[javiercampos]

I understand there's no generic way to do this, but I'm thinking of ways to optimize this, because the current way doesn't look very well done. Not sure if I'm missing something.

Steps to reproduce

Create a couple entities with a one-to-many relationship between them:

class Product {
   long Id { get; set;}
   ICollection<Supplier> { get; set;}
}
class Supplier {
  long Id { get; set;}
  long ProductId {get;set;}
  Product Product {get;set;}
}

And now make a query projecting the entity to a flattened one:

context.Products
    .Include(p => p.Suppliers)
    .Select(p => new { Id = p.Id, SupplierIds = p.Suppliers.Select(s => s.Id) } )
    .ToList();

EF will make one query for the products, like:

SELECT [x].[Id]
FROM [dbo].[Products] AS [x]

And then, for each product retrieved will issue a query like this:

exec sp_executesql N'SELECT [s].[Id]
FROM [dbo].[Suppliers] AS [s]
WHERE @_outer_Id = [s].[ProductId]',N'@_outer_Id bigint',@_outer_Id=1

(replacing 1 with each outer id)

In my sample, if you have 10.000 products, this will issue 10.001 queries (and doing 10.001 roundtrips is being very slow)

I understand the alternative for doing it on a single query would be doing a JOIN which would duplicate the rows if there are many suppliers and then discard the duplicated data, and that, as a general case would be wrong.

But I'm wondering: wouldn't it be possible to do something like ("one" table being Products and "many" table being Suppliers in the example):

SELECT <projected_fields>, <fk_field> 
FROM <many_table> 
WHERE <fk_field> IN <all_keys_retrieved_from_the_one_table>

And have it done in a single extra query instead of one per main record?

Is there anything I'm missing that led to this design decision?

I'm contemplating doing direct SQL queries for this since the EF way of doing it is proving really slow, but wondering if this could change in the future (in which case I may stick with EF for these queries and hope for the best) or it's something by design (in which case I should contemplate doing these kind of queries directly)

PS: the sample code has been hand-crafted in the editor, and may contain mistakes

Further technical details

EF Core version: 2.0.1
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Win10
IDE: Visual Studio 2017 15.5.4

[ajcvickers]

@javiercampos Issue #9282 fixes this and will be released as part of EF Core 2.1, although a ToList or equivalent will be needed to tell EF that this sub-query should not be streamed:

context.Products
    .Include(p => p.Suppliers)
    .Select(p => new { Id = p.Id, SupplierIds = p.Suppliers.Select(s => s.Id).ToList() } )
    .ToList();