C# Linq return the first element of each group

Question

What I try to achieve is the following: Filter the ProjektStatus with the where function, the group them by ProjektId. Order the group descending by StatusMonatJahr and then take the first element of each group and return it.

public IEnumerable<ProjektStatus> GetCurrentProjektStatus(Func<ProjektStatus, bool> where)
{
    return this.db.ProjektStatus
        .Where(where)
        .GroupBy(x => x.ProjektId)
        .Select(x => 
             x.OrderByDescending(y => y.StatusMonatJahr))
        .First();
}

Unfortunately, with this query I get the first whole group instead of the first element of each group.

What do I have to change to achieve this?

Thanks in advance

You just need to move the `First` inside of the `Select` so it's applied to the ordered group instead of the collection of groups. `.Select(x => x.OrderByDescending(y => y.StatusMonatJahr).First());` — juharr, Jun 03 '21 at 13:50
oooh that makes sense! thank you it worked :-) if you add it as an answer, I'll gladly accept it — xeraphim, Jun 03 '21 at 13:53
`OrderBy` plus `First` is a fairly expensive way to get the biggest item from a collection. You might consider implementing a `MaxBy` method that just searches for the biggest item directly. — Peter Ruderman, Jun 03 '21 at 13:57
@PeterRuderman `MaxBy` doesn't exist in the BCL. For linq to object it may exist in MoreLinq. And in this case (Linq to entities) I suppose the expression will be optimized. — Orace, Jun 03 '21 at 15:13

Orace · Accepted Answer · 2021-06-03T15:04:53.040

The code should return the first element in each group, not the content of the first group.

public IEnumerable<ProjektStatus> GetCurrentProjektStatus(Func<ProjektStatus, bool> where)
{
    return this.db.ProjektStatus
                  .Where(where)
                  .GroupBy(x => x.ProjektId)
                  .Select(x => x.OrderByDescending(y => y.StatusMonatJahr).First());
}

Some remarks :

Language keyword should be avoided as variable name. Here it's about the where parameter. whereFunc is a good name.
The GroupBy, Select, OrderByDescending operations can be done remotely (server side), for that they should be called first. An other option is to do everything remotely, for that, the type of the whereFunc should be Expression<Func<ProjectStatus, bool>>.
Personal opinion: you should prefer to code in English, if your company doesn't do it, I feel bad for you.

Here is the result :

public IEnumerable<ProjectStatus> GetCurrentProjectStatuses(Func<ProjectStatus, bool> whereFunc)
{
    return ProjectStatuses
        .GroupBy(s => s.ProjectId)
        .Select(g => g.OrderByDescending(s => s.MonthAndYear).First())
        .AsEnumerable()   // From now on the execution is done locally 
        .Where(whereFunc);
}

C# Linq return the first element of each group

1 Answers1