LINQ to Datatable Group by并返回所有列

本文关键字：返回 by to Datatable Group LINQ | 更新日期: 2023-09-27 18:04:06

我有一个Datatable 

ID  Age Last name   First Name  Sex class   Father_Name Mother_Name Marks
1   20  A   B   M   1   A   B   50
1   20  A   B   M   1   A   B   50
1   20  A   B   M   1   A   B   100
2   15  F   G   F   2   H   J   40
2   15  F   G   F   2   H   J   50

,我想要以下结果后应用Linq组

1   20  A   B   M   1   A   B   200
2   15  F   G   F   2   H   J   90

我能够得到的总和，但我不能得到其他列在LINQ组

有谁能帮帮忙吗

var drResults = from r1 in dtResults.AsEnumerable()
                            group r1 by new 
                                {
                                   MarksSum=  r1.Field<int>("Marks"),
                                }
                                into g
                            select new
                            {

                                    Total = g.Sum(r1=>r1.Field<int>("Marks"))                                   
                            };

LINQ to Datatable Group by并返回所有列

var _result =   from r1 in dtResults.AsEnumerable()
                group r1 by new
                {
                    ID = r1.Field<int>("ID"),
                    Age = r1.Field<int>("Age"),
                    LastName =  r1.Field<int>("LastName"),
                    FirstName = r1.Field<int>("FirstName"),
                    Sex =  r1.Field<int>("Sex"),
                    Class =  r1.Field<int>("class"),
                    Father_Name =  r1.Field<int>("Father_Name"),
                    Mother_Name =  r1.Field<int>("Mother_Name")
                } into g
                select new
                {
                    ID = g.Key.ID,
                    Age = g.Key.Age,
                    LastName =  g.Key.LastName,
                    FirstName = g.Key.FirstName,
                    Sex =  g.Key.Sex,
                    Class =  g.Key.Class,
                    Father_Name =  g.Key.Father_Name,
                    Mother_Name =  g.Key.Mother_Name,
                    TotalMark = g.Sum(x => x.Field<int>("Marks"))
                };

您要按Id ?!然后你可以在组上使用Sum和Marks:

var result = from r in dtResults.AsEnumerable()
             group r by r.Field<int>("ID") into IdGroup
             let row = IdGroup.First()
             select new
             {
                 ID = IdGroup.Key,
                 Age = row.Field<int>("Age"),
                 LastName = row.Field<string>("Last_Name"),
                 FirstName = row.Field<string>("First_Name"),
                 MotherName = row.Field<string>("Mother_Name"),
                 FatherName = row.Field<string>("Father_Name"),
                 Marks = IdGroup.Sum(r => r.Field<int>("Marks"))
             };

嘿Tim，如果所有行都有不同的值，那么我该怎么办?如LastName不同，ID相同。我有一些数据腐败，或者你可以说是多余的。

显示如何"修复"您的DataTable超出了这个问题的范围。但是，如果您想要列出所有行，即使它们具有相同的ID，但每个ID - group的Mark的正确总和，您可以使用此查询:

var result = from r in dtResults.AsEnumerable()
             group r by r.Field<int>("ID") into IdGroup
             let IdTotalMarks = IdGroup.Sum(r => r.Field<int>("Marks"))
             from row in IdGroup
             select new
             {
                 ID = IdGroup.Key,
                 Age = row.Field<int>("Age"),
                 LastName = row.Field<string>("Last Name"),
                 FirstName = row.Field<string>("First Name"),
                 MotherName = row.Field<string>("Mother Name"),
                 FatherName = row.Field<string>("Father_Name"),
                 Marks = IdTotalMarks
             };

JW使用Lambda语法的回答(我还将字段重命名为"class"，因为这将无法编译):

       var _result = dtResults
            .AsEnumerable()
            .GroupBy(r1 => new
            {
                ID = r1.Field<int>("ID"),
                Age = r1.Field<int>("Age"),
                LastName =  r1.Field<string>("LastName"),
                FirstName = r1.Field<string>("FirstName"),
                Sex =  r1.Field<int>("Sex"),
                Class =  r1.Field<int>("class"),
                Father_Name =  r1.Field<string>("Father_Name"),
                Mother_Name =  r1.Field<string>("Mother_Name")
            }).Select(g => new
            {
                ID = g.Key.ID,
                Age = g.Key.Age,
                LastName =  g.Key.LastName,
                FirstName = g.Key.FirstName,
                Sex =  g.Key.Sex,
                Class =  g.Key.Class,
                Father_Name =  g.Key.Father_Name,
                Mother_Name =  g.Key.Mother_Name,
                TotalMark = g.Sum(x => x.Field<int>("Marks"))
            });
相关文章：