In one of my applications I'm using EF Core 3.1 with lazy-loading proxies enabled. According to the documentation here and this StackOverflow answer, it seems like I should be able to explicitly load a collection and have that collection marked as loaded if I do something like:
await context.Entry(parent)
.Collection(p => p.Children)
.Query()
.Include(c => c.Grandchildren)
.Where(p => p.Id > 100)
.LoadAsync();
This should fetch a filtered subset of the child entities for the parent entity and pull back a collection of related entities on the children themselves.
At that point, I would expect that parent.Children is considered loaded by EF Core, such that I can access that collection later in my code and it won't try to go back to my database i.e. won't try to lazy-load it.
In my application I'm finding that that's not at all the case. In the screenshot below, you can see that the Order collection on the productConsultant entity isn't marked as loaded after I try to do something similar:
I can see it goes to the database and performs close to the query I specify (it includes the "where" condition but doesn't join on to the related entities):
SELECT [o].[Id],
[o].[Assignee],
[o].[CommissionAuthorisedInPeriod],
[o].[CommissionPaidInPeriod],
[o].[CsiMaxScore],
[o].[CsiScore],
[o].[CustomerName],
[o].[DateDelivered],
[o].[DateSigned],
[o].[DeliveredInPeriod],
[o].[EmailStatus],
[o].[HasZeroCsiScore],
[o].[IsFleetOrder],
[o].[IsSubPrime],
[o].[OrderStreamCheckpoint],
[o].[PaymentMethod],
[o].[Status],
[t].[Id],
[t].[Vehicle],
[t].[VehicleSaleType]
FROM [Order] AS [o]
LEFT JOIN
(
SELECT [o0].[Id],
[o0].[Vehicle],
[o0].[VehicleSaleType]
FROM [Order] AS [o0]
WHERE [o0].[VehicleSaleType] IS NOT NULL
AND [o0].[Vehicle] IS NOT NULL
) AS [t] ON [o].[Id] = [t].[Id]
WHERE ([o].[Assignee] = @__p_0)
AND ([o].[CommissionAuthorisedInPeriod] = @__activePeriod_1);
However, because it doesn't mark the collection as loaded, when I try to reference the Order collection within the ProductConsultant class, it goes off and does another database query for all the orders the product consultant has ever delivered, even though I've tried explicitly loading the order collection earlier with only a subset of orders:
public void PayCommissions(Period period)
{
var payableOrders = from o in Orders
where o.CommissionAuthorisedInPeriod == period
where !o.HasBeenPaid
select o;
// ...
}
SELECT [o].[Id],
[o].[Assignee],
[o].[CommissionAuthorisedInPeriod],
[o].[CommissionPaidInPeriod],
[o].[CsiMaxScore],
[o].[CsiScore],
[o].[CustomerName],
[o].[DateDelivered],
[o].[DateSigned],
[o].[DeliveredInPeriod],
[o].[EmailStatus],
[o].[HasZeroCsiScore],
[o].[IsFleetOrder],
[o].[IsSubPrime],
[o].[OrderStreamCheckpoint],
[o].[PaymentMethod],
[o].[Status],
[t].[Id],
[t].[Vehicle],
[t].[VehicleSaleType]
FROM [Order] AS [o]
LEFT JOIN
(
SELECT [o0].[Id],
[o0].[Vehicle],
[o0].[VehicleSaleType]
FROM [Order] AS [o0]
WHERE [o0].[VehicleSaleType] IS NOT NULL
AND [o0].[Vehicle] IS NOT NULL
) AS [t] ON [o].[Id] = [t].[Id]
WHERE [o].[Assignee] = @__p_0;
If I were to change the original query such that it didn't do anything but load the collection, then it does mark the collection as loaded, but this is pointless as it pulls back far too much data and doesn't allow me to include nested entity collections:
Like dozens of other problems I've encountered with EF Core 3, this is something that could probably be easily resolved with EF Core 5 (using filtered includes). Until I'm able to upgrade this project to target .NET 5, Is there a way to get this to work with EF Core 3? Is there something I'm missing?
Update
Based on Stephen's answer, I've made the changes shown below. This allows me to fetch back only the relevant data from the database, and prevent EF Core from doing the second query later on:
