Check One Date is falling Between Date Range-Linq

问题:

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 range.

Eg: Assume one Employee already Applied Leave between 01/01/2015 to 05/01/2015,

i)if user again try to apply leave for 04/01/2015 to 07/01/2015,then i  need to block that one.
ii)if user again try to apply leave for 05/01/2015 to 05/01/2015,then i  need to block that one.

How can i find it using a Linq query

回答1:

Try this one:

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

回答2:

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

回答3:

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

public static bool Between(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.

回答4:

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

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