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 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

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" }

Answer 2

A simpler and more correct solution (then leppie's):

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

    foreach (string qs in query)
    {
        q = q.Where(x => x.partName.Contains(qs));
    }

    return q;
}

This will work as long as partName is a string (or an SQL equivalent of a string).

The important thing to note is partName.Contains(qs) is different than query.Contains(partName).
With partName.Contains(qs), partName is searched for any occurrence of qs. The resulting SQL would be equivalent (where <qs> is the value of qs):

select * from Parts where partName like '%<qs>%';

Also of note are StartsWith and EndsWith which are similar to Contains but look for the string in the specific location.

query.Contains(partName) is the same as a SQL in command. The resulting SQL would be equivalent to (where <query0> is the value of query[0], <query1> is the value of query[1], and <queryN> is the last value in the query array):

select * from Parts where partName in ( <query0>, <query1>, ..., <queryN> );

Update:
It is also important to note that leppie's answer does not escape the wildcard characters before adding them to the like statement. This is not an issue with the Contains solution since Linq will escape the query before sending it. An escaped version of the SqlMethods.Like solution would be:

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

    foreach (var qs in query)
    {
        string escaped_bs = qs.Replace("/", "//"),
            escaped_us = escaped_bs.Replace("_", "/_"),
            escaped_p = escaped_us.Replace("%", "/%"),
            escaped_br = escaped_p.Replace("[", "/["),
            likestr = string.Format("%{0}%", escaped_br);

        q = q.Where(x => SqlMethods.Like(x.partName, likestr, '/'));
    }

    return q;
}

You don't have to worry about ' since Linq will escape that for you.

Answer 3

Using the NinjaNye.SearchExtension nuget package allows you to perform this search with ease:

string[] terms = new[]{"search", "term", "collection"};
var result = db.Parts.Search(terms, p => p.PartName);

You could also search multiple string properties

var result = db.Parts.Search(terms, p => p.PartName, p.PartDescription);

Or perform a RankedSearch which returns IQueryable<IRanked<T>> which simply includes a property which shows how many times the search terms appeared:

//Perform search and rank results by the most hits
var result = db.Parts.RankedSearch(terms, p => p.PartName, p.PartDescription)
                     .OrderByDescending(r = r.Hits);

There is a more extensive guide on the projects GitHub page: https://github.com/ninjanye/SearchExtensions

Hope this helps future visitors

Answer 4

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 5

You can write it as this

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

Answer 6

I feel this is somewhat simple and working for me:

string[] product = products.Split(','); 
using (var context = new ProjectTrackerEntities()) 
{ var result = from part in context.DBAudits where product.Contains(part.TableName) select part; }

Answer 7

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;
}