Efcore: Split query for non-navigation collections

[smitpatel]

C# customer.Select(c => new { c, Orders = c.Orders.Where(o => o.OrderId > 1000) }); customer.Select(c => new { c, OrderDates = c.Orders.Where(o => o.OrderId > 1000).Select(o => o.OrderDate) }); customer.Select(c => new { c, OrderDates = c.Orders.Where(o => o.OrderId > 1000).Select(o => o.OrderDate).Distinct() });

[smitpatel]

Above queries

1. is easy to do since columns are there, it is somewhat similar to Filtered include.
2. requires adding columns in the projection which are not projection in order to form collections from 2nd query.
3. is not possible in single query mode either since the additional columns required to identify different collections cannot be added due to Distinct.

[aahmadi458]

@smitpatel Any updates on this?

[aahmadi458]

@smitpatel seems like a lot of people need this.
23 votes already.
How about you consider planning this for the next release?

[aahmadi458]

@smitpatel Know you guys are busy, but would you answer please? Thanks.

[ajcvickers]

@aahmadi458 This issue is in the Backlog milestone. This means that it is not planned for the next release (EF Core 5.0). We will re-assess the backlog following the this release and consider this item at that time. However, keep in mind that there are many other high priority features with which it will be competing for resources.

[windhandel]

@ajcvickers I'm unclear on exactly what has been completed for this feature based on it currently saying in documentation that this has been partly included in the Preview 6.

I was paying particular attention to this feature because of the Logical Reads going exponential which are causing my larger queries to take minutes. This is caused by the cartesian product from the query generated by default from EF Core.

Could you itemize which of these features are/will be delivered within EF Core v5? i.e. It will work with .Include, .ThenInclude and Projections?

[ajcvickers]

@windhandel I believe the patterns not yet supported are the ones listed in this issue. @smitpatel should be able to confirm.

[smitpatel]

Using collection navs in Include/ThenInclude can be done with split query. Using them in projection is not yet supported. The issue and documentation captures both.