How do I implement a search feature with Entity Framework?

Question

I have a blog application that models a database using Entity Framework. The problem with this blog is that it has become difficult to find things I'm looking for. It needs a search function, but I'm not sure how to implement this with SQL and/or LINQ to Entities.

Right now I am searching my database with this LINQ query but it seems like it should be better.

    public IEnumerable<BlogPost> SearchBlogPosts(string query, int page, int itemsPerPage)
    {
        var result = _dataContext.BlogPosts
            .Where(BlogPostContains(query))
            .OrderByDescending(x => x.PostedDate)
            .Skip((page - 1) * itemsPerPage)
            .Take(itemsPerPage),
        return result;
    }

    private Func<BlogPost, bool> BlogPostContains(string query)
    {
        return x => x.Title.Contains(query) || x.Body.Contains(query) || x.Author.Contains(query);
    }

One big problem with this is that the search is case sensitive.

Question 1) Is there a better way to do searching with LINQ to Entities?

Question 2) What about with just plain SQL? How would I write a search stored procedure in SQL Server so that I can map and use that in EF instead of LINQ?

I just want a case-insensitive search that is performed in the database so as to maintain good performance.

Thanks in advance.

Answer 1

The standard approach for this would be a SQL fulltext search. You will have to enable fulltext on the DB, designate column(s) to be fulltext indexed and then will then be able to use SQL Contains queries using these columns.

Fulltext search queries are currently not supported by Linq to Entities - you will have to resort to standard SQL queries for this. You can use ExecuteStoreQuery() to at least map the search results to typed result.

Answer 2

The default collation of SQL Server is case-insensitive which means that a where clause like this (which is what LINQ to Entities would create out of Contains)...

where Name like '%JOHN%'

...should find "john Wayne". I believe that your query is not executed on the server but actually with LINQ to Objects in memory - and there the search is case sensitive. It's LINQ to Objects because you are not returning an Expression from your BlogPostContains. The signature should be:

private Expression<Func<BlogPost, bool>> BlogPostContains(string query)

If your are only returning Func<BlogPost, bool> you are working with the IEnumerable (and not the IQueryable) overload of the Where extension method which in turn causes the whole BlogPosts table loaded first into memory. Then the filter is applied in memory with LINQ to Objects.

Would be interesting to know if the case-sensitivity disappears if you return an Expression.

(Just as a note about your Case-Sensitivity problem, not a solution to your general question about the best way to implement a Search feature.)