Full text search and Entity Framework v1: is it possible?

Question

We're working in EFv1 and I'm implementing a search control with several filter options. Most of them I translate into .Where clauses and that is ok with EF.

But there is one specific filter that can make my application run very slow: text search. The column in the database has a corresponding entity property in my EF model, but I just can't put a Contains in there, this is not good for text searching.

What do you feel is good to use in this case?

I've heard of Lucene, I don't know if this integrates with EFv1.

Thanks

Answer 1

You can write User Defined Function that searches using Full Text Search, expose it to Entity Framework and use in C#. I've done this for LINQ to SQL, but for EF it should be the same. Please look at this blog post: http://sqlblogcasts.com/blogs/simons/archive/2008/12/18/LINQ-to-SQL---Enabling-Fulltext-searching.aspx

Answer 2

I ran into a similar problem and it was one of those cases where using a stored procedure just made sense. I think with any ORM you will always have queries that need to be precisely optimized and you just can't rely on the tool to do it for you.

Importing stored procedures in EF is really easy, the only problem is composing that function call with other linq operators. EF v1 won't do this on the server side, which means you will be doing in memory filtering.

As a note to the other answer, you can't really do function imports in EF v1. See Here

Answer 3

I've done this successfully. The steps were:

• Write a stored procedure that does the full text index search.
• Update your model definition ("Update Model From Database" in the context menu). You'll need to add the new Stored Procedure
• Create a Function import (in the Model Browser window, right click on the new Stored Procedure and Create a Function Import). Set the return type to be an appropriate return type (one of your entities).

You will then have a method you can call on your Context which will return an EntityCollection<>.

Answer 4

Lucene.NET won't integrate directly with EF, but you can use it first to get a list of candidate entity ID's that you can then pass to your EF query.