Searching for each word in string

Question

I'm still trying to get my head wrapped around LINQ and its functions.

I am trying to build a search. For example, I want to return results that match the search string (which we call search terms). In the database, for example, we have results such as:

ID    ItemNum    CategoryType
1     2737       Full Length Dresses
2     5353       Full Length Dresses

If a person searches "Full Dresses", I want to be able to return the two results above. However, using Contains does not work. Searching "Full Length Dresses" does work. I'm guessing I need to somehow split the search string into an array and then search using each item in the array, but I'm not sure how to go about doing this.

var results = db.Products.Where(p => p.CategoryType.Contains(searchString)).Select(p => p).Distinct();

Thanks.

if `searchString` equals `"Full Foo"` should the both items return? — fubo, Apr 05 '17 at 13:32
What do you mean by both items? You mean each item vs both items together? — yondaimehokage, Apr 05 '17 at 13:38
If the Searchstring is `Full Foo` then `Full` is a part of `Full Length Dresses` but `Foo` is not. Should be both words part of the result or at least one — fubo, Apr 05 '17 at 13:40
Ideally, I would like to have it so it can be a AND or OR with the search terms. — yondaimehokage, Apr 05 '17 at 13:43

christophano · Answer 1 · 2017-04-05T13:58:18.163

If your underlying datastore is Sql Server, and the order of the terms is important, then you could use:

searchString = searchString.Replace(" ", "%");
var results = db.Products.Where(p => SqlFunctions.PatIndex(searchString , p.CategoryType) > 0).Distinct();

otherwise you may have to split the terms and treat each separately:

var terms = searchTerms.Split(new [] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
var results = db.Products.Where(p => terms.All(x => p.CategoryType.Contains(x))).Distinct()

and, if you want it to return for any matches (so, an OR instead of an AND) use the Any linq function instead of the All linq function:

var terms = searchTerms.Split(new [] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
var results = db.Products.Where(p => terms.Any(x => p.CategoryType.Contains(x))).Distinct()

dotNET · Answer 2 · 2017-04-05T13:36:08.277

Since your LINQ will be forwarded to the underlying database for execution, many of the .NET functions cannot be used directly inside the query (because there are no canonical equivalents at the DB level). You should first fetch all categories into memory and then use full-scale LINQ-to-Objects to filter your results:

This should do it:

var results = db.Products.Select(p => p.CategoryType).ToArray();
var Matches = results.Where(p => searchString.Split(new []{' '}).Any(x => p.Contains(x)).Distinct();

I'm assuming that the words in your search string will be separated by space. If there are other punctuation, you can add all of them in the array above. If space is your only separator, the second line above can be simplified to:

var Matches = results.Where(p => searchString.Split(' ').Any(x => p.Contains(x)).Distinct();

I get this error: `Additional information: LINQ to Entities does not recognize the method 'System.String[] Split(Char[])' method, and this method cannot be translated into a store expression.` — yondaimehokage, Apr 05 '17 at 13:29

score 0 · Answer 3 · answered Apr 05 '17 at 13:40

I would expand it with some things that are user-friendly:

string[] keywords = searchString.Split(' ').Where(x=>!x.Equals(""));
    //split by space and leave double spaces (because might be an empty string between two spaces)
var results = db.Products.Where(p => keywords.Any(x => p.CategoryType.Contains(x.ToLower())).Distinct();
   //users will tyle only lowercase characters, so you need `String.ToLower()`.

Developer · Answer 4 · 2017-04-05T13:57:16.733

0

try this (This will (of course) only work with LINQ to SQL):

string[] str = searchString.split();
string newSearchString ="%";

foreach (string item in str)
{
   newSearchString += item + "%"; 
}

var results = from p in db.products
              where SqlMethods.Like(p.CategoryType , newSearchString  )
              select p;

edited Apr 05 '17 at 13:57

answered Apr 05 '17 at 13:49

Developer

460
4
17

score 0 · Answer 5 · answered Apr 05 '17 at 14:01

    IEnumerable<string> values = "Full,Dresses".Split(',');
var matchingRows = from row in datatable.AsEnumerable()
                   join value in values
                   on row.Field<string>(CategoryType) equals value
                   select row;
DataTable tblResult = matchingRows.CopyToDataTable();

score 0 · Answer 6 · edited May 23 '17 at 11:46

Posting this as an answer, it's too long for a comment.

Keep in mind that if the table is big this will likely have terrible performance, because even if you create a regular index on CategoryType, it's never going to be used (unless you're happy with a simple "starts with" kind of search, which you are not).

I don't know which RDBMS you are using, but I would investigate the use of full-text search if it's available. If you're using SQL Server, it looks like you can have Linq use a full-text index.

If you do the search in code like @dotNET suggested it gets better, and you also get the chance to implement something fancier (look into the Levenshtein Distance algorithm, which would allow you to offer fuzzy string matching), but full-text search is something you might want to keep in mind, because if you end up having millions of rows this approach might not scale that well.

At max, there will only by around 80 rows returned. This is a fairly small application right now. — yondaimehokage, Apr 06 '17 at 18:31
@yondaimehokage oh okay, then it's not an issue for you. Just something to keep in mind. — s.m., Apr 06 '17 at 18:46

Searching for each word in string

6 Answers6