Using contains() in LINQ to SQL

Question

I'm trying to implement a very basic keyword search in an application using linq-to-sql. My search terms are in an array of strings, each array item being one word, and I would like to find the rows that contain the search terms. I don't mind if they contain more than just the search terms (most likely, they will), but they all the search terms do have to be present.

Ideally, I would like something similar to the snippet below, but I know that this won't work. Also, I have looked at this question here, but the author of that question seems content to do things the other way round ( query.Contains(part.partName) ), which doesn't work for me.

public IQueryable<Part> SearchForParts(string[] query)
{
    return from part in db.Parts
           where part.partName.Contains(query)
           select part;
}

How can I rewrite this query so that it will do what I need?

Answer 1

You could try:

public IQueryable<Part> SearchForParts(string[] query)
{
    return from part in db.Parts
           where query.All(term => part.partName.Contains(term))
           select part;
}

However, I'm not sure if LINQ to SQL will be able to transform it into T-SQL. Another option would be:

public IQueryable<Part> SearchForParts(string[] query)
{
    var result = from part in db.Parts
                 select part;

    foreach(var term in query)
    {
        result = from part in result
                 where part.partName.Contains(term)
                 select part;
    }

    return result;
}

It's not as pretty, but it should work. You'll get a query with a lot of ANDs in the where clause.

Answer 2

You can write it as this

var result = db.Parts.Where(p => query.All(q => p.partName.Contains(q)));

Answer 3

please try this:

public IQueryable<Part> SearchForParts(string[] query)
{
    return from part in db.Parts
           where Search(part.Name,query)
           select part;
}

public bool Search(string partName,string[] query)
{
    for (int i = 0; i < query.Length; i++)
    {
        if(partName.Contains(query[i]))
           return true;
    }

    return false;
}

Answer 4

Looking at the other attempts saddens me :(

public IQueryable<Part> SearchForParts(string[] query)
{
  var q = db.Parts.AsQueryable(); 

  foreach (var qs in query)
  { 
    var likestr = string.Format("%{0}%", qs);
    q = q.Where(x => SqlMethods.Like(x.partName, likestr));
  }

  return q;
}

Assumptions:

• partName looks like: "ABC 123 XYZ"

• query is { "ABC", "123", "XY" }