Entity Framework Core GroupJoin & GroupBy produces unexpected SQL

Question

Entity Framework Core GroupJoin & GroupBy produces unexpected SQL

entity-framework-core linqpad

Question

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?

mikebridge

View more on Stack Overflow

mikebridge · Accepted Answer · 5/23/2017 5:20:35 PM

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.

Entity Framework Core GroupJoin & GroupBy produces unexpected SQL

Question

Fastest Entity Framework Extensions

Popular Answer

Performance

Expression Evaluator

Related