使实体框架使用 Contains 而不是 Like 并解释 'ESCAPE ~' [英] Make Entity Framework use Contains instead of Like and explain 'ESCAPE ~'

问题描述

我有一行 LINQ，我在 EF 中使用它基本上是在做 myTable.Where(c => c.Contains('mystring'));

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

这是生成的代码:

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 '~'

我的两个问题是:

• 如何让它使用 CONTAINS(...) 而不是 LIKE?当表使用全文索引时，LIKE 似乎非常慢.复制和粘贴查询需要 4 秒才能执行，但如果我将 LIKE 更改为 CONTAINS() 它会立即执行.

• 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.

为什么要 ESCAPE '~' ?通过将其复制并粘贴到 SQL Server 中，如果我删除 'ESCAPE' 部分，它的执行速度会快 4 倍左右.

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

推荐答案

来自【实体框架博客】:1

from the [entity framework blog]:1

目前没有计划对全文搜索提供原生支持.您需要使用原始 SQL 查询.

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();
}

这篇关于使实体框架使用 Contains 而不是 Like 并解释 'ESCAPE ~'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！