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?

Gaylord

Try to think what you ask trying to do. It does not make sense really. How can you match an array with a string? You need to tell us what partName will look like at least. – Ellamaeellan

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

Ellamaeellan

This is equivalent to my answer, except you are not escaping the wild card characters ('_', '%', '['). – Stringhalt

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.

Stringhalt

You assign escaped_p but never use it. Typo? – Mercurous

@PatrickM Yeah, I forgot to change some of the code after copy/paste. It is fixed now. – Stringhalt

In my testing I was returning an IQueryable<IMyInterface> that I then applied the Contains to, so I will have to see if that impacted the code (i.e. turned it into LINQ to entities accidentally). I will delete my previous comment for now. – Magness

@TrueBlueAussie I have had issues where getting the order wrong on the LINQ statment would cause the query to be run on the local machine. For example, if you add an order by clause and then add a where filter it seemed to always bring down the ordered data, then apply your where filter. If the contains is converted to SQL then it will use LIKE and '%<yourText>%'. If you cannot guarantee the LINQ will be run in SQL, then only your solution with ToLower will work. – Stringhalt

Answer 3

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.

Impala

Doing this results in the error "Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains() operator." – Gaylord

@casperOne: The expression passed to All uses the Contains method, not the equals operator, meaning the part name would be tested to see if it contains the query term instead of being exactly equal to it. As for using Any, the question states that the results must match all of the search terms, not just one of them (which is what any would do). – Impala

@a_m0d: I did say I wasn't sure if it would transform. Try the second suggestion, you should have better results. – Impala

To be honest, I don't really care too much whether they are all there or not, but I feel that the search would be much better if they are all there, and probably easier to implement as well. – Gaylord

Yeah, the second suggestion does work, but I don't really like the idea of loading it all into memory like that and then searching through it. Ideally, I would let the database search for me, but maybe that is just premature optimisation on my part. – Gaylord

@a_m0d: Well if that's the case, you should put it in your question. The way it's currently worded makes it seem like its a requirement. That aside, try running the profiler and checking the query that gets sent to the DB: it shouldn't be loading everything into memory. One of the benefits of deferred execution is that the query is only executed when the results are enumerated (which should only happen in your calling method). – Impala

Answer 4

You can write it as this

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

Etui

Doing this results in the error "Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains() operator." - same as below. – Gaylord

This works fine for me in a similar query, MVC 2/.NET 4. The accepted answer for the question uses SqlMethods which I can't find the namespace for in my project - deprecated? – Uball

SqlMethods is in System.Data.Linq.SqlClient namespace. Use SqlMethods only in Linq to SQL implementation and you will need reference to System.Data.Linq assembly – Etui

Answer 5

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

Bly

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

Nates

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

Surfactant

Your Search method isn't available to SQL - unless you fetched EVERY part by adding, say, .ToList() after db.Parts it's not going to work – Berkin