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

user_59791 0
Viewed 60

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?

linq c#
1 Answers

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 
    };
user_106463160
Related