Want to convert c# multiple Linq into one Linq query?

Question

I wrote the code with multiple if conditions and get list of Ids from each if statement. I want to write that whole code into one linq query if possible then let me know.

The issue is when we are running this code then can see our compiler runs hundreds line of query on output window. I just want to optimized. If you guys can help me. Following code for details:

if (queryObj.AgeCategory.Contains((int)AgeList._0to5))
{
    var list = queryRecord.Where(v => v.BuildYear <= yearNow && v.BuildYear > year5).Select(s => s.Id);
    Ids = Ids.Concat(list);
}
if (queryObj.AgeCategory.Contains((int)AgeList._6to10))
{
    var list = queryRecord.Where(v => v.BuildYear <= year5 && v.BuildYear > year10).Select(s => s.Id);
    Ids = Ids.Concat(list);
}
if (queryObj.AgeCategory.Contains((int)AgeList._11to15))
{
    var list = queryRecord.Where(v => v.BuildYear <= year10 && v.BuildYear > year15).Select(s => s.Id);
    Ids = Ids.Concat(list);
}
if (queryObj.AgeCategory.Contains((int)AgeList._16to20))
{
    var list = Ids.Concat(queryRecord.Where(v => v.BuildYear <= year15 && v.BuildYear > year20).Select(s => s.Id));
    Ids = Ids.Concat(list);
}
if (queryObj.AgeCategory.Contains((int)AgeList._21to25))
{
    var list = Ids.Concat(queryRecord.Where(v => v.BuildYear <= year20 && v.BuildYear > year25).Select(s => s.Id));
    Ids = Ids.Concat(list);
}
if (queryObj.AgeCategory.Contains((int)AgeList._26to30))
{
    var list = Ids.Concat(queryRecord.Where(v => v.BuildYear <= year25 && v.BuildYear > year30).Select(s => s.Id));
    Ids = Ids.Concat(list);
}
if (queryObj.AgeCategory.Contains((int)AgeList._31to35))
{
    var list = Ids.Concat(queryRecord.Where(v => v.BuildYear <= year30 && v.BuildYear > year35).Select(s => s.Id));
    Ids = Ids.Concat(list);
}
if (queryObj.AgeCategory.Contains((int)AgeList._36to40))
{
    var list = Ids.Concat(queryRecord.Where(v => v.BuildYear <= year35 && v.BuildYear > year40).Select(s => s.Id));
    Ids = Ids.Concat(list);
}
if (queryObj.AgeCategory.Contains((int)AgeList._Over41Years))
{
    var list = Ids.Concat(queryRecord.Where(v => v.BuildYear <= year40).Select(s => s.Id));
    Ids = Ids.Concat(list);
}
queryRecord = queryRecord.Where(v => Ids.Contains(v.Id));

I tested with following code but because of same column It fulfill first condition.

                    Ids = queryRecord.Where(st => ((queryObj.AgeCategory.Contains((int)AgeList._11to15)) ? st.BuildYear <= year10 && st.BuildYear > year15 : true) &&
                        ((queryObj.AgeCategory.Contains((int)AgeList._6to10)) ? st.BuildYear <= year5 && st.BuildYear > year10 : true))
                        .Select(s => s.Id);

Answer 1

I just added false in each case and add || between each statements and fixed this.

Ids = queryRecord.Where(st => 
                    ((queryObj.AgeCategory.Contains((int)AgeList._11to15)) ? st.BuildYear <= year10 && st.BuildYear > year15 : false) ||
                    ((queryObj.AgeCategory.Contains((int)AgeList._6to10)) ? st.BuildYear <= year5 && st.BuildYear > year10 : false)
                    ).Select(s => s.Id);

Answer 2

Another solution is to create a converter that transforms the BuildAge into an age category. This way you can simply let LINQ do the filtering:

private void FilterQueryRecord(IEnumerable<Record> queryRecord, ? queryObj)
{
  this.Ids = queryRecord
    .Where(item => queryObj.AgeCategory.Contains(ConvertBuildYearToAgeCategory(item.BuildYear)))
    .Select(item => item.Id)
    .ToList();
}

private int ConvertBuildYearToAgeCategory(int buildYear) 
{
  switch (buildYear)
  {
    case int value when value <= yearNow && value > year5: return (int) AgeList._0to5;
    case int value when value <= year5 && value > year10: return (int) AgeList._6to10;
    case int value when value <= year10 && value > year15: return (int) AgeList._11to15;
    case int value when value <= year15 && value > year20: return (int) AgeList._16to20;
    case int value when value <= year20 && value > year25: return (int) AgeList._21to25;
    case int value when value <= year25 && value > year30: return (int) AgeList._26to30;
    case int value when value <= year30 && value > year35: return (int) AgeList._31to35;
    case int value when value <= year35 && value > year40: return (int) AgeList._36to40;
    case int value when value < year40: return (int) AgeList._Over41Years;
    default: return (int) AgeList.Undefined
  }
}

Answer 3

Suppose the AgeList you are referring to was an enum like this:

public enum AgeList
{
    _0to5   = 0,
    _6to10  = 1,
    _11to15 = 2,
    _16to20 = 3,
    _21to25 = 4,
    _26to30 = 5,
    _31to35 = 6,
    _36to40 = 7,
}

I think you want to map the BuildYears to one of the above, and we can observe they go in fives. So we could do:

var yearNow = 2020;
var buildYear = 2019;

var ageGroupNumber = (yearNow - buildYear) / 5; // 0
var ageGroup = (AgeList) ageGroupNumber;        // _0to5 

So could we then do something like the following?

Ids = queryObj.AgeCategory
    .SelectMany(category => // For every category :
        queryRecord.Where(v => // Select the items in this category :
            (DateTime.Now.Year - v.BuildYear) / 5 == (int) category 
        )
        .Select(s => s.Id)
    );

I don't know if this will help you, or if I understood your problem correctly, but maybe this will give you some ideas.

Answer 4

Let's start with Marc's AndAlso method from this linked answer, and change AndAlso to OrElse:

public static Expression<Func<T, bool>> OrElse<T>(
    this Expression<Func<T, bool>> expr1,
    Expression<Func<T, bool>> expr2)
{
    var parameter = Expression.Parameter(typeof(T));

    var leftVisitor = new ReplaceExpressionVisitor(expr1.Parameters[0], parameter);
    var left = leftVisitor.Visit(expr1.Body);

    var rightVisitor = new ReplaceExpressionVisitor(expr2.Parameters[0], parameter);
    var right = rightVisitor.Visit(expr2.Body);

    return Expression.Lambda<Func<T, bool>>(
        Expression.OrElse(left, right), parameter);
}

private class ReplaceExpressionVisitor
    : ExpressionVisitor
{
    private readonly Expression _oldValue;
    private readonly Expression _newValue;

    public ReplaceExpressionVisitor(Expression oldValue, Expression newValue)
    {
        _oldValue = oldValue;
        _newValue = newValue;
    }

    public override Expression Visit(Expression node)
    {
        if (node == _oldValue)
            return _newValue;
        return base.Visit(node);
    }
}

That lets us write:

Expression<Func<Whatever, bool>> query = t => false;

if (queryObj.AgeCategory.Contains((int)AgeList._0to5))
{
    query = query.OrElse(v => v.BuildYear <= yearNow && v.BuildYear > year5);
}
if (queryObj.AgeCategory.Contains((int)AgeList._6to10))
{
    query = query.OrElse(v => v.BuildYear <= year5 && v.BuildYear > year10);
}
... and so on...

queryRecord = whatever.Where(query);

This generates a single query, which will look like:

WHERE (BuildYear <= yearNow AND BuildYear > year5) OR (BuildYear < year5 AND BuildYear > year10) etc