如何使用实体框架6执行全文搜索 [英] How to execute a full text search using entity framework 6

问题描述

我有以下查询：

var query = DataContext.Fotos.Where(x => x.Pesquisa.Contais("myTerm")

生成的SQL是：

SELECT 
...
FROM Fotos AS [Extent1]
WHERE [Extent1].[Pesquisa] LIKE N'%mytem%'

但是我需要使用：

SELECT 
...
FROM Fotos AS [Extent1]
WHERE CONTAINS ([Extent1].[Pesquisa], 'my term')

如何使用实体框架6执行全文搜索？

How to execute a full text search using entity framework 6?

推荐答案

似乎Entity Framework 6不支持全文本搜索，但存在拦截器的解决方法。

Seems that Entity Framework 6 does not support full text search, but there is a workaround with interceptors.

http://www.entityframework.info/Home/FullTextSearch

更新链接不起作用，因此原始内容如下：

Update Link doesn't work so here is the original content:

Microsoft TSQL通过谓词
（包含内容和FREETEXT）支持全文查询

Microsoft TSQL supports full-text query by means of predicates (CONTAINS and FREETEXT)

例如，您有表注释

Create table Notes (
    Id int Identity not null,
    NoteText text 
)

CREATE FULLTEXT CATALOG [Notes Data]

在此表中进行搜索时包含单词'John'的记录，您
需要发出

When you search this table for records containing word 'John', you need to issue

SELECT TOP (10) 
* from gps.NOTES
WHERE contains(NoteText, '(john)') 

不幸的是，实体框架仍不支持全文搜索
谓词。对于EFv6，您可以使用
拦截来解决。

Unfortunately, Enity framework does not support full-text search predicates still. For EFv6, you can make a workaround using interception.

这个想法是在
纯字符串内部用一些魔术词包装搜索文本。包含代码并使用拦截器在$ $$$ b之前将其解包，然后在SqlCommand中执行sql。

The idea is to wrap search text with some magic word during inside plain String.Contains code and use interceptor to unwrap it right before sql is executed in SqlCommand.

首先，创建拦截器类：

public class FtsInterceptor : IDbCommandInterceptor
{
    private const string FullTextPrefix = "-FTSPREFIX-";
    public static string Fts(string search)
    {
    return string.Format("({0}{1})", FullTextPrefix, search);
    }
    public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
    }
    public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
    }
    public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        RewriteFullTextQuery(command);
    }
    public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
    }
    public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
        RewriteFullTextQuery(command);
    }
    public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
    }
    public static void RewriteFullTextQuery(DbCommand cmd)
    {
        string text = cmd.CommandText;
        for (int i = 0; i < cmd.Parameters.Count; i++)
        {
            DbParameter parameter = cmd.Parameters[i];
            if (parameter.DbType.In(DbType.String, DbType.AnsiString, DbType.StringFixedLength, DbType.AnsiStringFixedLength))
            {
                if (parameter.Value == DBNull.Value)
                    continue;
                var value = (string)parameter.Value;
                if (value.IndexOf(FullTextPrefix) >= 0)
                {
                    parameter.Size = 4096;
                    parameter.DbType = DbType.AnsiStringFixedLength;
                    value = value.Replace(FullTextPrefix, ""); // remove prefix we added n linq query
                    value = value.Substring(1, value.Length - 2); // remove %% escaping by linq translator from string.Contains to sql LIKE
                    parameter.Value = value;
                    cmd.CommandText = Regex.Replace(text,
                    string.Format(
                    @"\[(\w*)\].\[(\w*)\]\s*LIKE\s*@{0}\s?(?:ESCAPE
                    N?'~')",parameter.ParameterName),
                    string.Format(@"contains([$1].[$2], @{0})",parameter.ParameterName));
                    if (text == cmd.CommandText)
                        throw new Exception("FTS was not replaced on: " + text);
                    text = cmd.CommandText;
                }
            }
        }
    }
}

我使用了扩展函数，可以这样定义：

I used extension function In that can be defined like this:

static class LanguageExtensions
{
    public static bool In<T>(this T source, params T[] list)
    {
        return (list as IList<T>).Contains(source);
    }
}

现在让我们组成一个示例如何使用它。我们需要实体类注意：

Now lets compose a sample how to use it. We need entity class Note:

public class Note
{
    public int Id { get; set; }
    public string NoteText { get; set; }
}

为其配置映射：

public class NoteMap : EntityTypeConfiguration<Note>
{
    public NoteMap()
    {
        // Primary Key
        HasKey(t => t.Id);
    }
}

和我们的DbContext祖先：

And our DbContext ancestor:

public class MyContext : DbContext
{
    static MyContext()
    {
        DbInterception.Add(new FtsInterceptor());
    }
    public MyContext(string nameOrConnectionString) : base(nameOrConnectionString)
    {
    }
    public DbSet<Note> Notes { get; set; }
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Configurations.Add(new NoteMap());
    }
}

现在我们可以使用它了。让我们搜索 john：

Now we ready to use it. Lets search for 'john':

class Program
{
    static void Main(string[] args)
    {
        var s = FtsInterceptor.Fts("john");
        using (var db = new MyContext("CONNSTRING"))
        {
            var q = db.Notes.Where(n => n.NoteText.Contains(s));
            var result = q.Take(10).ToList();
        }
    }
}

这篇关于如何使用实体框架6执行全文搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！