C# linq query - Where with multiple ANDs-CodePudding

I am trying to query using EF. The user can use up to 3 search words but they are not required. How do I write an EF query that will work as an AND for all of the search words that are used but be able to remove an AND for any search word that is empty?

Example, I want the following to return the first two elements in the array for s1='mobile', s2='', and s3='laptop'. It's not returning any. It should return the first two if s2 is changed to s2='burke'.

Example:

using System;
using System.Linq;
public class Simple {
  public static void Main() {
    string[] names = { "Burke laptop mobile", "laptop burke mobile", "Computer Laptop", 
                       "Mobile", "Ahemed", "Sania", 
                       "Kungada", "David","United","Sinshia" };

//search words
    string s1 = "mobile";
    string s2 = "";
    string s3 = "laptop";
    
      var  query = from s in names 
                               where (!string.IsNullOrEmpty(s1) && s.ToLower().Contains(s1)) 
                                    && (!string.IsNullOrEmpty(s2) && s.ToLower().Contains(s2))
                                    && (!string.IsNullOrEmpty(s3) && s.ToLower().Contains(s3))
                                orderby s
                               select s.ToUpper();

    foreach (string item in query)
      Console.WriteLine(item);
  }
}

CodePudding user response：

Instead of trying to write a single where statement, you can optionally extend an existing IQueryable<T> (or IEnumerable<T> like our code sample would be using) by chaining Where statements.

var query = names;

if (!string.IsNullOrEmpty(s1))
{
    query = query.Where(x => x.Contains(s1));
}

if (!string.IsNullOrEmpty(s2))
{
    query = query.Where(x => x.Contains(s2));
}

// ... (or make a foreach loop if s1,s2,s3 were an array.

var results = query.OrderBy(x => x).Select(x => x.ToUpper());

Chaining Where like this is equivalent to "anding" all where predicates together.

EDIT:
To update why your specific implementation doesn't work is because your && operators are incorrect for the given use-case.

(string.IsNullOrEmpty(s1) || s.ToLower().Contains(s1)) &&
(string.IsNullOrEmpty(s2) || s.ToLower().Contains(s2)) &&
(string.IsNullOrEmpty(s3) || s.ToLower().Contains(s3))

Remember that && requires both left and right statements to be true, so in your case !string.IsNullOrEmpty(s2) && s.ToLower().Contains(s2) this is saying that s2 must always be not-empty/null.

CodePudding user response：

Please consider this:

using System;
using System.Linq;
public class Simple {
  public static void Main() {
        string[] names = { "Burke laptop mobile", "laptop burke mobile", "Computer Laptop",
                       "Mobile", "Ahemed", "Sania",
                       "Kungada", "David","United","Sinshia" };

        //search words
        string s1 = "mobile";
        string s2 = "";
        string s3 = "laptop";

        var query = from s in names
                    where (s1 != null && s.ToLower().Contains(s1))
                       && (s2 != null && s.ToLower().Contains(s2))
                       && (s3 != null && s.ToLower().Contains(s3))
                    orderby s
                    select s.ToUpper();

    foreach (string item in query)
      Console.WriteLine(item);
  }
}

CodePudding user response：

If you use an array or list instead of multiple strings, you could do something like

List<string> searchWords = new List<string>
{
    "mobile",
    "",
    "laptop"
};

var query = names
    .Where(n => searchWords
        .Where(s => !string.IsNullOrEmpty(s))
        .All(s => n.ToLower().Contains(s)))
    .Select(n => n.ToUpper())
    .OrderBy(n => n);

This also is more flexible, as you can have any number of search words, without changing the query.