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

Question

I have a list of Employee Appraisal table, this table has TotalResult field, the values in this field are between 1 and 10. Another table Result Segmentation has the following columns:

Id int, Max double, Min double, Desc string

Let's say I have this 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

if the user chooses 70% and 40%, the query should show these employee appraisals:

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 queryable 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 it's acting like "And" , it should acting like "OR" but I didn't know how to write.

Answer 1

This was something that had been bugging me a while back, and the question just prompted me to dig into it a bit. .Where() will append conditions, but as you noted, with an AndAlso operation. To get EF and Linq to support an OrElse condition more dynamically you need to rebuild the expression tree a little to or the conditions together. Kudos to user743382's answer on Exception using OrElse and AndAlso expression methods

You'll need a couple classes to enable an expression visitor to line up the parameters for multiple expressions to be Or'd together. Something like:

private Expression<Func<EmployeeAppraisal, bool>> buildFilterExpression(IEnumerable<Segment> segments)
{
    Expression<Func<EmployeeAppraisal, bool>> exp = c => false;

    foreach (var segment in segments)
    {
        Expression<Func<EmployeeAppraisal, bool>> filter = x => x.TotalResult >= segment.Min && x.TotalResult <= segment.Max;
        exp = Expression.Lambda<Func<EmployeeAppraisal, bool>>(Expression.OrElse(exp.Body,
            new ExpressionParameterReplacer(filter.Parameters, exp.Parameters).Visit(filter.Body)), exp.Parameters);
    }

    return exp;
}

private class ExpressionParameterReplacer : ExpressionVisitor
{
    public ExpressionParameterReplacer(IList<ParameterExpression> fromParameters, IList<ParameterExpression> toParameters)
    {
        ParameterReplacements = new Dictionary<ParameterExpression, ParameterExpression>();
        for (int i = 0; i != fromParameters.Count && i != toParameters.Count; i++)
            ParameterReplacements.Add(fromParameters[i], toParameters[i]);
    }

    private IDictionary<ParameterExpression, ParameterExpression> ParameterReplacements
    {
        get;
        set;
    }

    protected override Expression VisitParameter(ParameterExpression node)
    {
        ParameterExpression replacement;
        if (ParameterReplacements.TryGetValue(node, out replacement))
            node = replacement;
        return base.VisitParameter(node);
    }
} 

Then in your EF Linq expression:

var rateSegs = _repositoryRateSeg.Query(x => rateSegId.Contains(x.Id)).ToList();

if (rateSeg.Any())
    query = query.Where(buildFilterExpression(rateSegs));

The ExpressionParameterReplacer and supporting classes accommodate OR-ing the different expression bodies together and ensuring that they are associating to the same expression parameter so that Linq will evaluate them correctly as a single expression.

Answer 2

Cross join can be one solution like:

    var rateSegIds = new int[] {2, 5}; //40% and 70%

    var result = from emp in EmployeeAppraisals
                 from segment in Segments.Where(x => rateSegIds.Contains(x.Id))
                 where emp.Total >= segment.Min && emp.Total <= segment.Max
                 select emp;