LINQ to Entities Searching text properties for multiple keywords

Question

Hi guys,

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.

Answer 1

Looks like Linq to Entities doesn't support contains:

http://msdn.microsoft.com/en-us/library/bb738638.aspx

I'd roll my own query for this one. Your probably going to want full control over the text search queries if you find out these types of searches become performance issues.

Answer 2

Not really LINQ related but you could consider using SQL Server Full-Text Search while CONTAINS predicate understands boolean operators—AND, OR, AND NOT.

This article could also be useful: Dynamically Composing Expression Predicates

Answer 3

how about instead of:

IQueryable<SesameCharacters> filter = entities.SesameCharacters;

        foreach (string keyword in k)
            filter = ForceFilter(filter, keyword);

        output = filter.ToList();

Do:

return (from c in entities.SesameCharacters
         where k.Contains(c..Description.ToLower())
         select c
         ).ToList();

Answer 4

Hi

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