Im building a filter and im using the FilterByItems method to compare two Arrays(one from my frontend and one from my database). It all goes through a Overlay Panel in my Frontend, where the user can select different persontypes like member, worker and much more. So my problem is now that i got multiple persontype hovers which dont want do work together. If i just have one it works fine, if i add a second one it only works if both have the same values in the frontend list.

The right case would be: Frontend Panel one got the Member in the Array, Frontend Panel two got the Friend in the Array. Table shows all persons who are saved with these types in their profile.

The current and wrong case is: Panel 1 and Panel two are not working with different arrays and only showing informations if both have the same list for example both got member on position [0]

Generelly it seems like it works as a single one as an and query and as the second panel joins in it blocks completely. So here is my code(FilterByItems Method is linked above):

//The Filter
//Login
[HttpPost("filter/")]
public async Task<IActionResult> Filter([FromBody] Filter user)
{
    var baseQuery = _context.Personens.AsQueryable();
    //Personentyp 1 Dont works in combination with below
    if (user.personenTypFilter.Length > 0)
        baseQuery = baseQuery.FilterByItems(user.personenTypFilter, (m, k) => m.Personentypzuordnungens.Any(i => i.Personentyp.Bezeichnung.Contains(k)), true);
    ////Personentyp 2
    //if (user.personenTypFilter2.Length > 0)
    //    baseQuery = baseQuery.FilterByItems(user.personenTypFilter2, (m, k) => m.Personentypzuordnungens.Any(i => i.Personentyp.Bezeichnung.Contains(k)), true);
    //---------------------------------
    var result = await (baseQuery.Select(p => new
    {
        personId = p.PersonId,
        nachname = p.Nachname,
        vorname = p.Vorname,
        plz = p.Plz,
        firmBez = p.Firmenbezeichnung,
        ort = p.Ort,
        personentyp = p.Personentypzuordnungens.Select(i => new
        {
            personentypId = i.PersonentypId,
        }),
        aktuellePosition = p.AktuellePosition,
        taetigkeit = p.Tätigkeit,
        kernkompetenzen = p.Kernkompetenzen,
        datenReviewedZeitpunkt = p.DatenReviewedZeitpunkt,
    }).ToListAsync());
    return Ok(result);
}

Thats how i declared my variables in my Filter Model:

public string[] personenTypFilter { get; set; }
public string[] personenTypFilter2 { get; set; }

New problem with .CombineAnd(from comments)

baseQuery = baseQuery.Where(
                //Characteristics
                character1Predicate.CombineOr(character1Predicate2).CombineOr(character1Predicate3)
                //Persontypes
                .CombineAnd(personType1Predicate.CombineOr(personType2Predicate).CombineOr(personType3Predicate)));

<iframe name="sif1" sandbox="allow-forms allow-modals allow-scripts" frameborder="0"></iframe>

CodePudding user response：

This function is evolution of FilterByItems and adds additional public methods for generating predicate and combining them.

You can use new extensions in your query in the following way:

var baseQuery = _context.Personens.AsQueryable();

var predicate1 = baseQuery.GetItemsPredicate(ser.personenTypFilter, (m, k) => m.Personentypzuordnungens.Any(i => i.Personentyp.Bezeichnung.Contains(k)));
var predicate2 = baseQuery.GetItemsPredicate(user.personenTypFilter2, (m, k) => m.Personentypzuordnungens.Any(i => i.Personentyp.Bezeichnung.Contains(k)));

// filter by combined predicates
baseQuery = baseQuery.Where(predicate1.CombineOr(predicate2));

And implementation:

public static class QueryableExtensions
{
    public static IQueryable<T> FilterByItems<T, TItem>(this IQueryable<T> query, IEnumerable<TItem> items,
        Expression<Func<T, TItem, bool>> filterPattern, bool isOr = true, bool emptyValue = true)
    {
        var filterLambda = query.GetItemsPredicate(items, filterPattern, isOr, emptyValue);

        return query.Where(filterLambda);
    }

    public static Expression<Func<T, bool>> GetItemsPredicate<T, TItem>(this IEnumerable<T> query, IEnumerable<TItem> items, Expression<Func<T, TItem, bool>> filterPattern, bool isOr = true, bool emptyValue = false)
    {
        Expression predicate = null;
        foreach (var item in items)
        {
            var itemExpr = Expression.Constant(item);
            var itemCondition = ExpressionReplacer.Replace(filterPattern.Body, filterPattern.Parameters[1], itemExpr);
            if (predicate == null)
                predicate = itemCondition;
            else
            {
                predicate = Expression.MakeBinary(isOr ? ExpressionType.OrElse : ExpressionType.AndAlso, predicate,
                    itemCondition);
            }
        }

        predicate ??= Expression.Constant(emptyValue);
        var filterLambda = Expression.Lambda<Func<T, bool>>(predicate, filterPattern.Parameters[0]);
        return filterLambda;
    }

    public static Expression<Func<T, bool>> CombineOr<T>(this Expression<Func<T, bool>> predicate1,
        Expression<Func<T, bool>> predicate2)
    {
        var parameter = predicate1.Parameters[0];
        var body = Expression.OrElse(predicate1.Body, ExpressionReplacer.GetBody(predicate2, parameter));
        return Expression.Lambda<Func<T, bool>>(body, parameter);
    }

    public static Expression<Func<T, bool>> CombineAnd<T>(this Expression<Func<T, bool>> predicate1,
        Expression<Func<T, bool>> predicate2)
    {
        var parameter = predicate1.Parameters[0];
        var body = Expression.AndAlso(predicate1.Body, ExpressionReplacer.GetBody(predicate2, parameter));
        return Expression.Lambda<Func<T, bool>>(body, parameter);
    }

    class ExpressionReplacer : ExpressionVisitor
    {
        readonly IDictionary<Expression, Expression> _replaceMap;

        public ExpressionReplacer(IDictionary<Expression, Expression> replaceMap)
        {
            _replaceMap = replaceMap ?? throw new ArgumentNullException(nameof(replaceMap));
        }

        public override Expression Visit(Expression node)
        {
            if (node != null && _replaceMap.TryGetValue(node, out var replacement))
                return replacement;
            return base.Visit(node);
        }

        public static Expression Replace(Expression expr, Expression toReplace, Expression toExpr)
        {
            return new ExpressionReplacer(new Dictionary<Expression, Expression> { { toReplace, toExpr } }).Visit(expr);
        }

        public static Expression Replace(Expression expr, IDictionary<Expression, Expression> replaceMap)
        {
            return new ExpressionReplacer(replaceMap).Visit(expr);
        }

        public static Expression GetBody(LambdaExpression lambda, params Expression[] toReplace)
        {
            if (lambda.Parameters.Count != toReplace.Length)
                throw new InvalidOperationException();

            return new ExpressionReplacer(Enumerable.Range(0, lambda.Parameters.Count)
                .ToDictionary(i => (Expression)lambda.Parameters[i], i => toReplace[i])).Visit(lambda.Body);
        }
    }
}