Take Last VchNo and Group by Month and Sum Total Amount in Entity Framework

Question

public partial class FeesVoucher
    {
        public long Auto_id { get; set; }
        public string VoucherNo { get; set; }
        public string StudentID { get; set; }
        public string SessionID { get; set; }
        public string ClassID { get; set; }
        public string SectionID { get; set; }
        public Nullable<System.DateTime> IssueDate { get; set; }
        public Nullable<System.DateTime> DueDate { get; set; }
        public Nullable<System.DateTime> FeesDate { get; set; }
        public string FeeMonth { get; set; }
        public string FeeYear { get; set; }
        public Nullable<decimal> RegistrationFee { get; set; }
        public Nullable<decimal> AdmissionFee { get; set; }
        public Nullable<decimal> SecurityDeposit { get; set; }
        public Nullable<decimal> TutionFee { get; set; }
        public Nullable<decimal> Fine_Late_Fee { get; set; }
        public Nullable<decimal> TransportCharges { get; set; }
        public Nullable<decimal> Lab_ExaminationFee { get; set; }
        public Nullable<decimal> AnnualCharges { get; set; }
        public Nullable<decimal> BoardPECFee { get; set; }
        public Nullable<decimal> OtherCharges { get; set; }
        public Nullable<decimal> TotalAmount { get; set; }
        public string FeesStatus { get; set; }
        public string PaidType { get; set; }
        public string CampusID { get; set; }
        public string FeesID { get; set; }
        public string VoucherMessages { get; set; }
        public string CreatedBy { get; set; }
        public Nullable<System.DateTime> CreatedDate { get; set; }
        public string LastUpdateBy { get; set; }
        public Nullable<System.DateTime> LastUpdated { get; set; }
        public string EditMsg { get; set; }
        public string IsEdit { get; set; }
        public string RevAcc { get; set; }
        public string RecAcc { get; set; }
    }

using (Entities db = new Entities())
                    {

                        var dt = (from stu in db.StudentsInfoes
                                  join CampusS in db.CampusSetups on stu.CampusID equals CampusS.CampusID
                                  join stuclass in db.StudentClassInfoes on stu.StudentID equals stuclass.StudentID
                                  join sectionID in db.ClassesSections on stuclass.ClassID equals sectionID.ClassID
                                  join classs in db.ClassSetups on stuclass.ClassID equals classs.ClassID
                                  join SessS in db.SessionsSetups on stuclass.SessionID equals SessS.SessionID
                                  join FeeV in db.FeesVouchers on stu.StudentID equals FeeV.StudentID 
                                  
                                  

                                  select new
                                  {

                                      StudentID = stu.StudentID,
                                      FullName = stu.FullName,
                                      FatherName = stu.FatherName,
                                      StStatus = stu.Status,
                                      ClassStatus = stuclass.Status,
                                      CampusID = CampusS.CampusID,
                                      RollNo = stuclass.RollNo,
                                      ClassID = stuclass.ClassID,
                                      ClassDesc = classs.ClassDesc,
                                      SectionID = sectionID.SectionID,
                                      SectionDesc = sectionID.SectionDesc,
                                      VoucherNo = FeeV.VoucherNo,
                                      SessionID = SessS.SessionID,
                                      Months = FeeV.FeeMonth,
                                      RegistrationFee = FeeV.RegistrationFee,
                                      AdmissionFee = FeeV.AdmissionFee,
                                      AnnualCharges = FeeV.AnnualCharges,
                                      SecurityDeposit = FeeV.SecurityDeposit,
                                      Fine_Late_Fee = FeeV.Fine_Late_Fee,
                                      BoardPECFee = FeeV.BoardPECFee,
                                      Lab_ExaminationFee = 
                                      FeeV.Lab_ExaminationFee,
                                      OtherCharges = FeeV.OtherCharges,
                                      TutionFee = FeeV.TutionFee,
                                      IssueDate = FeeV.IssueDate,
                                      DueDate = FeeV.DueDate,
                                      
                                      TotalAmount = FeeV.TotalAmount,
                                      FeeStatus = FeeV.FeesStatus,
                                      FeeYear = FeeV.FeeYear,
                                      FeesID = FeeV.FeesID,

                                  }

                              ).Where(x => x.FeeStatus.Equals("unpaid")).Select(x => new {x.VoucherNo,x.FullName,x.IssueDate,x.DueDate,x.Months,x.TotalAmount }).Distinct().ToList();

This query perfectly work without group and sum as shown in picture ===> Without Group and Sum Picture after multiple tables joining this query build result like this

VchNo	Student Name	Month	Total Amount
0001	aa	Jan	2000
0002	aa	Feb	2000
0003	aa	Mar	2000
0004	bb	Jan	2000
0005	bb	Feb	2000
0006	bb	Mar	2000

But I want to modify my query to achieve group and sum as show in this picture ===>With Group and Sum Picture but I want to take last VchNo and group by month and sum total amount like this

VchNo	Student Name	Month	Total Amount
0003	aa	Jan,Feb,Mar	6000
0006	bb	Jan,Feb,Mar	6000

what will be the query for entity framework 6.0 all answer will be appreciated

Answer 1

See following :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;


namespace ConsoleApplication
{
    public class Program
    {
        
        public static void Main(string[] args)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("VchNo", typeof(string));
            dt.Columns.Add("Student Name", typeof(string));
            dt.Columns.Add("Month", typeof(string));
            dt.Columns.Add("Total Amount", typeof(int));

            dt.Rows.Add(new object[] { "0001", "aa", "Jan", 2000 });
            dt.Rows.Add(new object[] { "0002", "aa", "Feb", 2000 });
            dt.Rows.Add(new object[] { "0003", "aa", "Mar", 2000 });
            dt.Rows.Add(new object[] { "0004", "bb", "Jan", 2000 });
            dt.Rows.Add(new object[] { "0005", "bb", "Feb", 2000 });
            dt.Rows.Add(new object[] { "0006", "bb", "Mar", 2000 });

            DataTable dt2 = dt.Clone();

            var groups = dt.AsEnumerable().GroupBy(x => x.Field<string>("Student Name")).ToList();

            foreach (var group in groups)
            {
                dt2.Rows.Add(new object[] {
                    group.Select(x => x.Field<string>("VchNo")).LastOrDefault(),
                    group.Key,
                    string.Join(",", group.Select(XmlReadMode => XmlReadMode.Field<string>("Month"))),
                    group.Sum(x => x.Field<int>("Total Amount"))
                });
            }

        }
    }

}

---

Using Entity :

    class Program
    {
        static void Main(string[] args)
        {

            using (Entities db = new Entities())
            {

                var dt = (from stu in db.StudentsInfoes
                          join CampusS in db.CampusSetups on stu.CampusID equals CampusS.CampusID
                          join stuclass in db.StudentClassInfoes on stu.StudentID equals stuclass.StudentID
                          join sectionID in db.ClassesSections on stuclass.ClassID equals sectionID.ClassID
                          join classs in db.ClassSetups on stuclass.ClassID equals classs.ClassID
                          join SessS in db.SessionsSetups on stuclass.SessionID equals SessS.SessionID
                          join FeeV in db.FeesVouchers on stu.StudentID equals FeeV.StudentID

                          select new
                          {

                              StudentID = stu.StudentID,
                              FullName = stu.FullName,
                              FatherName = stu.FatherName,
                              StStatus = stu.Status,
                              ClassStatus = stuclass.Status,
                              CampusID = CampusS.CampusID,
                              RollNo = stuclass.RollNo,
                              ClassID = stuclass.ClassID,
                              ClassDesc = classs.ClassDesc,
                              SectionID = sectionID.SectionID,
                              SectionDesc = sectionID.SectionDesc,
                              VoucherNo = FeeV.VoucherNo,
                              SessionID = SessS.SessionID,
                              Months = FeeV.FeeMonth,
                              RegistrationFee = FeeV.RegistrationFee,
                              AdmissionFee = FeeV.AdmissionFee,
                              AnnualCharges = FeeV.AnnualCharges,
                              SecurityDeposit = FeeV.SecurityDeposit,
                              Fine_Late_Fee = FeeV.Fine_Late_Fee,
                              BoardPECFee = FeeV.BoardPECFee,
                              Lab_ExaminationFee =
                              FeeV.Lab_ExaminationFee,
                              OtherCharges = FeeV.OtherCharges,
                              TutionFee = FeeV.TutionFee,
                              IssueDate = FeeV.IssueDate,
                              DueDate = FeeV.DueDate,

                              TotalAmount = FeeV.TotalAmount,
                              FeeStatus = FeeV.FeesStatus,
                              FeeYear = FeeV.FeeYear,
                              FeesID = FeeV.FeesID,

                          }

                      ).Where(x => x.FeeStatus.Equals("unpaid")).Select(x => new { x.VoucherNo, x.FullName, x.IssueDate, x.DueDate, x.Months, x.TotalAmount }).Distinct().ToList();

                var results = dt.GroupBy(x => x.FullName).Select(x => new
                {
                    VchNo = x.Last().VoucherNo,
                    Name = x.Key,
                    Months = string.Join(",", x.Select(y => y.Months)),
                    Amount = x.Sum(y => y.TotalAmount)

                }).ToList();
            }
        }
    }
    public class Entities : IDisposable
    {
        public List<CampusSetups> CampusSetups { get; set; }
        public List<ClassSetups> ClassSetups { get; set; }
        public List<ClassesSections> ClassesSections { get; set; }
        public List<FeesVoucher> FeesVouchers { get; set; }
        public string SectionsSetups { get; set; }
        public List<StudentsInfoes> StudentsInfoes { get; set; }
        public List<StudentClassInfoes> StudentClassInfoes { get; set; }
        public List<SessionsSetups> SessionsSetups { get; set; }

        public void Dispose()
        {
        }
    }
    public class ClassSetups
    {
        public string ClassID { get; set; }
        public string ClassDesc { get; set; }
    }
    public class ClassesSections
    {
        public string ClassID { get; set; }
        public string SectionDesc { get; set; }
        public string SectionID { get; set; }
    }
    public class SessionsSetups
    {
        public string SessionID { get; set; }
    }
    public class StudentClassInfoes
    {
        public string StudentID { get; set; }
        public string ClassID { get; set; }
        public string SessionID { get; set; }
        public string RollNo { get; set; }
        public string Status { get; set; }
    }
    public class CampusSetups
    {
        public string CampusID { get; set; }
    }
    public class StudentsInfoes
    {
        public string CampusID { get; set; }
        public string StudentID { get; set; }
        public string FatherName { get; set; }
        public string FullName { get; set; }
        public string Status { get; set; }
    }
    public partial class FeesVoucher
    {
        public long Auto_id { get; set; }
        public string VoucherNo { get; set; }
        public string StudentID { get; set; }
        public string SessionID { get; set; }
        public string ClassID { get; set; }
        public string SectionID { get; set; }
        public Nullable<System.DateTime> IssueDate { get; set; }
        public Nullable<System.DateTime> DueDate { get; set; }
        public Nullable<System.DateTime> FeesDate { get; set; }
        public string FeeMonth { get; set; }
        public string FeeYear { get; set; }
        public Nullable<decimal> RegistrationFee { get; set; }
        public Nullable<decimal> AdmissionFee { get; set; }
        public Nullable<decimal> SecurityDeposit { get; set; }
        public Nullable<decimal> TutionFee { get; set; }
        public Nullable<decimal> Fine_Late_Fee { get; set; }
        public Nullable<decimal> TransportCharges { get; set; }
        public Nullable<decimal> Lab_ExaminationFee { get; set; }
        public Nullable<decimal> AnnualCharges { get; set; }
        public Nullable<decimal> BoardPECFee { get; set; }
        public Nullable<decimal> OtherCharges { get; set; }
        public Nullable<decimal> TotalAmount { get; set; }
        public string FeesStatus { get; set; }
        public string PaidType { get; set; }
        public string CampusID { get; set; }
        public string FeesID { get; set; }
        public string VoucherMessages { get; set; }
        public string CreatedBy { get; set; }
        public Nullable<System.DateTime> CreatedDate { get; set; }
        public string LastUpdateBy { get; set; }
        public Nullable<System.DateTime> LastUpdated { get; set; }
        public string EditMsg { get; set; }
        public string IsEdit { get; set; }
        public string RevAcc { get; set; }
        public string RecAcc { get; set; }
    }

Answer 2

Perhaps something like

collection.GroupBy(x => x.StudentName).Select(g =>
  new{
    g.Last().VchNo,
    g.Last().StudentName,
    Month = string.Join(',', g.Select(gg => g.Month)),
    TotalAmount = g.Sum(gg => g.TotalAmount)
  }
);

I have guessed the property names because you didn't include them. Modify the code to substitute your actual entity names