CODEWITHSUNDEEP
.netsql-serverlinqentity-frameworklambda
vmb
vmb

Reputation: 3028

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

Upvotes: 1

Views: 6066

Answers (5)

GirishBabuC
GirishBabuC

Reputation: 1379

enter image description here

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);

Upvotes: 0

codebased
codebased

Reputation: 7073

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.

Upvotes: 1

nZeus
nZeus

Reputation: 2504

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

Upvotes: 4

developer
developer

Reputation: 1625

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

Upvotes: 5

Dmitrij Kultasev
Dmitrij Kultasev

Reputation: 5745

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

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

Upvotes: 0

Related Questions