Search dates in linq in C#

Question

0.00/5 (No votes)

See more:

i have employee salary modification history

and need to find what is the salary for the searched period for example

salary have been modified in the following dates

08/03/2017 it was 2200 US and became 2300
24/12/2018 it was 2300 US and became 2400
25/12/2018 it was 2400 US and became 2300
17/02/2019 it was 2300 US and became 2400

if i searched for salary at the period from 26/01/2017 to 25/02/2017 i expect the result to be 2200

if i searched for salary at the period from 26/02/2017 to 25/03/2017 i expect the result to be 2300

if i searched for salary at the period from 26/11/2018 to 25/12/2017 i expect the result to be 2400

if i searched for salary at the period from 26/12/2018 to 25/01/2019 i expect the result to be 2300

the problem with my c# code that it return the whole dates from 08/03/2017 to 17/02/2019

at any period from the searched periods above

i need the above expected results what can i do to obtain correct result

What I have tried:

C#

var dbitem = db.tblSalaryHistories.Where(c => c.Employee_ID == employeeId && (
                         (startDate <= c.Update_Date && endDate >= c.Update_Date) ||
                         (startDate >= c.Update_Date && endDate <= c.Update_Date) ||
                         (startDate <= c.Update_Date) ))

Posted 10-Mar-19 1:36am

TheSniper105

Updated 10-Mar-19 1:52am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2019-03-10T01:53:00

The problem is that your code does exactly what you ask of it: returns all data.
This is because you have OR conditions which amount to:

(a <= b && c >= b) || (a >= b && c <= b) || (a <= b)

So, if at any time, a < b, the whole condition is true. The first condition is irrelevant because the third will always be true when the first is.

So if you search for a startDate of 26-01-2017, if will automatically match all rows in your sample data, and return them.

If you provide an order - perhaps by c.UpdateDate ascending, you could use FirstOrDefault instead of Where to return a single row, but even then, I think you want to look rather more closely at your condition and probably reduce it to

C#

startDate <= c.Update_Date && endDate >= c.Update_Date