For a website I'm doing we're using LINQ to Entities. I have been charged with adding search functionality to the site. I'm trying to figure out the most elegant way to search for multiple keywords (user entered) on a single field in the database. Allow me to give an example.
Table columns:
Name, Description
Example row:
"Cookie monster", "Fluffy, likes cookies and blue"
User search (delimiter doesn't matter):
"blue fluffy"
Currently I am using the following:
public List<SesameCharacters> SearchByKeywords(string keywords)
{
List<SesameCharacters> output = new List<SesameCharacters>();
string[] k = keywords.ToLower().Split(' ');
using (SesameStreet_Entities entities = new SesameStreet_Entities())
{
IQueryable<SesameCharacters> filter = entities.SesameCharacters;
foreach (string keyword in k)
filter = ForceFilter(filter, keyword);
output = filter.ToList();
}
return output;
}
private IQueryable<SesameCharacters> ForceFilter(IQueryable<SesameCharacters> filter, string keyword)
{
return filter.Where(p => p.Description.ToLower().Contains(keyword));
}
This currently works as expected but I imagine it is not the best solution to the problem. Am I missing something glaringly obvious?
NOTE: This is AND
matching.
I found this worked for me - this is using VB.Net with Entity Framework 4.0, but I'm sure the principle translates.
This one does the "OR" style query:
Function Search(ByVal query As String) As IQueryable(Of Product)
Dim queryWords As String() = query.Split()
Dim entities As New Entities()
Return entities.Products.Where(Function(p) queryWords.Any(Function(w) p.Description.Contains(w)))
End Function
And this one does "AND" style queries:
Function Search(ByVal query As String) As IQueryable(Of product)
Dim queryWords As String() = query.Split()
Dim entities As New Entities()
Return entities.Products.Where(Function(p) queryWords.All(Function(w) p.Description.Contains(w)))
End Function
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With