C#用LINQ操作Datatable数据库

假设你上图表格数据为 DataTable dt，列分别为AA,BB,CC,DD

var query = from p in dt.AsEnumerable()
//分组
group p by new
{
t1 = p.Field("AA").ToString().Substring(0, p.Field("AA").ToString().Length - 2),
t2 = p.Field("BB"),
t3 = p.Field("CC")
} into g
select new
{
ColA = g.Key.t1,
ColB = g.Key.t2,
ColC = g.Key.t3,
ColD = g.Sum(c => c.Field("DD"))
};

//转datatable
DataTable dtNew = dt.Clone();
DataRow drNew;
foreach (var p in query)
{
drNew = dtNew.NewRow();
drNew["AA"] = p.ColA;
drNew["BB"] = p.ColB;
drNew["CC"] = p.ColC;
drNew["DD"] = p.ColD;

dtNew.Rows.Add(drNew);
}

dtNew 为最终结果

注：转换过程中要考虑空值的情况