Sql Server Freetext through Entity Framework

Question

I have an existing website developped using ASP.NET MVC 3 and Entity Framework 4 querying an Sql Server 2008 database. It contains a search form with around 10 fields, and when the user clicks the submit button I dynamically create an Entity SQL request containing only the specified search fields, omitting the empty ones. It works. So far so good.

Now, the client wants a Full Text Search behavior for one of the fields. I see this request as being pretty complex because (AFAIK) :

• Entity Framework does not natively supports full text search
• I want to avoid stored procedures to wrap the FTS syntax because so far I've only used "static" SPs, keeping the logic in the .NET code. So I want to try to avoid building the query inside the procedure. And creating one procedure per possible search field combination is not an option.

Solutions I could think of so far :

• Putting a stored procedure or user defined function as a seach preadicate in the WHERE clause (i'm not sure that's possible though)
• Getting the FTS results alone in a temporary table, and execute the other filters on that temporary table. I'm afraid of poor performances if there are a lot of FTS results with this technique...

What's the best way for daeling with this ?

Answer 1

Can you not just use raw sql? then you can keep the logic in your .NET code.

So, it would look something like:

string sql = "DO FULLTEXT STUFF";
MyObjectContext.ExecuteStoreQuery<MyEntity>(sql, .......);

Answer 2

You don't have to think about performance - this will be slow anyway because you will be replacing indexed full text search with standard string comparing on concatenated value.

There are three ways to go:

• Dynamically create ESQL query as you do now but use LIKE on concatenated value of your columns
• User defined SQL function or model defined function for evaluating search check imported to your EDMX model and exposed for Linq-to-entities queries.
• Instead of searching on table directly use view with computed column (containing all ten fields) and run "full text" on that column.

Any of this methods is not solution for performance.