I am trying to link multiple values OR in a loop with LINQ.
Plattform: .net 5 C# 9
We are building a filter logic for a list. In the current case it concerns string values which are to be filtered.
The user can search for one or more values. He can decide if the single search terms are AND/OR linked and if a value is negated.
I saw this entry. But since my values are in the loop, I can't use ||.
https://stackoverflow.com/a/37195788/1847143
Example:
SELECT * FROM "Animal" WHERE "Name" = 'A';SELECT * FROM "Animal" WHERE "Name" = 'A' OR "Name" = 'B';SELECT * FROM "Animal" WHERE "Name" = 'A' OR "Name" = 'B' OR "Name" != 'C' ;SELECT * FROM "Animal" WHERE "Name" = 'A' AND "Name" = 'B';SELECT * FROM "Animal" WHERE "Name" = 'A' AND "Name" = 'B' AND "Name" != 'C';The AND link with LINQ is no problem. But how can the values be linked with OR?
using System.Collections.Generic;
using System.Linq;
namespace SampleProject
{
public class Program
{
public static void Main(string[] args)
{
// Or Condtion
Condition condition = Condition.Or;
var animalsQuery = Animals.AsQueryable();
// Loop over all search values to extend the query
foreach (FilterValue filterValue in FilterValues)
{
switch (filterValue.LikeType)
{
case LikeType.Left: // LIKE '%value'
animalsQuery = filterValue.IsNegated
? animalsQuery.Where(animal => !animal.Name.EndsWith(filterValue.Value))
: animalsQuery.Where(animal => animal.Name.EndsWith(filterValue.Value));
break;
case LikeType.Right: // LIKE 'value%'
animalsQuery = filterValue.IsNegated
? animalsQuery.Where(animal => !animal.Name.StartsWith(filterValue.Value))
: animalsQuery.Where(animal => animal.Name.StartsWith(filterValue.Value));
break;
case LikeType.LeftAndRight: // LIKE '%value%'
animalsQuery = filterValue.IsNegated
? animalsQuery.Where(animal => !animal.Name.Contains(filterValue.Value))
: animalsQuery.Where(animal => animal.Name.Contains(filterValue.Value));
break;
case LikeType.Equals: // Like 'value'
animalsQuery = filterValue.IsNegated
? animalsQuery.Where(animal => animal.Name != filterValue.Value)
: animalsQuery.Where(animal => animal.Name == filterValue.Value);
break;
}
}
var result = animalsQuery.ToList();
}
/// Values to filter
public static List<Animal> Animals = new()
{
new() {Name = "Lenny"},
new() {Name = "Gideon"},
new() {Name = "Shania"},
new() {Name = "Jada"},
new() {Name = "Kamil"},
new() {Name = "Fariha"},
};
/// Search Values
public static List<FilterValue> FilterValues = new()
{
new() {Value = "a", LikeType = LikeType.Left},
new() {Value = "n", LikeType = LikeType.Right},
new() {Value = "f", LikeType = LikeType.LeftAndRight},
new() {Value = "k", LikeType = LikeType.Equals},
};
}
public class Animal
{
public string Name { get; set; }
}
public class FilterValue
{
public string Value { get; set; }
public bool IsNegated { get; set; }
public LikeType LikeType { get; set; }
}
public enum LikeType
{
Left = 1,
Right = 2,
LeftAndRight = 3,
Equals = 4,
}
public enum Condition
{
And = 1,
Or = 2,
}
}
This gets into the realm of expression-tree rewriting. The good news is: it isn't horribly complex and you can perform your negate step at the same time:
using System;
using System.Collections.Generic;
using System.Diagnostics.CodeAnalysis;
using System.Linq;
using System.Linq.Expressions;
namespace SampleProject
{
public class Program
{
static Expression<Func<T, bool>> Combine<T>(Condition condition, Expression<Func<T, bool>> left, Expression<Func<T, bool>> right, bool negateRight)
{
if (right is null) return left;
if (left is null)
{
return negateRight ?
Expression.Lambda<Func<T, bool>>(
Expression.Not(right.Body), right.Parameters)
: right;
}
var leftP = left.Parameters.Single();
var rightP = right.Parameters.Single();
var rightBody = right.Body;
if (!ReferenceEquals(leftP, rightP))
{
// swap all uses of rightP on rightBody to leftP
// i.e. normalize on the parameter
rightBody = new SwapVisitor(rightP, leftP).Visit(rightBody);
}
if (negateRight)
{
rightBody = Expression.Not(rightBody);
}
return Expression.Lambda<Func<T, bool>>(condition switch
{
Condition.And => Expression.AndAlso(left.Body, rightBody),
Condition.Or => Expression.OrElse(left.Body, rightBody),
_ => throw new ArgumentOutOfRangeException(nameof(condition)),
}, left.Parameters);
}
class SwapVisitor : ExpressionVisitor
{
private readonly Expression _from, _to;
public SwapVisitor(Expression from, Expression to)
{
_from = from;
_to = to;
}
public override Expression Visit(Expression node)
=> ReferenceEquals(node, _from) ? _to : base.Visit(node);
}
public static void Main(string[] args)
{
// Or Condtion
Condition condition = Condition.Or;
var animalsQuery = Animals.AsQueryable();
// Loop over all search values to extend the query
Expression<Func<Animal, bool>> predicate = null;
foreach (FilterValue filterValue in FilterValues)
{
switch (filterValue.LikeType)
{
case LikeType.Left: // LIKE '%value'
predicate = Combine(condition, predicate, animal => animal.Name.EndsWith(filterValue.Value), filterValue.IsNegated);
break;
case LikeType.Right: // LIKE 'value%'
predicate = Combine(condition, predicate, animal => animal.Name.StartsWith(filterValue.Value), filterValue.IsNegated);
break;
case LikeType.LeftAndRight: // LIKE '%value%'
predicate = Combine(condition, predicate, animal => animal.Name.Contains(filterValue.Value), filterValue.IsNegated);
break;
case LikeType.Equals: // Like 'value'
predicate = Combine(condition, predicate, animal => animal.Name == filterValue.Value, filterValue.IsNegated);
break;
}
}
if (predicate is not null)
{
animalsQuery = animalsQuery.Where(predicate);
}
var result = animalsQuery.ToList();
}
/// Values to filter
public static List<Animal> Animals = new()
{
new() { Name = "Lenny" },
new() { Name = "Gideon" },
new() { Name = "Shania" },
new() { Name = "Jada" },
new() { Name = "Kamil" },
new() { Name = "Fariha" },
};
/// Search Values
public static List<FilterValue> FilterValues = new()
{
new() { Value = "a", LikeType = LikeType.Left },
new() { Value = "n", LikeType = LikeType.Right },
new() { Value = "f", LikeType = LikeType.LeftAndRight },
new() { Value = "k", LikeType = LikeType.Equals },
};
}
public class Animal
{
public string Name { get; set; }
}
public class FilterValue
{
public string Value { get; set; }
public bool IsNegated { get; set; }
public LikeType LikeType { get; set; }
}
public enum LikeType
{
Left = 1,
Right = 2,
LeftAndRight = 3,
Equals = 4,
}
public enum Condition
{
And = 1,
Or = 2,
}
}