I realize that a lot of questions have been asked relating to full text search and Entity Framework, but I hope this question is a bit different.
I am using Entity Framework, Code First and need to do a full text search. When I need to perform the full text search, I will typically have other criteria/restrictions as well – like skip the first 500 rows, or filter on another column, etc.
I see that this has been handled using table valued functions – see http://sqlblogcasts.com/blogs/simons/archive/2008/12/18/LINQ-to-SQL—Enabling-Fulltext-searching.aspx. And this seems like the right idea.
Unfortunately, table valued functions are not supported until Entity Framework 5.0 (and even then, I believe, they are not supported for Code First).
My real question is what are the suggestions for the best way to handle this, both for Entity Framework 4.3 and Entity Framework 5.0. But to be specific:
-
Other than dynamic SQL (via
System.Data.Entity.DbSet.SqlQuery
, for example), are there any options available for Entity Framework 4.3? -
If I upgrade to Entity Framework 5.0, is there a way I can use table valued functions with code first?
Thanks,
Eric
Best Answer
Using interceptors introduced in EF6, you could mark the full text search in linq and then replace it in dbcommand as described in http://www.entityframework.info/Home/FullTextSearch:
For example, if you have class Note with FTS-indexed field NoteText:
and EF map for it
and context for it:
you can have quite simple syntax to FTS query:
That will generate SQL like
Please notice that you should use local variable and cannot move FTS wrapper inside expression like