How to compare value of a field in a list to another value in another list in Where clause

Refresh

February 2019

Views

23 time

0

I have a list of Employee Appraisal Table, this table has TotalResutl field ,the values in this field are between 1 and 10 ,another table which is Result Segmentation has the following fields , Id int, Max double ,Min double ,and Desc as string

let say i have these data for Employee Appraisal :

EmpId  EmpName   TotalResult
---  -------   -----------
1      Jaims     1.5
2      Johny     8.3
3      Moon      5.6
4      Michle    7
5      Mariam    9
6      Kamel     4

Result Segmentation Values

Id   Max    Min   Desc
---  ---    ---   -----
1     3      1     ~ 30%
2     4      3     40%
3     5      4     50%
4     6      5     60%
5     7      6     70%
6     10     7     ~ 80%

Now , the user has a multi select list of the Rate Segmentation table

enter image description here

if the user choose 70% and 40%the query should show these employee appraisal

EmpId  EmpName   TotalResult
-----  -------   -----------
3      Moon      5.6
6      Kamel     4
4      Michle    7

i wrote this code

if (rateSegIds != null)
{
    var rateSegs = _repositoryRateSeg.Query(x => rateSegId.Contains(x.Id)).ToList();

    if (rateSeg.Any())
    {
        foreach (var segmentation in rateSeg)
        {
            query = query.Where(x => x.TotalResult > segmentation.Min &&  x.TotalResult <= segmentation.Max);       
        }
    }
}

rateSegIds : is the a list of integers hold the user selection rateSegs : contains the records from RateSegmataions table according to the list of Ids query is a Qureyable object of EmployeeAppraisal table

this code works only if the user choose one value from the list , if he/she choose multiple values , the query will return nothing.
because its acting like "And" , it should acting like "OR" but i didnt know how to write.

0 answers