Convert Datatable GroupBy Multiple Columns with Sum using Linq

Question

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.

Sample Data:-

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();

Answer 1

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:-

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;
    }

And, here is the Countries type:-

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

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