CODEWITHSUNDEEP
c#linq
Hussein
Hussein

Reputation: 991

Using LINQ to Group, concatenate and Counting

Using Linq i need to summarize table (1) into table (2)

Table (1)

Race Swimmer Country
freestyle A USA
freestyle B USA
freestyle C UK
freestyle D ITA
butterfly E USA
butterfly F UK
butterfly G UK
butterfly H ITA

Table (2)

Race Count Country
freestyle 4 USA(2)-UK(1)-ITA(1)
butterfly 4 USA(1)-UK(2)-ITA(1)

Where Race is the Race Type, Count=the number of Swimmers in the Race, Country= summary of the number of swimmers per each country in the indicated form. I managed to use grouping and join text using string.Join() method but i could not add the count beside each country as shown above

var _s= _races.GroupBy(x => new { x.Race })
                      .Select(y => new
                       {
                         Race= y.Key.Race,
                         Count= y.Count(x => x.Race!= null),
                         Country= string.Join(" - ", y.Select(z =>z.Country).Distinct()) 
                                                 
                        });

Upvotes: 0

Views: 246

Answers (1)

titol
titol

Reputation: 1149

You may replace the line

Country= string.Join(" - ", y.Select(z =>z.Country).Distinct())

With:

Country= string.Join(" - ", y.Select(z =>$"{z.Country}({y.Where(c=>c.Country==z.Country).Count()})").Distinct())

It's not the best solution, but it keeps your way of doing query.

If you want this query to be little bit more optimized, then you may introduce distinctCountries variable f.e.:

list.GroupBy(x => x.race)
       .Select(y => {
           var distinctCountries = y.Select(z => z.country).Distinct();
           return new
           {
               Race = y.Key,
               Count = y.Count(),
               Country = string.Join(" - ", distinctCountries.Select(z => $"{z}({y.Where(c => c.country == z).Count()})").Distinct())

           };

       });

Upvotes: 2

Related Questions