Entity Framework LINQ complex query - combine multiple predicates

Question

I'm trying to create a complex Linq query that goes like this: Get all organisations which have employees that match the given filter parameters.

Example filter:

Firstname: John
Name: Smith

My first attempt:

if (!filter.Name.IsNullOrWhiteSpace())
{
    query = query.Where(o => o.Persons.Any(p => p.Name.ToLower().Contains(filter.Name.ToLower())));
}

if (!filter.Firstname.IsNullOrWhiteSpace())
{
    query = query.Where(o => o.Persons.Any(p => p.Firstname.ToLower().Contains(filter.Firstname.ToLower())));
}

if (!filter.ContactNumber.IsNullOrWhiteSpace())
{
    query = query.Where(o => o.Persons.Any(p => p.ContactNumber.contains(filter.ContactNumber)));
}

The problem with this approach is that when there is someone with the firstname John (ex. John Johnson) in organisation A, and someone with the last name Smith (Jenny Smith) in organisation A. The organisation (A) that contains those two persons gets returned. Which it shouldn't. I only want organisations that have people with the firstname "john" AND the lastname "Smith"

I found a working, but dirty and non-scalable approach:

if (!filter.ContactNumber.IsNullOrWhiteSpace())
{
    if (!filter.Name.IsNullOrWhiteSpace() && !filter.Firstname.IsNullOrWhiteSpace())
    {
        query = query.Where(o => o.Persons.Any(p => p.ContactNumber.contains(filter.ContactNumber)
                                                && p.Name.ToLower().Contains(filter.Name.ToLower())
                                                && p.Firstname.ToLower().Contains(filter.Firstname.ToLower())));
    }
    else if (!filter.Name.IsNullOrWhiteSpace())
    {
        query = query.Where(o => o.Persons.Any(p => p.ContactNumber.contains(filter.ContactNumber)
                                                && p.Name.ToLower().Contains(filter.Name.ToLower())));
    } else if (!filter.Firstname.IsNullOrWhiteSpace())
    {
        query = query.Where(o => o.Persons.Any(p => p.ContactNumber.contains(filter.ContactNumber)
                                                && p.Firstname.ToLower().Contains(filter.Firstname.ToLower())));
    } else
    {
        query = query.Where(o => o.Persons.Any(p => p.ContactNumber.contains(filter.ContactNumber));
    }
} else if(!filter.Name.IsNullOrWhiteSpace())
{
    if (!filter.Firstname.IsNullOrWhiteSpace())
    {
        query = query.Where(o => o.Persons.Any(p => p.Firstname.ToLower().Contains(filter.Firstname.ToLower()) && p.Name.ToLower().Contains(filter.Name.ToLower())));
    } else
    {
        query = query.Where(o => o.Persons.Any(p => p.Name.ToLower().Contains(filter.Name.ToLower())));
    }
} else if (!filter.Firstname.IsNullOrWhiteSpace())
{
    query = query.Where(o => o.Persons.Any(p => p.Firstname.ToLower().Contains(filter.Firstname.ToLower())));
}

As you can see this not a very clean solution.

I also tried using method calls inside the expression but Linq couldnt translate that. Is there any way I can can make a list of predicate expressions an merge them to one? Or is there a another, better solution?

By the way, since I need a paginated list, it all has to be in one query.

For your information, this is what my filter class looks like. It is just a class send from my front-end with all the fields that need to be filtered.

public class ContactFilter
{
    public string Name{ get; set; }
    public string Firstname{ get; set; }
    public string ContactNummer { get; set; }
}

What type exactly is filter? Doesn't seem like a Predicate<T>. Where are its properties coming from? — Amal K
– Amal K, Commented Jun 2, 2021 at 15:25
Filter is just a simple class with the fields that need to be filtered. Some fields may be null, some not, depends on the which fields are filled in. I edited the description — Arne
– Arne, Commented Jun 2, 2021 at 15:34
Why is your first code not working? It seems to be AND conditions of each value in the filter. — Epic Chen
– Epic Chen, Commented Jun 2, 2021 at 15:43
The first code was not working because of the explanation below it. And I needed contains since I want to allow partial matches — Arne
– Arne, Commented Jun 3, 2021 at 9:08

Servy · Accepted Answer · 2021-06-02 16:18:39Z

6

Is there any way I can can make a list of predicate expressions an merge them to one?

Yes, and that's the approach I'd prefer in this situation.

First build the list:

var filterExpressions = new List<Expression<Func<Person, bool>>();
if (!filter.Name.IsNullOrWhiteSpace())
{
    filterExpressions.Add(p => p.Name.ToLower().Contains(filter.Name.ToLower()));
}

if (!filter.Firstname.IsNullOrWhiteSpace())
{
    filterExpressions.Add(p => p.Firstname.ToLower().Contains(filter.Firstname.ToLower()));
}

if (!filter.ContactNumber.IsNullOrWhiteSpace())
{
    filterExpressions.Add(p => p.ContactNumber.contains(filter.ContactNumber));
}

From there, you can use this implementation to And arbitrary Expressions together. You'll also need to decide what to do if there are no filters to apply (I'll use a default of no filter, but you may want to do something else).

var predicate = filterExpressions.DefaultIfEmpty(p => true)
    .Aggregate((a, b) => a.And(b));

Now we get to the hard part. We have an expression that represents the lambda you want to pass to a call to Any. It would be nice if we could just do:

query = query.Where(o => o.Persons.Any(predicate));

But sadly, this won't work because the type of o.Persons isn't an IQueryable. So now we have an expression that we want to embed in another expression in which the inner expression needs to be a lambda. Fortunately this isn't too complicated:

public static Expression<Func<TSource, TResult>> EmbedLambda
    <TSource, TResult, TFunc1, TFunc2>(
    this Expression<Func<TFunc1, TFunc2>> lambda,
    Expression<Func<TSource, Func<TFunc1, TFunc2>, TResult>> expression)
{
    var body = expression.Body.Replace(
        expression.Parameters[1],
        lambda);
    return Expression.Lambda<Func<TSource, TResult>>(
        body, expression.Parameters[0]);
}

(Using a helper class from the above link)

Now we just need to call the method. Note we won't be able to rely entirely on type inference due to the way this all works out, so some types need to be specified explicitly.

query = query.Where(predicate.EmbedLambda((UnknownType o, Func<Person, bool> p) => o.Persons.Any(p)));

answered Jun 2, 2021 at 16:18

Servy

204k27 gold badges347 silver badges470 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ivan Stoev Over a year ago

"But sadly, this won't work because the type of o.Persons isn't an IQueryable..." But it can easily be maid by adding .AsQueryable(), thus eliminating the need of additional expression helpers with ugly calling syntax.

Servy Over a year ago

@IvanStoev Assuming the query provider knows how to translate that, which many will not. It does not produce a query the same as having inlined the lambda, which is what the query provider is generally expecting to happen.

Ivan Stoev Over a year ago

Well, generally you are right, but the question has a context ([entity-framework-core] tag, that's why I'm landing here). Fortunately the target query provider (EF Core) recognizes the need of such functionality and property handles it.

Svyatoslav Danyliv · Accepted Answer · 2021-06-02 16:16:12Z

6

One of the easiest solution is using LINQKit library:

var predicate = PredicateBuilder.New<Person>();

if (!filter.Name.IsNullOrWhiteSpace())
{
    predicate = predicate.And(p => p.Name.ToLower().Contains(filter.Name.ToLower()));
}

if (!filter.Firstname.IsNullOrWhiteSpace())
{
    predicate = predicate.And(p => p.Firstname.ToLower().Contains(filter.Firstname.ToLower()));
}

if (!filter.ContactNumber.IsNullOrWhiteSpace())
{
    predicate = predicate.And(p => p.ContactNumber.contains(filter.ContactNumber));
}

Expression<Func<Person, bool>> exp = predicate;

query = query
    .AsExpandable()
    .Where(o => o.Persons.Any(exp.Compile()));

answered Jun 2, 2021 at 16:16

Svyatoslav Danyliv

28.2k4 gold badges24 silver badges46 bronze badges

1 Comment

Arne Over a year ago

Thanks, I went with the dependency free solution but I'll keep this in mind.

Collectives™ on Stack Overflow

Entity Framework LINQ complex query - combine multiple predicates

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related