Make Entity Framework use Contains instead of Like and explain 'ESCAPE ~'

Question

I have a line of LINQ that im using in EF which is basically doing myTable.Where(c => c.Contains('mystring'));

This is the generated code:

SELECT TOP (300) 
[Extent1].[ID] AS [ID], 
[Extent1].[FKFishEntityID] AS [FKFishEntityID], 
[Extent1].[Fish] AS [Fish], 
[Extent1].[FishText] AS [FishText], 
[Extent1].[FishType] AS [FishType]
FROM [dbo].[Fish] AS [Extent1]
WHERE [Extent1].[FishText] LIKE @p__linq__0 ESCAPE '~'

My two questions are:

• How do I make it use CONTAINS(...) instead of LIKE? It seems that LIKE is very slow when the table is using full text indexing. Copying and pasting the query it takes 4 seconds to execute, but if I change LIKE to CONTAINS() it executes instantly.

• Why does it do ESCAPE '~' ? By copying + pasting this into SQL server, it executes around 4 times faster if I remove the 'ESCAPE' part.

Answer 1

from the [entity framework blog]:1

There is no native support for full-text search planned at the moment. You would need to use a raw SQL query.

Seems that the way to go is something like this:

using (var context = new BloggingContext())
{
    var fishes = context.Fishes.SqlQuery("SELECT * FROM dbo.Fishes WHERE CONTAINS(FishText, @p0)", searchPhrase).ToList();
}