Sum of fields using LINQ

Question

Some background before asking my question.

Im using sql compact, and i have two tables, The first table (IssueEmp)

enter image description here

The second table (RecEmp)

enter image description here

 SqlCeDataAdapter adap = new SqlCeDataAdapter("SELECT * FROM RecEmp", cn);
            DataTable dat = new DataTable();
            DataSet receice = new DataSet();
            adap.Fill(receice);
            adap.Fill(dat);

 SqlCeDataAdapter adap1 = new SqlCeDataAdapter("SELECT * FROM IssueEmp", cn);
            DataTable dat1 = new DataTable();
            DataSet issue = new DataSet();
            adap1.Fill(issue);
            adap1.Fill(dat1);

Im performing a join between RecEmp and IssueEmp using linq

var res = from t1 in receice.Tables[0].AsEnumerable()

                      join t2 in issue.Tables[0].AsEnumerable()                                            
                         on new 
                         {
                             CNo = t1.Field<int>("CNo"),
                             Empid = t1.Field<int>("EmpID") 
                         } 
                         equals new 
                         {
                             CNo = t2.Field<int>("CNo"),
                             Empid = t2.Field<int>("EmpID") 
                         }                     
                      select new
                       {
                           SNo = t1.Field<int>("SNo"),
                           ChNo = t1.Field<int>("CNo"),
                           EmpID = t1.Field<int>("EmpID"),
                           DateIssued = t2.Field<DateTime>("Date"),                                                                               
                           RMIssued = t2.Field<string>("RMCode"),
                           QuantityIssued = t2.Field<double>("Quantity"),

                           DateReceived = t1.Field<DateTime>("Date"),
                           RMCodeReceived = t1.Field<string>("RMCode"),
                           QuantityReceived = t1.Field<double>("Quantity")

                       };

The output Im getting from the above linq query is

enter image description here

But I don't know how to get the sum of issued quantity likewise the sum of received quantity, lastly the difference between the two sum as the diff. The required is below.

enter image description here

See [HOW TO: Implement a DataSet JOIN helper class in Visual C# .NET](http://support.microsoft.com/kb/326080/en-us) — Anujith, Dec 23 '12 at 15:55
@A.V why should i use DataSet JOIN helper class when it can be done with linq... — Ali Hasan, Dec 23 '12 at 16:20

score 2 · Accepted Answer · answered Dec 23 '12 at 19:00

Note:

I´m a bit lazy so I didn´t use all the records you provided, only the first four records.

Expected result:

This is what I got:

enter image description here

The Linq query:

var query = from d in data
           group d by new { d.DateIssued, d.EmpId, d.ChNo, d.DateReceived }
           into x
           select new {
                Date = x.Key.DateIssued,
                CNo = x.Key.ChNo,
                EmpId=x.Key.EmpId,
                CRi = x.Where(c=>c.RMIssued == "CR").Sum(c=>c.QuantityIssued),
                SJi = x.Where(c=>c.RMIssued == "SJ").Sum(c=>c.QuantityIssued),
                TTi = x.Where(c=>c.RMIssued == "TT").Sum(c=>c.QuantityIssued),
                WRi = x.Where(c=>c.RMIssued == "WR").Sum(c=>c.QuantityIssued),
                TotalIssued = x.Sum(c => c.QuantityIssued),

                DateReceived = x.Key.DateReceived,
                CRr = x.Where(c=>c.RMCodeReceived == "CR").Sum(c=>c.QuantityReceived),
                SJr = x.Where(c=>c.RMCodeReceived == "SJ").Sum(c=>c.QuantityReceived),
                TTr = x.Where(c=>c.RMCodeReceived == "TT").Sum(c=>c.QuantityReceived),
                WRr = x.Where(c=>c.RMCodeReceived == "WR").Sum(c=>c.QuantityReceived),
                TotalReceived = x.Sum(c => c.QuantityReceived),

                Diff = x.Sum(c => c.QuantityIssued) - x.Sum(c => c.QuantityReceived)
            };

Data used:

And this is the set of data I used to test it:

var data= new []{
    new { SNo= 9,  ChNo=5,  EmpId=81, DateIssued=dateIssued, RMIssued="SJ", QuantityIssued=30,   DateReceived=dateReceived, RMCodeReceived="SJ", QuantityReceived=20.3},
    new { SNo= 10, ChNo=5,  EmpId=81, DateIssued=dateIssued, RMIssued="SJ", QuantityIssued=30,   DateReceived=dateReceived, RMCodeReceived="CR", QuantityReceived=9.6},
    new { SNo= 11, ChNo=28, EmpId=82, DateIssued=dateIssued, RMIssued="TT", QuantityIssued=30.5, DateReceived=dateReceived, RMCodeReceived="TT", QuantityReceived=29},
    new { SNo= 12, ChNo=28, EmpId=82, DateIssued=dateIssued, RMIssued="WR", QuantityIssued=10,   DateReceived=dateReceived, RMCodeReceived="TT", QuantityReceived=29}
};

I recommed you use LinqPad to test it.

Good luck!

Sum of fields using LINQ

1 Answers1