Can you query total monthly amount paid by members from database table?

Question

Payment Table Contains PaymentID MemberID AmountPaid Date

I want to query sum of amount for each month individually using linq in C#. Can you help me with querying this?

Answer 1

var result = (Your db context).tableName
.GroupBy(g => new { g.MonthColumn})
.Select(x => new
{
    x.Key.MonthColumn,
    amount = x.Sum(c => c.amount )
}).OrderBy(o => o.MonthColumn)
.ToList();

try this way it will help you to find your result

Answer 2

Good Question!

If your database has multiple year and multiple record in every month per member

   var all = _context.PaymentTable.ToList();
        var min = all.Min(f => f.Date).Year;
        var max = all.Max(f => f.Date).Year;
        List<PaidQueryViewModel> paidlist = new List<PaidQueryViewModel>();
        for (int i = min; i < max; i++)
        {
            for (int m = 1; m < 11; m++)
            {
                PaidQueryViewModel paid = new PaidQueryViewModel();
                 var start = new DateTime(i, m, 1);
                 var end = start.AddMonths(1).AddMinutes(-1);
                string Month = start.ToString("MMM-yyyy"); // if you want string month name
                var amount = all.Where(f => f.Date >= start && f.Date <= end).Sum(f => f.AmountPaid);
                paid.Month = start;
                paid.Amount = amount;
                paidlist.Add(paid);
            }
        }


    public class PaidQueryViewModel 
    {
        public DateTime Month { get; set; }
        public decimal Amount { get; set; }
    }