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.

BrokenGlass · Accepted Answer · 2011-08-10 18:31:41Z

5

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.

answered Aug 10, 2011 at 18:31

BrokenGlass

161k28 gold badges297 silver badges339 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ChevCast Over a year ago

Sounds good. I'm glad that there is a standard way to do this. I don't mind if I have to create a stored procedure since I can map those as methods in Entity Framework. I will follow your link. Thank you.

Slauma · Accepted Answer · 2011-08-10 18:53:50Z

3

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.)

answered Aug 10, 2011 at 18:53

Slauma

177k60 gold badges407 silver badges424 bronze badges

1 Comment

ChevCast Over a year ago

Nevermind, I misunderstood. I was unaware my Func would cause it to pull into memory. You were correct. Expression did indeed fix the case sensitivity. Thanks!

Keith Rousseau · Accepted Answer · 2011-08-10 18:34:46Z

0

I would look at Lucene.Net for a good search provider.

answered Aug 10, 2011 at 18:34

Keith Rousseau

4,4931 gold badge24 silver badges28 bronze badges

Comments

user118190 · Accepted Answer · 2012-02-24 07:43:13Z

0

As per your searches using LINQ, you can try something like this and it works just fine:

(x.Title).ToUpper().Contains(query.ToUpper())

Or the similar method of ToLower() should suffice as well.

For databases that truly consider an uppercase 'A' different from a lowercase 'a' as different values, which they are technically are, the above LINQ procedure will get you the results.

Hope this helps.

answered Feb 24, 2012 at 7:43

user118190

2,2077 gold badges31 silver badges45 bronze badges

Collectives™ on Stack Overflow

How do I implement a search feature with Entity Framework?

4 Answers 4

1 Comment

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related