CODEWITHSUNDEEP
c#asp.netlinq
Harshal
Harshal

Reputation: 41

datatable colum sum using linq in c#

Here is my first datatable dt

sscode  scons  cscons  cstagged
  A      10         2         20
  A      10         2         20
  B      10         2         40

Here is my second datatable dt1

Unit  sscode
A101     A 
A101     A
B101     B

and i want this output

Unit  scons  cscons  cstagged
A101     20      4         40

I'm getting error while executing this query. Here is my code

IEnumerable<DataRow> result = from data1 in dt.AsEnumerable() 
                             join data2 in dt1.AsEnumerable()
                             on data1.Field<string>("sscode") equals
                             data2.Field<string>("substation_code")
                             group data2.Field<string>("Unit") by new {unit= data2.Field<string>("Unit")} into grp
                             orderby grp.Key.unit
                             select new
                                 {
                                     unit = grp.Key.unit,
                                     sscons = grp.Sum(s => s.Field<string>("cscons")),
                                     cscons = grp.Sum(s => s.Field<string>("cscons")),
                                     cstagged = grp.Sum(s => s.Field<string>("cstagged"))
                                 };
                             result.CopyToDataTable();

Upvotes: 2

Views: 1480

Answers (2)

Ofir Winegarten
Ofir Winegarten

Reputation: 9365

First, You have to select after the group by otherwise only the grouped field is selected.
Second, You cannot sum strings. Only numeric fields (int, double...)

I'm not fluent in the inline-linq syntax, so I've changed it to methods chain.

var result =
dt.AsEnumerable()
    .Join(dt1.AsEnumerable(), data1 => data1.Field<string>("sscode"), data2 => data2.Field<string>("substation_code"),
        (data1, data2) => new {data1, data2})
    .GroupBy(@t => new {unit = @t.data2.Field<string>("Unit")}, 
        @t => @t.data1)
    .Select(
        grp =>
            new
            {
                unit = grp.Key.unit,
                sscons = grp.Sum(s => s.Field<int>("sscons")),
                cscons = grp.Sum(s => s.Field<int>("cscons")),
                cstagged = grp.Sum(s => s.Field<int>("cstagged"))
            });

Note: Be aware that from this query you cannot use CopyToDataTable

Update
Since i understand that your fields are stored as strings you should use Convert.ToInt32:

grp.Sum(s => Convert.ToInt32(s.Field<string>("cscons"))

Update 2
As per the chat - it seems that the values are decimal and not ints:

 sscons = grp.Sum(s => s.Field<decimal>("sscons")),
 cscons = grp.Sum(s => s.Field<decimal>("cscons")),
 cstagged = grp.Sum(s => s.Field<decimal>("cstagged"))

Upvotes: 1

Rahul Singh
Rahul Singh

Reputation: 21795

The problem with your current code is that grp holds the collection of both dataTables in which case you won't be able to get the items from first DataTable directly.

If I have understood your question correctly then this should give you the expected output:-

var result = from data2 in dt2.AsEnumerable() 
             group data2 by data2.Field<string>("Unit") into g
             select new { Unit = g.Key, dt2Obj = g.FirstOrDefault() } into t3
             let filteredData1 = dt.AsEnumerable()
               .Where(x => x.Field<string>("sscode") == t3.dt2Obj.Field<string>("sscode"))
             select new
                   {
                       unit = t3.unit,
                       sscons = filteredData1.Sum(s => s.Field<int>("cscons")),
                       cscons = filteredData1.Sum(s => s.Field<int>("cscons")),
                       cstagged = filteredData1.Sum(s => s.Field<int>("cstagged"))
                   };

First we are grouping by Unit in second dataTable (as that is the grouo which we need) then we are projecting the the entire object to get the sscode by using FirstOrDefault, after this simply filter the first list based on value we got from grouped sscode and project the items.

Check Working Fiddle.

Upvotes: 1

Related Questions