Check One Date Is Falling Between Date Range-linq Query

i am storing Leave Information of an employee in database.From and To Date will be stored in DB.I want to block employee to apply leave,when it comes in already applied leaves ran

Solution 1:

you can try as below

return (from t1 in db.Employee where ( empid == t1.EmplyeeId &&
date1 >= t1.LeaveStart && date2 <= t1.LeaveEnd))

you can try as below,detailed answer

int cntleaves = (from values in dbcontext.User_master
                       where values.iUser_id == Userid &&
                               ((values.dtStart_date >= Startdate &&
                                values.dtEnd_date <= Enddate)
                                 ||
                                 (values.dtStart_date <= Startdate &&
                                values.dtEnd_date <= Enddate))
                       select values).ToList().Count();

        if (cntleaves > 0) {
            ViewBag.Message = "You have already applied for leaves !";            
        }

Solution 2:

Try this one:

var startDate = newDateTime(2015, 01, 04);
var endDate = newDateTime(2015, 01, 07);
if (_context.AppliedLeaves.Any(x => 
     x.UserId == userId &&
     ((startDate >= x.StartDate && startDate <= x.EndDate) || 
      (endDate >= x.StartDate && endDate <= x.EndDate)))
{
    throwException("You cannot apply this period");
}

Solution 3:

Another better approach is create an extension method to the DateTime class as:

publicstaticboolBetween(this DateTime input, DateTime date1, DateTime date2){
    return (input > date1 && input < date2);
}

And then you can use this with linq predicate where clause.

if ( leaves.Where( l => l.empId == empId && dateApplied.Between(l.from, l.to)).Any()) { ... error... }

PS: It is just pseudo code for your direction.

Solution 4:

IFEXISTS (
SELECT * FROM table WHERE user = @user AND (
@newStartDate BETWEEN appliedStartDate AND appliedEndDate

)
PRINT 'Can not apply'
ELSE PRINT 'Can apply'

Solution 5:

This post is related to the querying between two columns within range that might help some developers.

1st step: Get into list

var Price = GetLoad().ToList();

2nd step: your search items

string code = "PLUS";

var Total_Squares = 101: //value need to be checked within integer range

DateTime Checkdate = Convert.ToDateTime("2012-10-03"); //value need to be checked within date

Query1 to check the range of integers along with the string value

var filteredCodeWithSquare = Price.Where(C => C.Code.StartsWith(code))

.FirstOrDefault(r => r.FromSquare <= Total_Squares && r.ToSquare >= Total_Squares); a Query2 to check the range of date

var daterange = Price.FirstOrDefault(d => d.FromIntall

<= Checkdate && d.ToInstall >= Checkdate);