locked
Linq group by question RRS feed

  • Question

  • User-1104215994 posted

    Hi,

    Can I group by this?

    9501	England	1023	1023	London
    9501	Englang	1090	1090	Manchester
    9501	England	1094	1094	Liverpool
    9501	England	1108	1108	Birmingham
    9502	US	1181	1181	Boston
    9502	US	1247	1247	New York
    9502	US	1379	1379	Washington
    9503	Brazil	1466	1466	Sao Paulo
    9503	Brazil	1508	1508	Curitiba
    9504	Italy	1630	1630	Milano
    9505	France	1647	1647	Paris
    9505	France	1653	1653	Nice
    

    and get this result?

    9501 London, Manchester, Liverpool, Birmingham 
    9502 Boston, New York, Washington 
    9503 Sao Paulo, Curitiba 
    9504 Milano 
    9505 Paris, Nice

    Wednesday, September 30, 2020 12:11 PM

Answers

  • User-1330468790 posted

    Hi cenk1536,

     

    You could use GroupBy + Select() with string.Join() to get the desired result.

    Codes could describe much more clear than words.

    Console App:

    static void Main(string[] args)
            {
                DataTable dt = new DataTable();
                dt.Columns.Add("CountryID",typeof(int));
                dt.Columns.Add("CountryName", typeof(string));
                dt.Columns.Add("CityID", typeof(int));
                dt.Columns.Add("CityName", typeof(string));
                
                dt.Rows.Add(9501, "England", 1023, "London");
                dt.Rows.Add(9501, "England", 1090, "Manchester");
                dt.Rows.Add(9501, "England", 1094, "Liverpool");
                dt.Rows.Add(9501, "England", 1108, "Birmingham");
    
                dt.Rows.Add(9502, "US", 1181, "Boston");
                dt.Rows.Add(9502, "US", 1247, "New York");
                dt.Rows.Add(9502, "US", 1379, "Washington");
    
                dt.Rows.Add(9503, "Brazil", 1466, "Sao Paulo");
                dt.Rows.Add(9503, "Brazil", 1508, "Curitiba");
    
                dt.Rows.Add(9504, "Italy", 1630, "Milano");
    
                dt.Rows.Add(9505, "France", 1647, "Paris");
                dt.Rows.Add(9505, "France", 1653, "Nice");
    
                var query = from row in dt.AsEnumerable()
                            select new
                            {
                                CountryID = row.Field<int>("CountryID"),
                                CityName = row.Field<string>("CityName")
                            };
    
    
                var result = query.GroupBy(a => a.CountryID).Select(b => new { CountryID = b.Key, CityName = string.Join(",", b.Select(c => c.CityName).ToList()) });
    
                foreach(var item in result)
                {
                    Console.Write(item.CountryID);
                    Console.Write("\t");
                    Console.WriteLine(item.CityName);
                }
    
                Console.ReadLine();
                
            }

    Printing Result:

     

    Hope this can help you.

    Best regards,

    Sean

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 1, 2020 5:30 AM

All replies

  • User-1330468790 posted

    Hi cenk1536,

     

    You could use GroupBy + Select() with string.Join() to get the desired result.

    Codes could describe much more clear than words.

    Console App:

    static void Main(string[] args)
            {
                DataTable dt = new DataTable();
                dt.Columns.Add("CountryID",typeof(int));
                dt.Columns.Add("CountryName", typeof(string));
                dt.Columns.Add("CityID", typeof(int));
                dt.Columns.Add("CityName", typeof(string));
                
                dt.Rows.Add(9501, "England", 1023, "London");
                dt.Rows.Add(9501, "England", 1090, "Manchester");
                dt.Rows.Add(9501, "England", 1094, "Liverpool");
                dt.Rows.Add(9501, "England", 1108, "Birmingham");
    
                dt.Rows.Add(9502, "US", 1181, "Boston");
                dt.Rows.Add(9502, "US", 1247, "New York");
                dt.Rows.Add(9502, "US", 1379, "Washington");
    
                dt.Rows.Add(9503, "Brazil", 1466, "Sao Paulo");
                dt.Rows.Add(9503, "Brazil", 1508, "Curitiba");
    
                dt.Rows.Add(9504, "Italy", 1630, "Milano");
    
                dt.Rows.Add(9505, "France", 1647, "Paris");
                dt.Rows.Add(9505, "France", 1653, "Nice");
    
                var query = from row in dt.AsEnumerable()
                            select new
                            {
                                CountryID = row.Field<int>("CountryID"),
                                CityName = row.Field<string>("CityName")
                            };
    
    
                var result = query.GroupBy(a => a.CountryID).Select(b => new { CountryID = b.Key, CityName = string.Join(",", b.Select(c => c.CityName).ToList()) });
    
                foreach(var item in result)
                {
                    Console.Write(item.CountryID);
                    Console.Write("\t");
                    Console.WriteLine(item.CityName);
                }
    
                Console.ReadLine();
                
            }

    Printing Result:

     

    Hope this can help you.

    Best regards,

    Sean

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 1, 2020 5:30 AM
  • User-1104215994 posted

    I will try and let you know, thank you Sean Fang.

    Thursday, October 1, 2020 5:48 AM
  • User-1104215994 posted

    How can I modify this in order to get the names? I have data on text file.

    var groupQuery = from name in lines
                                    let n = name.Split('\t')
                                    group name by n[0]
                                    into g
                                    orderby g.Key
                                    select g;

    Thursday, October 1, 2020 8:19 AM
  • User-1104215994 posted

    This is working.

    var lines = File.ReadAllLines(@"T:\Store\store.txt")
                                    .Select(record => record.Split('\t'))
                                    .Select(tokens => new Region
                                    { Id = tokens[0], Name = tokens[1] })
                                    .GroupBy(x => x.Id);
    
    
    
                                foreach (var g in lines)
                                {
                                    Region[] values = g.ToArray();
                                    Console.WriteLine(values[0].Id +" "+ values[0].Name);
    }

    Thursday, October 1, 2020 6:56 PM
  • User-1330468790 posted

    Hi cenk1536,

     

    Glad to see that the codes are working now.

    If you still have any problem, feel free to post here. 

    We are happy to be able to help you. 

     

    Best regards,

    Sean

    Tuesday, October 6, 2020 6:03 AM