LINQ to SQL multi-keyword search
Thursday, April 15th, 2010how-to search mutiple keywords using LINQ to SQL
this bit of LINQ code will let you search for records using any number of keywords
it’s a bit limited, but it illustrates the starting point for something more.
the key is to get results for the first keyword into a disconnected object (in this case, a simple List<String>) and then search for the remaining keywords in there. clearly you could up the ante, and select more fields and spin up a collection of custom objects (i.e. List<MyClass>) but this suited my needs for an autocomplete for country names.
// GetMatchesIntersect(string csv, int maxMatches)
// mainly developed for auto-complete scenarios
//
// searches single [n]varchar|text|char field for multiple keywords
//
// returns array of field values containing ALL keywords
//
// NOTE: i decided on array because i am implementing this a webservice
// to be consumed by other languages/platforms)
public string[] GetMatchesIntersect(string csv, int maxMatches)
{
// keywords come in as comma-separated values
string[] keywords = csv.Split(new char[] { ' ', ',' });
// this list will hold the matches
List<string> matches;
using (DbDataContext db = new DbDataContext())
{
// build list of matches for first keyword into generic List<string>
matches = (from s in db.TableToSearch
where s.FieldToSearch.Contains(keywords[0])
select s.FieldToSearch).ToList<string>();
// if any were found
if (null != matches && matches.Count > 0)
{
// start searching at the 2nd keyword (if any)
for (int i = 1; i < keywords.Length && matches.Count > 0; i++)
{
// using .ToUpper() because matching in a List<string> is case-sensitive
// set matches equal to the query results for each succesive keyword.
matches = (from match in matches
where match.ToUpper().Contains(keywords[i].ToUpper())
select match).ToList<string>();
}
}
}
// ship it out as array
return matches.Take(maxMatches).ToArray<string>();
}
oh how many days seem like this one:
completely self-contained computer pod