CODEWITHSUNDEEP
c#asp.netlinq-to-sql
DDR
DDR

Reputation: 1322

How to validate dates falling not occupied in database

What I want to check date ranges falling in my provided date or not. For example I have a table with "FROM" and "TO" dates.

I just want to check that given date doesn't fall in dates in database.

Data in Database

FromDate  and TODate 

1 Aug 2012  ------  4 Aug 2012

5 Aug 2012 ------   11 Aug 2012

12 Aug 2012 ------  15 Aug 2012

Example From and To Dates 

FromDate      and      ToDate 

1 Aug 2012 ------   2 Aug 2012  **Should Return INVALID**

5 Aug 2012  ------  11 Aug 2012 **Should Return INVALID**

10 Aug 2012 ------   10 Aug 2012  **Should Return VALID**

15 Aug 2012  ------ 15 Aug 2012 **Should Return INVALID**

Can someone give any idea?

I am using LINQ to SQL in querying from database.

EDIT: just want to make sure that my TO and From dates dont conflicts with the dates in Database

Upvotes: 0

Views: 137

Answers (1)

Gábor Plesz
Gábor Plesz

Reputation: 1223

The fact that the intervals start and end point is correct, do not check it. We examine the existing interval intersects whether the interval in question:

void Main()
{

var ExistIntervals = new HashSet<Interval>();
//1 Aug 2012 4 Aug 2012
//5 Aug 2012 11 Aug 2012
//12 Aug 2012 15 Aug 2012
ExistIntervals.Add(new Interval { From = new DateTime(2012, 8, 1), 
                                  To = new DateTime(2012, 8, 4) });
ExistIntervals.Add(new Interval { From = new DateTime(2012, 8, 5), 
                                  To = new DateTime(2012, 8, 11) });
ExistIntervals.Add(new Interval { From = new DateTime(2012, 8, 12), 
                                  To = new DateTime(2012, 8, 15) });

var QueryIntervals = new HashSet<Interval>();
//1 Aug 2012 2 Aug 2012 INVALID
//5 Aug 2012 11 Aug 2012 INVALID
//10 Aug 2012 10 Aug 2012 VALID
//15 Aug 2012 15 Aug 2012 INVALID
QueryIntervals.Add(new Interval { From = new DateTime(2012, 8, 1), 
                                  To = new DateTime(2012, 8, 2) });
QueryIntervals.Add(new Interval { From = new DateTime(2012, 8, 5), 
                                  To = new DateTime(2012, 8, 11) });
QueryIntervals.Add(new Interval { From = new DateTime(2012, 8, 10), 
                                  To = new DateTime(2012, 8, 10) });
QueryIntervals.Add(new Interval { From = new DateTime(2012, 8, 15), 
                                  To = new DateTime(2012, 8, 15) });

var result = QueryIntervals.Where( x=> !ExistIntervals.Any(
                                         y=>(y.From <= x.From && x.From <= y.To)
                                         || (y.From <= x.To && x.To<=y.To)
                                                          )
                                 );
result.Dump();                                                         

}

public class Interval
{
  public DateTime From { get; set; }
  public DateTime To { get; set; }
}

The result is an empty set.

(the result is an empty set, since 10 Aug 2012th in this interval: 5 Aug 2012 ------ 11 Aug 2012)

You can test with LinqPad.

Update:

Suppose that the table name "CompaitnDates", the context name "CompaitnContext" and to determine the interval of two variables: "checkFrom", "checkTo".

var checkFrom = new DateTime(2012, 8, 10);
var checkTo = new DateTime(2012, 8, 10);
var db = new CompaitnContext();

then the test may be as follows:

var isValid = !db.CompaitnDates.Any(
                                    y=>(y.From <= checkFrom && checkFrom <= y.To)
                                    || (y.From <= checkTo && checkTo<=y.To)
                                   );

Upvotes: 1

Related Questions