Changing Rows to Columns in LINQ

Question

I have list which displays recorded as shown by View A using LINQ but I want the record to display as shown by View B.

Also, I am using angularjs as my binding script using ng-repeat with a table to display the record.

I have included a sample of my code below.

View A:

enter image description here

View B:

enter image description here

Code:

   public IQueryable<PayGrossEntitlement> GetPayrollById(int payrollId, int schoolBranchId, int schoolId)
          {
              var query = from x in _context.Db.tbl_PayGrossEntitlement
                          where x.SchoolId == schoolId && x.SchoolBranchId == schoolBranchId && x.PayrollId == payrollId && x.SalaryComponentId !=6
                          select new PayGrossEntitlement()
                          {
                              SchoolId = x.SchoolId,
                              SchoolBranchId = x.SchoolBranchId,
                              SalaryComponentId = x.SalaryComponentId,
                              SalaryComponentName = x.tbl_SalaryComponent.SalaryComponentName,
                              StaffName = x.tbl_Staff.LastName + " " + x.tbl_Staff.FirstName,
                              Amount = x.Amount,
                              TaxAmount = x.tbl_Staff.tbl_Tax.Count(y=>y.PayrollId == payrollId && y.StaffId == x.StaffId) == 0 ? 0 : x.tbl_Staff.tbl_Tax.FirstOrDefault(y=>y.PayrollId == payrollId && y.StaffId == x.StaffId).Amount,
                              LeaveAmount = x.tbl_Staff.tbl_PaidLeave.Count(y => y.PayrollId == payrollId && y.StaffId == x.StaffId) == 0 ? 0 : x.tbl_Staff.tbl_PaidLeave.FirstOrDefault(y => y.PayrollId == payrollId && y.StaffId == x.StaffId).Amount,
                              LoanAmount = x.tbl_Staff.tbl_PaidLoan.Count(y => y.PayrollId == payrollId && y.StaffId == x.StaffId) == 0 ? 0 : x.tbl_Staff.tbl_PaidLoan.FirstOrDefault(y => y.PayrollId == payrollId && y.StaffId == x.StaffId).Amount,
                              StaffId = x.StaffId,
                              PayrollId = x.PayrollId,

                          };

              return query;
          }

View Template:

<table class="table table-striped table-hover">
                            <thead>
                                <tr>

                                    <th class="table-item-title table-item-w30">Staff Name</th>
                                    <th class="table-item-title table-item-w20">Component</th>
                                    <th class="table-item-title table-item-w20">Component Amount</th>
                                    <th class="table-item-title table-item-w10">Tax</th>
                                    <th class="table-item-title table-item-w10">Loan</th>
                                    <th class="table-item-title table-item-w10">Leave</th>

                                </tr>
                            </thead>
                            <tbody>
                                <tr data-ng-repeat="item in payrollByIdItem">
                                    <td>{{item.StaffName}}</td>
                                    <td>{{item.SalaryComponentName}}</td>
                                    <td>{{item.Amount}}</td>
                                    <td>{{item.TaxAmount}}</td>
                                    <td>{{item.LoanAmount}}</td>
                                    <td>{{item.LeaveAmount}}</td>
                                </tr>

                            </tbody>
                        </table>

This is called pivoting. You'll find numerous questions in SO about this, eg [this](http://stackoverflow.com/questions/167304/is-it-possible-to-pivot-data-using-linq). Just search for `LINQ pivot` — Panagiotis Kanavos, Jun 10 '15 at 13:34
Hello @PanagiotisKanavos I went through the examples. They are static, mine is dynamic. — codegrid, Jun 11 '15 at 07:34
This doesn't really matter - you can return a dictionary with values or a dynamic object, instead of an object with properties. The principle is the same - group by the pivot column (StaffName) then use aggregates for each column you want to return in each group eg. ` dict["Amount"]=g.Where(it=>it.Component=="Basic").Sum(item=>item.Amount)`. If by "dynamic" you mean the kind of columns isn't known in advance - you can group the grouped values again over `Component` to get aggregates for each component value. This will get ugly though - how many columns will you add to the table? — Panagiotis Kanavos, Jun 11 '15 at 08:09
Hello @PanagiotisKanavos I followed what you said and was able to achieve it but it still remain how to display it using angularjs. — codegrid, Jun 11 '15 at 19:33

Changing Rows to Columns in LINQ

0 Answers0