CODEWITHSUNDEEP
c#linqasp.net-core
Anthony
Anthony

Reputation: 19

check a List of values in db and retrieve items that fall under a Date Time Month Property

Please guys, What i want to achieve is to get the previous 6 month back and search through my Transactions Table and retrieve a list of transactions that fall under each month property of Date Time and sum thier amount.

forexample. the current date is 04/03/2020 the last 6 months date becomes 02/20,01/2020,12/2019,11/2019,10/2019,09/2019 now i want to search through a table transactions which has a DateTime Property DateCreated and retrieve all the transactions that occurred within each month and sum all their amount.

ResponseCode 00 means successful payment what i have tried.


 List<DateTime> months = new List<DateTime>();
    List<double> MonthSum= new List<Double>();        
            DateTime[] lastSixMonths = Enumerable.Range(0, 6).Select(i => DateTime.Now.AddMonths(-i)).ToArray();

foreach (var month in lastSixMonths)
{
    var monthString = month.ToString("MM/yyyy");
            var trans = await _context.Transactions.Where(c => monthString.Contains(c.DateCreated.ToString()) && c.ResponseCode == "00").Select(c => c.Amount).ToListAsync();
            var sum = trans.Sum();
            MonthSum.Add(sum);

}

something seems to be wrong with how am doing this. please help

Upvotes: 0

Views: 59

Answers (2)

LouraQ
LouraQ

Reputation: 6881

The DateTime field is displayed as "yyyy-MM-dd" in linq, so you need to change month to "yyyy-MM" for judgment.

In the where condition, the 'Contains' condition needs to be exchanged.

        List<DateTime> months = new List<DateTime>();
        List<double> MonthSum = new List<Double>();
        DateTime[] lastSixMonths = Enumerable.Range(0, 6).Select(i => DateTime.Now.AddMonths(-i)).ToArray();
        foreach (var month in lastSixMonths)
        {
            var monthString = month.ToString("yyyy-MM");
            var trans = await _context.Transactions.Where(c => c.DateCreated.ToString().Contains(monthString) && c.ResponseCode == "00").Select(c => c.Amount).ToListAsync();
            var sum = trans.Sum();
            MonthSum.Add(sum);

        }

Upvotes: 0

Alexander
Alexander

Reputation: 46

I hope that's what you need: 

var fromDate = DateTime.Now.AddMonths(-6);
var sumByMonth = _context.Transactions.Where(d => d.CreateDate > fromDate)
                    .GroupBy(d => d.CreateDate.Month)
                    .Select(d => new { Month = d.Key, Sum = d.Sum(docs => docs.Amount) })
                    .ToDictionary(a => a.Month , b => b.Sum);

Upvotes: 1

Related Questions