How to select only several columns from GroupBy... First()-CodePudding

I am trying to select "highest" licence user has per year.

I started with:

var lic = _context.Licences
            .Where(l => l.MemberNumber == memberNumber) 
            .GroupBy(l => new { l.LicenceYear }
            (key, g) => g.OrderByDescending(l => l.ProfessionalTitleId)
            .First());

and it works.

However it always returns ALL columns from Licence which I don't want. Moreover I would like to select some child property like l.Title.TitleNumber, but no matter where I put .Select(l => new {l.LicenceNumber, l.Title.TitleNumber}) generated sql query selects all columns from Licences, and only from Licences.

I have also tried

var lic = _context.Licences
            .Where(l => l.MemberNumber == User.GetMemberNumber())            
            .GroupBy(l => new { l.LicenceYear },
            l => new { l.Title.TitleNumber, l.LicenceYear, l.LicenceNumber, l.Title.ProfessionalTitleId },
            (key, g) => g.OrderByDescending(l => l.ProfessionalTitleId)
            .First());

Interestingly enough it properly creates ROW_NUMBER() using ProfessionalTitleId from Title but still selects all columns from Licences.

So, how to modify query to select only some columns?

CodePudding user response：

This feature is new for EF Core 6, and I saw it's implementation - they just clone parsing context and probably with all fields. Probably they will improve projection later if you create issue in GitHub.

Anyway, there is workaround which emulates the same query without grouping:

var filtered = _context.Licences.Where(l => l.MemberNumber == memberNumber);

var lic = 
    from d in filtered.Select(l => new { l.LicenceYear }).Distinct()
    from l in filtered.Where(l => l.LicenceYear == d.LicenceYear)
        .OrderByDescending(l => l.ProfessionalTitleId)
        .Take(1)
    select new 
    { 
        l.Title.TitleNumber, 
        l.LicenceYear, 
        l.LicenceNumber, 
        l.Title.ProfessionalTitleId 
    };