LINQ to Entities Searching text properties for multiple keywords

Question

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.

Weird, an answer was here, I voted it up, then down because I thought it was wrong, then went to vote it back up because it was right, now its gone. — John Farrell, Feb 18 '09 at 05:07
Yeah there was an answer from casperOne but someone must have deelted it. He suggested using a stored proc, which is what im leaning towards right now. — Darko, Feb 18 '09 at 20:18
I am in the same situation... Did you find a solution with LinqToEntities, or did you end up with using a stored procedure? — Kjensen, Jun 13 '09 at 16:46

score 10 · Answer 1 · answered Jul 08 '10 at 09:14

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

Note - turns out this works with EF4 but not with Linq to Sql in 3.5. — RichardW1001, Jul 09 '10 at 12:36
+1 - I've been looking all over for a solution to this problem the last couple of days. Didn't think of doing it like that. — bernhof, Oct 19 '10 at 19:40

score 3 · Accepted Answer · answered Feb 18 '09 at 05:09

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.

score 1 · Answer 3 · answered Feb 20 '09 at 22:19

1

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();

answered Feb 20 '09 at 22:19

eglasius

35,831
5
65
110

because that matches the entire description to the keywords. What i'm looking for is matching many individual words to many individual words. – Darko Feb 22 '09 at 22:48

Alexander Prokofyev · Answer 4 · 2009-02-18T05:26:56.713

0

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

edited Feb 18 '09 at 05:26

answered Feb 18 '09 at 05:19

Alexander Prokofyev

33,874
33
95
118

LINQ to Entities Searching text properties for multiple keywords

4 Answers4