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 ?

score 6 · Accepted Answer · answered May 06 '11 at 21:37

6

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

answered May 06 '11 at 21:37

Lee Gunn

8,417
4
38
33

+1 This is good point. By doing it this way you can even introduce Full text search on SQL server. I think `sp_executesql` is not needed because it is used by `ExecuteStoreCommand` directly. – Ladislav Mrnka May 06 '11 at 21:41
2

Thanks for this suggestion. I tried to enhance it by keeping the entity query building, then generating the resulting SQL query and replacing the LIKE precidate with a FREETEXT one, before executing it. – Shtong May 10 '11 at 11:34

score 1 · Answer 2 · edited May 23 '17 at 10:34

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.

Sql Server Freetext through Entity Framework

2 Answers2