CODEWITHSUNDEEP
c#.netlinq
Harshal Yelpale
Harshal Yelpale

Reputation: 535

C#: LINQ query grouping by multiple columns in data table using Field method

I am trying to get sum of t & kg grouping by year and unit. Having problems getting it done in Linq using Field method. However. I'm able to do that without using the Field method.

The data in the data table is in below format.

Unit  Quantity  Year
 "t"    "10"    2019
 "t"    "05"    2018
 "kg"   "05"    2018
 "t"    "02"    2019
 "kg"   "14"    2018
 "kg"   "28"    2019

And the result should be this;

Result

 Total 2018 in kg - 19 
 Total 2018 in t  - 05
 Total 2019 in kg - 28
 Total 2019 in t  - 12

Here's all I have right now, it's grouping by all 3 columns as I mentioned in a group by statement. But, if I don't then I won't be able to access that column in the select statement.

Code snippet

Note: I don't have model property structure. 

DataTable Dt = dsData.Tables["Data2019"];

 var data = from mfRecords in Dt.AsEnumerable()
        group mfRecords by new
        {
         DataUnit = mfRecords.Field<string>("Unit").ToString(),
         Quantity = Convert.ToDouble(mfRecords.Field<string>("Qty")),
         RecordYear = Convert.ToDateTime(mfRecords.Field<string>("SalesDate")).Year,
        } into g
        select new
         {
          SalesYear = g.Key.RecordYear,
          SalesUnit = g.Key.DataUnit,  
          TotalQty = g.Sum(k => g.Key.Quantity)
         };

This is done by using LINQ

var data = lst.Select(k => new { k.SalesDate.Year, k.Qty, k.Unit })
            .GroupBy(x => new { x.Year,x.Unit }, (key, group) => new
            {
                SalesYear =  key.Year,
                SalesUnit = key.Unit,
                TotalQty = string.Format("{0:0.00}",group.Sum(k => k.Qty))
            })            
            .ToList();

Does anyone know how I can group by with Field method using LINQ?

Upvotes: 1

Views: 147

Answers (1)

dotNET
dotNET

Reputation: 35400

Go like this instead:

 var data = from mfRecords in Dt.AsEnumerable()
            group mfRecords by new
            {
             DataUnit = mfRecords.Field<string>("Unit").ToString(),
             RecordYear = mfRecords.Field<string>("SalesDate")
            } into g
            select new
             {
              SalesYear = g.Key.RecordYear,
              SalesUnit = g.Key.DataUnit,  
              TotalQty = g.Sum(k => int.Parse((string)k["Qty"]))
             };

Explanation: You do not need to group by Quantity, because that is what you want to sum.

Upvotes: 2

Related Questions