CODEWITHSUNDEEP
c#sqlasp.netlinqlambda
TheMonkeyMan
TheMonkeyMan

Reputation: 9152

Lambda between two dates explicit

I am in need of some wizards.

I have a table

Start                 End                   PersonID
-----------------------------------------------------
10/07/2017 00:00:00   18/07/2017 00:00:00   1
27/07/2017 00:00:00   27/07/2017 00:00:00   1
28/07/2017 00:00:00   28/07/2017 00:00:00   1
29/07/2017 00:00:00   29/07/2017 00:00:00   1
30/07/2017 00:00:00   30/07/2017 00:00:00   1

If I search for 

Date Start = 11/07/2017
Date End = 12/07/2017

Using this query:

DateTime start = new DateTime(2017,07,11,0,0,0,0,0);
DateTime end = start.AddDays(1);
DateTime[] days = new DateTime[end.Subtract(start).Days];

for (int i = 0; i < end.Subtract(start).Days; i++)
{
     var d = start.AddDays(i);
     days[i] = d;
}

IQueryable block = tmOpen1.Calendar.Where(x => days.All(y => y >= x.start && y <= x.end)).Select(x => new { ID = x.PersonID });`

I get a positive result for ROW 1 (10/07/2017 - 18/07/2017)

However If I apply it against the remaining rows e.g. Filter

Date Start = 28/07/2017
Date End = 29/07/2017

Then obviously this will fail. How Can I get this side of the search to work.

E.g. Either

  1. Take the first row and make it split out into individual rows
  2. Make the Individual rows return true if a Person has several true conditions.

I hope one of the geniuses here can help.

Upvotes: 2

Views: 6854

Answers (5)

suhail salim
suhail salim

Reputation: 19

modelclassList= modelclassList.Where(x => x.gf_expdate>DateTime.Now).ToList();

to check expiry date and save back list of model class

Upvotes: 0

Drag and Drop
Drag and Drop

Reputation: 2734

Simplier with the not valid time frame: 

DateTime start = new DateTime(2017, 07, 11, 0, 0, 0, 0, 0);
DateTime end = start.AddDays(1);

var results = tmOpen1.Calendar.
                    .Where( c => ! ( c.Start > end  ||  c.End < start) )
                    .Select(x => new { ID = x.PersonID } );

For DateTime start = new DateTime(2017, 07, 11, 0, 0, 0, 0, 0);
The result are: 

TEST 1: 11/07/2017 00:00:00
  Start:10/07/2017 00:00:00       End:18/07/2017 00:00:00       ID:1

For DateTime start = new DateTime(2017, 07, 28, 0, 0, 0, 0, 0);
The result are:

TEST 2: 28/07/2017 00:00:00
  Start:28/07/2017 00:00:00       End:28/07/2017 00:00:00       ID:1
  Start:29/07/2017 00:00:00       End:29/07/2017 00:00:00       ID:1

Upvotes: 0

Scrobi
Scrobi

Reputation: 1215

From understanding of your question you want to know when the Date Start or Date End is within a range of dates.

You can check Date Start is within the date range or the Date End is within the date range

Example:

List<DateRange> dates = new List<DateRange>();
dates.Add(new DateRange()
{
    StartDate = new DateTime(2017, 07, 10),
    EndDate = new DateTime(2017, 07, 18)
});

dates.Add(new DateRange()
{
    StartDate = new DateTime(2017, 07, 28),
    EndDate = new DateTime(2017, 07, 28)
});

DateRange search1 = new DateRange()
{
    StartDate = new DateTime(2017, 07, 11),
    EndDate = new DateTime(2017, 07, 12)
};

DateRange search2 = new DateRange()
{
    StartDate = new DateTime(2017, 07, 28),
    EndDate = new DateTime(2017, 07, 29)
};

var result1 =  dates.Where(x => search1.StartDate >= x.StartDate && search1.StartDate <= x.EndDate || 
                search1.EndDate <= x.StartDate && search1.EndDate >= x.EndDate);

var result2 = dates.Where(x => search2.StartDate >= x.StartDate && search2.StartDate <= x.EndDate ||
                search2.EndDate <= x.StartDate && search2.EndDate >= x.EndDate);

Upvotes: 0

DavidG
DavidG

Reputation: 118937

Seems like all you really need is something like this:

DateTime start = new DateTime(2017,07,11,0,0,0,0,0);
DateTime end = start.AddDays(1);

var results = tmOpen1.Calendar
    .Where(c => start <= c.end && end >= c.start)
    .Select(x => new { ID = x.PersonID });

Upvotes: 3

Alexandru Pupsa
Alexandru Pupsa

Reputation: 1868

If your interval starts or ends somewhere between a start and end date from the table, than it means it is overlapping and you should included in your result.

tmOpen1.Calendar.Where(x => (startDate >= x.start && startDate <= x.end) || (endDate >= x.start && endDate <= x.end)).Select(x => new { ID = x.PersonID });

So an interval 10.07 - 27.07 should give you the first 2 rows, right?

Or is the interval supposed to be fully enclosed between 2 dates in the table?

Upvotes: 2

Related Questions