GroupBy first result of lookup table in Entity Framework Core

Question

I'm trying to get the total Man Days worked by volunteers per section between a date range. There is an attendance table which includes the day and the member id to map to the member table. There is also a section table to list all the sections and a membertosection table as a member can be in more than one section.

I want to group by the first section a member is in and then have a count of all the attendance days for that section.

The following works perfectly where I'm grouping by a 1 to many field (Category) on the members table but I can't work out how to change this to the first section (If the member has a section)

var result = await _context.Attendance
                .Where(p => p.Day >= firstDayOfLastQuarter && p.Day <= lastDayOfLastQuarter)
                .GroupBy(p => p.Member.Category.Name)
                .Select(p => new { key = p.Key, count = p.Count() })
                .ToListAsync();

I'm looking for something like

var result = await _context.Attendance
                .Where(p => p.Day >= firstDayOfLastQuarter && p.Day <= lastDayOfLastQuarter && p.Member.MemberToSection.Any(s => s.MemberId == p.MemberId))
                .GroupBy(p => p.Member.MemberToSection.First().Section.Name)
                .Select(p => new { key = p.Key, count = p.Count() })
                .ToListAsync();

Update

T-Sql code that produces the desired result is

SELECT Count(*) as Total, Section.Name
FROM Attendance
LEFT JOIN Member on Attendance.Member_Id = Member.Id
OUTER APPLY (
  SELECT TOP(1) sec.id, sec.Name
  FROM MemberToSection m2s
  LEFT JOIN Section sec ON m2s.Section_Id = sec.Id
  WHERE m2s.Member_Id = Member.Id 
) as Section
WHERE Attendance.Day >= '2016-10-01' AND Attendance.Day <= '2016-12-31'
GROUP BY Section.Name

The "something like" code returns the error

An unhandled exception occurred while processing the request.

ArgumentException: Property 'System.String Name' is not defined for type 'Microsoft.EntityFrameworkCore.Storage.ValueBuffer'

Classes

As requested here are the relevant classes

Attendance.cs

public partial class Attendance
{
    public long Pk { get; set; }
    public int MemberId { get; set; }
    public DateTime Day { get; set; }

    public virtual Member Member { get; set; }
}

Member.cs

public partial class Member
{
    public Member()
    {
        MemberToSection = new HashSet<MemberToSection>();
    }

    public int Id { get; set; }
    public int? CategoryId { get; set; }

    public virtual ICollection<MemberToSection> MemberToSection { get; set; }
}

MemberToSection.cs

public partial class MemberToSection
{
    public int SectionId { get; set; }
    public int MemberId { get; set; }

    public virtual Member Member { get; set; }
    public virtual Section Section { get; set; }
}

Section.cs

public partial class Section
{
    public Section()
    {
        MemberToSection = new HashSet<MemberToSection>();
    }

    public int Id { get; set; }
    public string Name { get; set; }

    public virtual ICollection<MemberToSection> MemberToSection { get; set; }
}

Answer 1

The (currently combined) EF documentation starts with Compare EF Core & EF6.x section which contains the very "useful" topic Which One Is Right for You. Well, looks like EF Core is not for you (yet). The following applies to latest at this time EF Core v1.1.0.

First, GroupBy (even by simple primitive property) is always processed in memory.

Second, there are a lot of internal bugs causing exceptions when processing pretty valid LINQ queries like yours.

Third, after some trial and error, the following equivalent construct works for your case (at least does not generate exceptions):

.GroupBy(p => p.Member.MemberToSection.Select(m => m.Section.Name).FirstOrDefault())

(btw, irrelevant to the issue, the s => s.MemberId == p.MemberId condition inside the p.Member.MemberToSection.Any call is redundant because it is enforced by the relationship, so simple Any() would do the same.)

But now not only the GroupBy is performed in memory, but also the query is causing N + 1 SQL queries similar to EF Core nested Linq select results in N + 1 SQL queries. Congratulations :(