Rewrite query to achieve full text search using EF Core

[AshishMoradiya]

Hello,

I am developing MVC6 application with EF7, I want to use full text search(SQL server feature). full text feature requires special type of query with 'CONTAINS' or 'FREETEXT' keyword. For more details see this

EF does not generate query that is supported by full text search. that is why I need to rewrite query before it goes to execution to SQL Server. Previously with EF6 I was using Interception to rewrite the query. See example here.

I have noticed that IDbCommandInterceptor interface is removed from EF7. So, I need to find out way for EF7. I have seen there are many classes to handle raw SQL functionality in DI. we might need put something on DI? (see attached picture)

I have also considered RelationalCommand and SqlStatementExecutor. RelationalCommand has similar functionality. we want to customize it to rewrite query.
Any solution to achieve this goals?

Thank you,
Ashish

[zabulus]

Maybe it should be supported via SqlServer provider via extension method for DbContext?

[rowanmiller]

We have #1590 tracking proper support for full text search. In the meantime, the best way to do this is with FromSql. You can now compose on top of this with LINQ, so you aren't restricted to raw SQL even though you used raw SQL to initiate the query.

Here is an example that is using a TVF with full text search and then composing on the query with LINQ https://github.com/rowanmiller/UnicornStore/blob/master/UnicornStore/src/UnicornStore/Controllers/ShopController.cs#L92

[d668]

come on, why would I use FromSql? I have a 3 page query generated by EF and I just need to replace LIKE to CONTAINS

[d668]

BTW @Ashish225 the http://www.entityframework.info/Home/FullTextSearch is dead

[bbrandt]

@rowanmiller How would the suggestion to use FromSql() work if the predicate was against a property of a navigation property of the entity?

[ajcvickers]

@bbrandt You are commenting on an old closed issue here. Full text search was implemented in EF Core 3.1, as tracked in #11484.