How to get previous and next months records in linq

Question

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; }

Hint: rather than querying on the month, try querying on the date range itself, i.e. "on or after X and before Y". — Jon Skeet, Jan 31 '22 at 10:35
Instead of using `<` or `>` try using `==` operator in your predicate. Then you will have to tackle the issue of comparing the year side of the date. — Jamshaid K., Jan 31 '22 at 10:40
This is a very well known problem, aka "the perpetual calendar". E.g. when no data is available you get blank results — usr-local-ΕΨΗΕΛΩΝ, Jan 31 '22 at 10:43
Please, can you elaborate the question with additional information on the query you are doing, the expected results and a little insight on the data model (model classes)? — usr-local-ΕΨΗΕΛΩΝ, Jan 31 '22 at 10:46
try this query : select CreatedDate as Month ,sum(Amount) as Value , Count(CreatedDate) as Quantity from ... where CreatedDate between urdate-1 and urdate+1 Groub by CreatedDate — Amjad S., Jan 31 '22 at 10:50
@AmjadSaab : Could you please please provide the sql in LINQ query ? — Thyagi9510, Jan 31 '22 at 10:54
@usr-local-ΕΨΗΕΛΩΝ I have added the model. I need to get previous month & next month records based on the month selected from drop down, Example If the selected month is January 2022, i need to get records for December 2021 & February 2022. — Thyagi9510, Jan 31 '22 at 11:10

Amjad S. · Answer 1 · 2022-01-31T12:05:12.097

-1

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();

edited Jan 31 '22 at 12:05

answered Jan 31 '22 at 11:10

Amjad S.

1,196
1
4
16

i didnt test the query , just trying to help hopefully it is helpful – Amjad S. Jan 31 '22 at 11:12
In the where condition "urdate-1" will provide the previous date records ,I need records from previous month , current month & next month – Thyagi9510 Jan 31 '22 at 11:14
i told you to check how to parse the date to delete one month from it – Amjad S. Jan 31 '22 at 11:30
DateTime now = DateTime.Now; DateTime thisMonth = new DateTime(now.Year, now.Month, 1); DateTime lastMonth = thisMonth.AddMonths(-1); – Amjad S. Jan 31 '22 at 11:35

score -1 · Answer 2 · answered Jan 31 '22 at 13:44

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

How to get previous and next months records in linq

2 Answers2