Is it possible to get the data from a range in an easy way in C#?

Question

I have a range of 2 dates given by my business: "we would like to have all data from July and 6 previous months". I create two dates:

_range1.MaxDateTime = new DateTime(today. Year, 7, 31);
_range1.MinDateTime = rangeLastDate.AddMonths(-6).AddDays(1);

Because the system will always perform the calculation on last 6 months. Last date is also always end of month. I have no problem to get the last day of the month. C# has the feature EndOfMonth() for this. But when I do so I always get the last day at midnight. when I create the DateTime() myself I also get a date a midnight.

In my code I use Entity Framework with LinQ. (old EF, not Core yet)

_range1_ShippedQuantities = DbContext.Job_Dtl
    .Where(j=> j.LastShipDate >= _range1.MinDateTime && j.LastShipDate <= _range1.MaxDateTime)

This is not correct because my last datetime is the 31 at midnight and not the first of next month at midnight. I should do

_range1_ShippedQuantities = DbContext.Job_Dtl
    .Where(j=> j.LastShipDate.Date >= _range1.MinDateTime.Date && j.LastShipDate.Date <= _range1.MaxDateTime.Date)

Or I can also manipulate the dates do force the time at 23:59:59... what I don't like. I still miss 1 second. I can add 999 milliseconds but i will still tell you I miss one millisecond. I can also add one day in a temp variable and use this temp variable in my LinQ query... yes but I have the feeling there is something better. How do you deal with these ranges?

But LinQ does not allow this. LinQ for entities cannot recognize the date.

Answer 1

As several people have commented on your question, the simplest and most common way to solve this problem is to choose an exclusive end date rather than an inclusive one.

var minDateInclusive = rangeLastDate.AddMonths(-6).AddDays(1);
var maxDateExclusive = rangeLastDate.AddDays(1);

DbContext.Job_Dtl
    .Where(j=> j.LastShipDate >= minDateInclusive 
        && j.LastShipDate < maxDateExclusive);