Home > Enterprise >  Group By 2 column and Max() others in C#
Group By 2 column and Max() others in C#

Time:09-17

what way that make me able to group by 2 columns and get MAX each other columns

lets say I have this:

        DataTable dt = new DataTable();
        dt.Columns.Add("s1", typeof(string));
        dt.Columns.Add("s2", typeof(string));
        dt.Columns.Add("nt1", typeof(int));
        dt.Columns.Add("nt2", typeof(int));

        // Here we add five DataRows.
        dt.Rows.Add("g1", "gg1", 1, 16);
        dt.Rows.Add("g2", "gg1", 2, 15);
        dt.Rows.Add("g1", "gg1", 3, 14);
        dt.Rows.Add("g2", "gg1", 4, 13);
        dt.Rows.Add("g1", "gg2", 5, 12);
        dt.Rows.Add("g2", "gg2", 6, 11);
        dt.Rows.Add("g1", "gg2", 7, 10);
        dt.Rows.Add("g2", "gg2", 8, 9);

what I'm trying to get is :

        g1  gg1 3   16
        g1  gg2 7   12
        g2  gg1 4   15
        g2  gg2 8   11

this code not work :

           dt = dt.AsEnumerable()
           .GroupBy(r => new
           {
               s1 = r["s1"],
               s2 = r["s2"]
           })
           .Select(x => x.Max())
           .CopyToDataTable();

give me error :

Severity Code Description Project File Line Suppression State Error CS0311 The type 'System.Linq.IGrouping<string, string>' cannot be used as type parameter 'T' in the generic type or method 'DataTableExtensions.CopyToDataTable(IEnumerable)'. There is no implicit reference conversion from 'System.Linq.IGrouping<string, string>' to 'System.Data.DataRow'. testt C:\Users\TECNO\source\repos\testt\testt\Program.cs 44 Active

CodePudding user response:

So, the error happens on CopyToDataTable because of a type mismatch. You will need to convert the result of GroupBy into an IEnumerable of DataRow.

I did not work in LINQ for a very long while, you will need to apply a Select before you call CopyToDataTable and make sure that you select s1, s2, Max(nt1) and Max(nt2). Maybe you will also need to call ToList, I'm not sure, but you will definitely need to do another Select after the grouping.

CodePudding user response:

ok this code fix the problem

thanks all

        var query = dt.AsEnumerable()
            .GroupBy(x => new
            {
                s1 = x["s1"],
                s2 = x["s2"]
            })
            .Select(grp => new {
                s1 = grp.Key.s1,
                s2 = grp.Key.s2,
                nt1 = grp.Max(x => x["nt1"]),
                nt2 = grp.Max(x => x["nt2"])
            });

        DataTable dt2 = dt.Clone();
        dt2.Clear();

        foreach (var item in query)
            dt2.Rows.Add(item.s1, item.s2, item.nt1, item.nt2);
  • Related