How to get previous and next months records in linq-CodePudding

I am using EF core 6, I need to get the records for previous month & next month using LINQ . There is a dropdown list with months name , I will be sending month number of the selected month to the LINQ condition

If I select January ,it should get results of December full month records from previous year & current year February whole month records.

When i use the below predicate, It doesn't provide proper results

 Func<Products, bool>? predicate = i => i.CreatedDate.Month > month   1 && i.CreatedDate.Month < month - 1;

Product
ID Name CreatedDate   Amount
1  John 21/12/2021    1000   
2  Mark 10/12/2021    2000  
3  Steve 02/01/2022   3000 
4  Arun 21/01/2022    4000 
5  Adi 10/02/2022     5000
6  Sanjay 11/02/2022  6000 
7  Sanjay 14/02/2022  7000

I also need to get the total amount of each month based on the month selected

If I select January month the result should be

Month       Value    Quantity
December    3000      2
January     7000      2
February    18000     3

I am new to LINQ, please provide suggestions

Model -- Product
 public int Id { get; set; }
 public string? Name { get; set; }
 public int? Amount{ get; set; }
public DateTime CreatedDate { get; set; }

CodePudding user response：

the linq query will look somthing like this:

public class ResultLine{

   public ResultLine() { }

   public string Month {get; set;}
   public int Value {get; set; }
   public int Quantity {get; set;}
}

List<ResultLine> result = Lines
    .GroupBy(l => l.CreatedDate)
    .Where(l=> t.CreatedDate >= urdate-1 && ...) // check how to parse dates so you can delete and add one month to compare dates
   .Select(cl => new ResultLine
            {
                Month  = cl.CreatedDate,
                Value  = cl.Sum(c => c.Amount),
                Quantity = cl.Count(),
            }).ToList();

CodePudding user response：

Revamping @KiranJoshi's answer

public List<ProductQty> GetData(int month)
        {
            var now = DateTime.Now.Date;
            var startDate = DateTime.Now.AddMonths(-1);
            // https://stackoverflow.com/a/41765316/471213
            if (startDate.Day > 1)
                startDate = startDate.AddDays(-(startDate.Day-1));
            var endDate = DateTime.Now.AddMonths(1);
            // https://stackoverflow.com/a/4078999/471213
            endDate = endDate.AddDays(1-endDate.Day).AddMonths(1).AddDays(-1);


            var query = db.product.Where(x => (x.CreatedDate >= startDate&& x.CreatedDate <= endDate));

            var gquery = query.GroupBy(c => c.CreatedDate.Month).Select(x =>
            new ProductQty
            {
                Amount = x.Sum(x => x.Amount),
                Total = x.Count(),
                Month = x.Key
            }).ToList();

            var ret = new List<ProductQty>();

            for(var date = startDate; date <= endDate; date = date.AddMonths(1)) {
                var month = gquery.FirstOrDefault(x=>x.Month == date.Month && x.Year == date.Year);
                ret.add(month);
            }
            return ret;
        }


public struct ProductQty
    {
        public decimal Amount { get; set; }
        public decimal Total { get; set; }
        public int Month { get; set; }
    }

This solution (not compiled, not tested, intended to give a designer's idea) overcomes the perpetual calendar problem.

You don't know for sure if you have any data for a particular month. E.g. think of a business closed all August, so you have absolutely no records.

I chose to use a struct and use FirstOrDefault in a second for cycle in order to always return a value in case the database's result set contains no data. If you assume the database contains no data for a month, the number of returned rows is 2 than 3.

What you really want is to return zeroes. SQL databases do not generally allow JOINing a table with a perpetual calendar

CodePudding user response：

You can get your data using following query:

public List<ProductQty> GetData(int month)
        {
            int prevYear = DateTime.Now.Year;
            int nextYear = DateTime.Now.Year;
            int nextMonth = DateTime.Now.Month   1;
            int prevMonth = DateTime.Now.Month - 1;
            if (nextMonth > 12)
            {
                nextYear  = 1;
                nextMonth = 1;
            }
            if (prevMonth <= 0)
            {
                prevYear -= 1;
                prevMonth = 12;
            }

            DateTime prevMonthFirstDate = new DateTime(prevYear, prevMonth, 01);
            DateTime nextMonthEndDate = new DateTime(nextYear, nextMonth, DateTime.DaysInMonth(nextYear, nextMonth));

            var query = db.product.Where(x => (x.CreatedDate >= prevMonthFirstDate && x.CreatedDate <= nextMonthEndDate));

            var gquery = query.GroupBy(c => c.CreatedDate.Month).Select(x =>
            new ProductQty
            {
                Amount = x.Sum(x => x.Amount),
                Total = x.Count(),
                Month = x.Key
            }).ToList();

            return gquery;
        }

Model

public class ProductQty
    {
        public decimal Amount { get; set; }
        public decimal Total { get; set; }
        public int Month { get; set; }
    }

In this query you will get Month as number so convert it to month in html as per your requirement.