How to implement a search function using the Entity Framework?

Question

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

I am currently looking for 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 of the big problems is that the search is case sensitive.

Question 1) Is there a better way to do a search using LINQ to Entities?

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

I just need a case insensitive search that runs in the database in order to maintain good performance.

Thanks in advance.

Answer 1

The standard approach for this is full-text SQL search. You will need to include the full text in the database, assign the columns (columns) so that they are indexed with the full text, and then you can use SQL. Contains queries using these columns.

Full-text search queries are not currently supported by Linq to Entities - you will have to use standard SQL queries for this. You can use ExecuteStoreQuery() to at least match search results with a typed result.

Answer 2

SQL Server default compatibility is case insensitive, which means where clause like this (this is what LINQ to Entities will create from Contains ) ...

 where Name like '%JOHN%' 

... should find "john Wayne". I believe that your request is not executed on the server, but in fact with LINQ to Objects in memory - and there the search is case sensitive. This is LINQ to Objects because you are not returning Expression from your BlogPostContains . Signature must be:

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

If you return only Func<BlogPost, bool> , you are working with an IEnumerable (and not IQueryable ) overload of the Where extension method, which in turn first loads the entire BlogPosts table into memory. The filter is then applied in memory with LINQ to Objects.

It would be interesting to know if case sensitivity disappears if you return Expression .

(As a note about your case-sensitivity problem, not a solution to the general question of the best way to implement the search function.)

Answer 3

According to your searches using LINQ, you can try something like this and it works great:

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

Or a similar ToLower () method should be sufficient.

For databases that truly consider the uppercase letter “A” different from the lowercase letter “a”, as different values ​​that they technically are, the above LINQ procedure will give you the results.

Hope this helps.

Answer 4

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