I'm attempting to query 5 parent records, then a summary of all child categories and counts:
context.Parent
.Take(5)
.GroupJoin(inner: context.Child,
outerKeySelector: parent => parent.Id,
innerKeySelector: child => child.ParentId,
resultSelector: (parent, children) => new SummaryResult
{
Id = parent.Id,
Name = parent.Name,
Children = parent.Children
.GroupBy(c => c.Category)
.Select(group => new ChildCategorySummary
{
Category = group.Key,
Count = group.Count()
})
});
This works as expected from LinkPad---I get one query for the first 5 Parent records then five queries summarizing each of the groups of children.
However, in EF7, I get this query:
exec sp_executesql N'SELECT [child].[Id], [child].[Category], [t].[Id]
FROM (
SELECT TOP(@__p_0) [s0].*
FROM [Parent] AS [s0]
) AS [t]
LEFT JOIN [Child] AS [child] ON [t].[Id] = [child].[ParentId]
ORDER BY [t].[Id]',N'@__p_0 int',@__p_0=5
and then this, five times:
SELECT [p].[Id], [p].[Category]
FROM [Child] AS [p]
I didn't expect the left join in the first query, which gives me all the child records.
Is this a problem with my query?
Partially figured this out. The child class has both Parent
and ParentId
:
public class Child
{
[Key]
public virtual Guid Id { get; set; }
[Required]
public virtual Parent Parent { get; set; }
public virtual Guid ParentId { get; set; }
}
So the selector
should use the required field Parent
, not 'ParentId`:
outerKeySelector: parent => parent,
innerKeySelector: child => child.parent
However, I now have a different error, which seems to be an EF Core bug:
System.ArgumentException : Property 'MyApp.Models.Parent Parent' is not defined for type 'MyApp.Models.Parent'
Parameter name: property
at System.Linq.Expressions.Expression.Property(Expression expression, PropertyInfo property)
at System.Linq.Expressions.Expression.MakeMemberAccess(Expression expression, MemberInfo member)
...
I think this is fixed in a prerelease version of EF Core 2.