使用Linq将Sumable转换为多个列 [英] Convert Datatable GroupBy Multiple Columns with Sum using Linq

问题描述

我想在Group BY之后的所有TotalImages列的总和，但它显示我的错误。
任何一个谁可以帮助我什么问题。
记住只是想从这个语法基础使用，并希望DataTable不是列表。如果有人帮我出门，将不胜感激。

I want to sum of all TotalImages Column after Group BY but its' showing me error. any one who can help me what's going wrong. Remember just want to use from this syntax base and want DataTable not a List. Kindly if some one help me out will be grateful.

样本数据： -

CountryId | CItyId | TotalImages
1              1        2
1              2        2
1              2        3
1              3        4 
2              1        2
2              2        2
2              2        3
2              3        4 




DataTable dt = dt.AsEnumerable()
 .GroupBy(r => new { Col1 = r["CountryId"], Col2 = r["CityId"]})
 .Select(g => g.Sum(r => r["TotalImages"]).First())
 .CopyToDataTable();

推荐答案

你可以使用这个： -

You can use this:-

DataTable countriesTable = dt.AsEnumerable().GroupBy(x => new { CountryId = x.Field<int>("CountryId"), CityId = x.Field<int>("CityId") })
                             .Select(x => new Countries
                                          {
                                              CountryId = x.Key.CountryId,
                                              CityId = x.Key.CityId,
                                              TotalSum = x.Sum(z => z.Field<int>("TotalImages"))
                                          }).PropertiesToDataTable<Countries>();

我收到以下输出： -

I am getting, following output:-

既然，我们对于匿名类型，不能使用 CopyToDataTable 方法，我使用了从这里，并相应修改。

Since, We cannot use CopyToDataTable method for anonymous types, I have used an extension method took from here and modified it accordingly.

public static DataTable PropertiesToDataTable<T>(this IEnumerable<T> source)
    {
        DataTable dt = new DataTable();
        var props = TypeDescriptor.GetProperties(typeof(T));
        foreach (PropertyDescriptor prop in props)
        {
            DataColumn dc = dt.Columns.Add(prop.Name, prop.PropertyType);
            dc.Caption = prop.DisplayName;
            dc.ReadOnly = prop.IsReadOnly;
        }
        foreach (T item in source)
        {
            DataRow dr = dt.NewRow();
            foreach (PropertyDescriptor prop in props)
            {
                dr[prop.Name] = prop.GetValue(item);
            }
            dt.Rows.Add(dr);
        }
        return dt;
    }

而且，这里是国家 type： -

And, here is the Countries type:-

public class Countries 
{
    public int CountryId { get; set; }
    public int CityId { get; set; }
    public int TotalSum { get; set; }
}

如果您愿意，您可以使用任何其他方法将其转换为DataTable 。

You can use any other approach to convert it to a DataTable if you wish.

这篇关于使用Linq将Sumable转换为多个列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！