Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

LINQ to Entities Searching text properties for multiple keywords

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.

like image 421
Darko Z Avatar asked Feb 18 '09 03:02

Darko Z


1 Answers

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
like image 98
RichardW1001 Avatar answered Sep 23 '22 06:09

RichardW1001