Flattening Linq Group query

Question

I have a list of data like so:

ID AddressPurpose Address ...
1  L
1  P
2  L
2  P
3  P
3  L
4  P
4  L
5  P
6  L

I want to be able to filter the data so that for each unique number if there is a P row then it is returned else the L row is returned. So the data will look like this:

ID AddressPurpose Address ...
1  P
2  P
3  P
4  P
5  P
6  L

At the moment I have this query which works fine:

var query = from c in list
            orderby c.AddressPurpose descending
            group c by c.ID
            into g
                select g;

var finalList = new List<Company>();
foreach (var list in query)
{
    finalList.Add(list.First());
}
return finalList;

Is there a better way to do this without using the extra foreach?

Answer 1

You could always nest your queries:

var query =
    from i in (
        from c in list 
        orderby c.AddressPurpose descending 
        group c by c.ID into g 
        select g)
    select i.First();

return query;

I'm sure this isn't the only way to do it (or possibly even the best), but it does wrap your "foreach" up into the one query.

Edit

In fact, you can simplify this down to:

var query = from c in list
            orderby c.AddressPurpose descending
            group c by c.ID into g
            select g.First();

That seems to give the right result.

Answer 2

Why don't you select g.First() instead?

Answer 3

var finalList = list
  .GroupBy(c => c.ID)
  .Select(g => g.OrderByDescending(c => c.AddressPurpose).First())
  .ToList();