Split query for non-navigation collections

[smitpatel]

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.

[groege]

I also have the issue having developed with the preview for a while where this worked:

db.MyEntity.Select(x => new MyModel { MyParam = x.MyOtherEntity.Select(y => y.MyString).Distinct().ToList()).ToListAsync(ct);

Not a nice surprise finding out it does not actually work in the release version - seems like we have to stick to the prerelease till the end of the year at least until this hopefully is being released with ef6

[aradalvand]

@smitpatel Any updates on this?!
It's among the most upvoted issues.
This feature was taken away from us in v3.0 I think, we're still waiting for it to make a comeback... Still nothing...

[smitpatel]

@AradAral - This issue is planned for EF Core 6.0 the next release of EF Core.

[aradalvand]

@smitpatel Great to hear. Thanks!

[Aeroverra]

I Love the .Net ecosystem but why does it take a whole version change to fix this stuff? Migrated to 3.0 only to find out the Include API was butchered and then upgraded to 5.0 to find out the fix to that still has issues. This is the kind of thing that happens when you fix things that are not broken.

[johnkwaters]

I am really upset by this. I have been waiting ever since 2.2 for AsSplitQuery. And now that it's here, it can't handle the use case where it's needed! I fetch a deep object graph off my Trip object with the following includes:

{
"CurrentBreadcrumb",
"Route",
"Route.Driver",
"Route.StartsAt",
"Route.StartsAt.TimeZone",
"Stops",
"Stops.Location",
"Stops.Location.TimeZone",
"Stops.Location.Address",
"Stops.Location.Address.State",
"Driver",
"Legs",
"Stops.Activities",
"Stops.Activities.ActivityType",
"Stops.Activities.Properties",
"Stops.Activities.SurveyResult",
"Stops.Activities.Survey",
"TripErrors",
"TripErrors.Stop",
"Metrics"
});

2.2 did it fast. 3.0. 3.1 and 5.0 use tons of memory and take a minute. SplitQuery wont process it. Am I supposed to hand code all of the parts of assembling this tree? What's the point of an ORM then? Why did you remove this feature and not add back any compatibility?

[goldsam]

Kind of a big deal that OData is broken without this

[jsheetzati]

We purposely skipped EFCore 3.x because of the single vs split query difference between 2.x and 3.x. Now, I am trying to migrate to 5.x and enabled SplitQuery globally. However, because of this open issue, we are finding ourselves getting hit with random runtime exceptions.

Does Microsoft.EntityFrameworkCore.Analyzers detect this situation at compile time? We are looking for a way to detect this situation rather than waiting for runtime exceptions.