LINQ - How to exclude conditions of CONTAINS predicate when input parameter is null?

Question

I'm facing an issue when trying to LINQ an array of items. Based on my input argument, I want to exclude from the predicate criteria if an input value is null:

public record Card(Guid Id, List<CardAttribute> Attributes);
public record CardAttribute(Guid CardId, string Key, string Value, string Searchable);

query.Where(card => {
  return card.Attributes.Any(attribute => {
    // Here is the deal!

    // the attribute should be searchable, easy to spot.
    attribute.Searchable = true;

    // In case the input key is null, I want to add a contains clause to this predicate.
    if (input.Key != null)
      attribute.Key.Contains(input.Key);

    // The same applies for value.
    if (input.Value != null)
      attribute.Value.Contains(input.Value);
  });
});

How can I turn this to a proper LINQ operation, returning a boolean value out of the condition but still ignoring a given condition whenever an input parameter is null?

Thank y'all!

Answer 1

// In case the input key is null, I want to add a contains clause to this predicate.

is unclear to me. I would have thought you wanted quite the opposite:

query.Where(card => card.Attributes.Any(attribute => 
    attribute.Searchable && // == true is not needed
    ( input.Key is null || attribute.Key.Contains(input.Key)) &&
    ( input.Value is null || attribute.Value.Contains(input.Value))
));
    

Explanation: In a a || b expression, b is only evaluated, if a is false. So, in input.Key is null || attribute.Key.Contains(input.Key) if input.Key is null, the right hand side won't be evaluated and the expression evaluates to true.

Which means in other words: Either input.Key is null or attribute.Key must contain it.

Same goes for Value. And since all of the three conditions must be satisfied, they are combined with &&.

When dealing with Linq2SQL, you'll have to keep in mind how this is going to be translated to SQL, though. In that case above solution probably will result in poorly performing queries.

I am not able to test this extensively, but an improved version considering SQL Execution Plans should be something along these lines:

query.Where(card => 
    {
       var searchable = card.Attributes.Where(attr => attr.Searchable);
       if (input.Key is not null) 
          searchable = searchable.Where(attr => attr.Key.Contains(input.Key);
       if (input.Value is not null)
          searchable = searchable.Where(attr => attr.Value.Contains(input.Value);
       return searchable.Any();   
    });
    

The idea behind this is that the query can be translated into an SQL that only contains the neccessary filters etc. to enable the database to further optimize execution.

For example: we don't need the db to figure out that one of the parameters we gave is null, we can instead just omit that whole clause and it will never even be considered by the db.