Insert in to DB if input doesn't fall within a range

Question

As a input i am getting startDate and endDate. I have to do a linq query from client side to check if there is any existing entry in DB within this time.

For example, in my DB if i have an entry with StartDate of 2018-01-05 and enddate of 2018-01-09 and user tries to put another entry with start date of 2018-01-04 and enddate of 2018-01-06 i should not allow that, because in my DB i have already an entry that is common within this time range. Here is my code

 var stDate = dbcontext.tables.Where( st => 
                             st.StartDate <= UserStartDate && st.EndDate >= UserEndDate && st.Status == "Queued" ||
                             st.StartDate >= UserStartDate && st.EndDate >= UserEndDate && st.Status == "Queued" ||
                             st.StartDate <= UserStartDate && st.EndDate <= UserEndDate && st.Status == "Queued" ||
                             st.StartDate >= UserStartDate && st.EndDate <= UserEndDate && st.Status == "Queued").ToList();
                if (stDate.Count >0 )
                {
                    //notification to user("There is an existing request within this time range");
                }
                else
                {
                    // do the rest operation
                }

But the problem is if someone tries to enter an entry from 2018-01-09 to 2018-01-10 it falls under 3 condition and application is not taking this entry. Any suggestion for me how can i improve my code?

I would always do filtering/checking like that in the Query itself. Retreiving the Data to your client to do filtering/checks there is asking to get issues liek Race conditoins, excessive Network load and what not. As for "find if data is within range" problem, that is a problem that vexes me too. — Christopher, Mar 07 '18 at 17:49
I don't think there can be any date range that doesn't fall in these conditions. — FaizanHussainRabbani, Mar 07 '18 at 18:05
You are right @FaizanRabbani, do you have any idea how can i allow user to input if their start and end date dosent fall in to the existing entry. .Staus i have to use Queued because there might my other entry within same time range but in different status. I am not concern about those status. — user2019, Mar 07 '18 at 18:29
@Shaif Do you only want both start and end to don't fall in existing range? Are these ranges okay: `2018-01-08` & `2018-01-15` `2018-01-02` & `2018-01-07` `2018-01-01` & `2018-01-15`? — FaizanHussainRabbani, Mar 07 '18 at 18:34

score 3 · Accepted Answer · edited Apr 03 '18 at 16:22

Your logic is a bit off. In all of your conditions, you compare StartDate to UserStartDate and EndDate to UserEndDate. Instead, you should be comparing the same thing in each condition. See below:

var stDate = dbcontext.tables
             .Where(st =>  st.Status == "Queued" 
               && (  st.StartDate >= UserStartDate && st.StartDate < UserEndDate   
                  || st.StartDate > UserStartDate  && st.StartDate <= UserEndDate  
                  || st.EndDate >= UserStartDate   && st.EndDate < UserEndDate     
                  || st.EndDate > UserStartDate    && st.EndDate <= UserEndDate )
              ).ToList();

Notice that the StartDate is compared to both UserStartDate and UserEndDatein the top 2 conditions and the EndDate is compared to both UserStartDate and UserEndDatein the bottom 2 conditions.

i should not allow that, because in my DB i have already an entry that is common within this time range.

That means that the only conditions you are worried about is if the UserStartDate and UserEndDate are on opposite sides of either your StartDate or EndDate as shown below.

If Either of these conditions are met, you will always be within the same date range.

Update:

The code above was updated as the OP indicated that the UserStartDate could actually be the same as the EndDate or the UserEndDate could be the same as the StartDate but don't count as overlapping data.

Update2:

As the OP pointed out, I forgot about the case where both the UserStartDate and UserEndDate were within the date range. Per the OP's comment, that line is the following:

st.StartDate <= UserStartDate && st.EndDate >= User.EndDate && st.Status == "Queued"

Than you @Sudsy1002 but the problem if i follow your code user can't create entry for start and end date lesser then DB. for example if someone try to add an entry 2018-01-04 to 2018-01-05 it will prevent them — user2019, Mar 07 '18 at 18:27
@Shaif I've editted so that the `StartDate` can be the same value as the `UserEndDate` and the `EndDate` can be the same value as the `UserStartDate` but cannot satisfy both (as that would result in overlapping data). — Sudsy1002, Mar 07 '18 at 18:39
You should use `.Any()` instead of `.ToList()` and a `.Count>0` afterwards for performance reasons, though. This way in a case where there are lots of lines in the DB you don't get millions of matches. — toornt, Mar 07 '18 at 18:40
Thank you so much @Sudsy1002 . Problem resolved. I have just added another check point for not to allow user add entry within the rime range. `st.StartDate <= UserStartDate && st.EndDate >= User.EndDate && st.Status == "Queued"` — user2019, Mar 07 '18 at 18:54

Insert in to DB if input doesn't fall within a range

1 Answers1