Linq - C# : Create Concatenated Lists and Sub-Lists

Question

I have the following table :

Country	Team	Player
England	MU	1
England	MU	2
England	MU	3
England	LIV	4
England	LIV	5
Spain	RM	6
Spain	RM	7
Spain	BAR	8
Spain	BAR	9
Spain	BAR	10

And i want to get this output :

Country	Team & Player
England	MU(1,2,3) - LIV(4,5)
Spain	RM(6,7) - BAR(8,9,10)

I tried for several times till i developed following linq query but couldn't adjust it to give the required output :

var output= _model.GroupBy(x => new { x.Country })
            .Select(y =>
            {   
              var distinctTeam = y.Where(z => (z.Player !=  null)).Select(z => z.Team);
              return new
                {   
                   Country = y.Key.Country,
                   Team&Player= string.Join(" - ", distinctTeam.Select(z => $"{z} 
                          ({y.Where(c => c.Team == z && ((c.Team != 
                           null))).SelectMany(x=> string.Join(" , 
                           ",xPlayer)))})").Distinct())
                 };
              }).ToList();

Your support is highly appreciated.

Answer 1

For starters, Team&Player isn't a valid property name. That ampersand will cause an error. Second, you're going to want a nested GroupBy() for teams, e.g.:

var output = _model
  .GroupBy(g => g.Country)
  .Select(c => new {
    Country = c.Key,
    TeamAndPlayer = string.Join(" - ", c
      .GroupBy(c => c.Team)
      .Select(t => $"{t.Key}({string.Join(",", t.Select(p => p.Player))})")
    )         
  });