Multiple group data

Question

I have a dataset as shown below from the Payment table

 DateIssue | Amount | CoursePaidForMonth | 2/3/2011 9:54:07 PM | 2000.00| 2 2/27/2011 2:22:58 PM | 80.00| 2 3/5/2011 11:14:56 PM | 80.00| 3 3/27/2011 2:22:58 PM | 80.00| 2 2/8/2011 6:32:45 PM | 80.00| 2 

I would like to do two grouping sets for the data above:

• The closing date is 27 from each month, so I would like to group all the data from 27 to 26 of the next month into a group. This succeeds with gName . NO PROBLEM!! Check the output as the image below.

   DateIssue AppendCommToMonthΞΞ CoursePaidForMonthΞΞ TotalAmountΞΞ 2/8/2011 6:32:45 PM 2 2 2080.00 2/27/2011 2:22:58 PM 3 2 160.00 3/27/2011 2:22:58 PM 4 2 80.00 

• Now I would like to do another grouping on the same query. I would also like to group CoursePaidForMonth . The aforesaid means that the final result should display 4 rows of records instead of 3, it is grouped from 27 this month to 26 of the next month into a group, as well as CoursePaidForMonth . How to do it?

   DateIssue AppendCommToMonthΞΞ CoursePaidForMonthΞΞ TotalAmountΞΞ 2/8/2011 6:32:45 PM 2 2 2080.00 2/27/2011 2:22:58 PM 3 2 80.00 3/5/2011 11:14:56 PM 3 3 80.00 3/27/2011 2:22:58 PM 4 2 80.00 

My code is here:

 var result = from p in db.Payments join soi in db.SaleOrderItems on p.ReferenceId equals soi.Id join ct in db.CourseByTutors on soi.InventoryOrCourseId equals ct.CourseId where p.PayType == (int)PayTypes.PayCourseFee && ct.TutorId == tutorId let gName = (p.DateIssue.Value.Day < 27) ? (p.DateIssue.Value.Month) : (p.DateIssue.Value.Month % 12 + 1) group p by new { gName} into g select new { DateIssue = g.Select(x => x.DateIssue).First(), AppendCommForWhichMonth = g.Key.gName, CoursePaidForMonth = g.Select(x => x.CoursePaidForMonth).First(), TotalAmount = g.Sum(x => x.Amount), }; 

I ask for advice ..

Answer 1

You need to include CoursePaidForMonth in your key. Something like that:

 var query = from p in db.Payments join soi in db.SaleOrderItems on p.ReferenceId equals soi.Id join cbt in db.CourseByTutors on soi.InventoryOrCourseId equals cbt.CourseId where p.PayType == (int)PayTypes.PayCourseFee && cbt.TutorId == tutorId orderby p.DateIssue let AppendCommForWhichMonth = p.DateIssue.Month + p.DateIssue.Day < 27 ? 0 : 1 group p by new { AppendCommForWhichMonth, p.CoursePaidForMonth } into g select new { g.First().DateIssue, g.Key.AppendCommForWhichMonth, g.Key.CoursePaidForMonth, TotalAmount = g.Sum(p => p.Amount), };