“可组合”全文检索与代码第一模型 [英] A "Composable" Full Text Search with a Code First Model

问题描述

更新 2013年9月18日

UPDATE 18 Sep 2013

看起来没有一个简单的方法来做到这一点。我正在提出一个解决方案，涉及实体框架的一些扩展。

It looks like there isn't an easy way to do this. I'm holding out for a solution that involves some extension to Entity Framework.

如果您想在Entity Framework中看到这些功能，请在<一个href =http://data.uservoice.com/forums/72025-ado-net-entity-framework-ef-feature-suggestions =nofollow>用户语音网站，也许 here 和 here

If you'd like to see these features in Entity Framework, vote for them on the user voice site, perhaps here and here

在SO上有几个类似的问题，但我找不到一个新的和类似的问题，答案我正在寻找。

There are several similar questions on SO but I can't find a question new and similar enough to have the answer I'm looking for.

如果这看起来像信息过载，请跳到总结。

If this looks like information overload, jump down to In Summary.

背景

我正在写一个网页Api REST服务通过OData端点公开一些预先存在的数据。我正在使用 EntitySetContoller< TEntity，TKey> 为我做所有的咕噜作品。除了标准OData参数外，还有路由并转换为基类，我添加了一些自定义参数，以允许我的控制器的特定功能。

I'm writing a WebApi REST service to expose some pre-existing data through an OData end point. I'm using the EntitySetContoller<TEntity, TKey> to do all the grunt work for me. As well as the standard OData parameters, that are routed and translated by the base class, I've added some custom parameters, to allow specific functionality for my controller.

我的数据库服务器是具有全文的MS SQL Server [BigText] NVarChar [4000] 列中的 [SomeEntity] 表的索引。

My database server is MS SQL Server with a full text index on the [BigText] NVarChar[4000] column of the [SomeEntity] table.

我有一个限制，我必须使用Code First模型。

// Model POCO
public class SomeEntity
{
    public int Id { get; set; }
    public string BigText { get; set; }
}

// Simple Controller
public class SomeEntityController : EntitySetController<SomeEntity, int>
{
    private readonly SomeDbContext context = new SomeDbContext();

    public override IQueryable<SomeEntity> Get()
    {
        var parameters = Request.GetQueryNameValuePairs()
            .ToDictionary(p => p.Key, p => p.Value);

        if (parameters.ContainsKey("BigTextContains")
        (
            var searchTerms = parameters["BigTextContains"];
            // return something special ... 
        )

        return this.context.SomeEntities;
    }

    // ... The rest is omitted for brevity.
}

问题

如何实现 //返回一些特殊的东西... 我的例子中的一部分

How to implement the // return something special ... part of my example?

显然， niave

return this.context.SomeEntities.Where(e =>
    e.BigText.Contains(searchTerm));

完全错误，它组成一个 WHERE 子句如

is completely wrong, it composes to a WHERE clause like

[BigText] LIKE '%' + @searchTerm + '%'

这不使用全文搜索，所以不支持复杂的搜索术语，否则执行terribley。

This doesn't use Full Text Searching so, doesn't support complex search terms and otherwise, performs terribley.

此方法

return this.context.SomeEntities.SqlQuery(
    "SELECT E.* FROM [dbo].[SomeEntity] E " +
        "JOIN CONTAINSTABLE([SomeEntity], [BigText], @searchTerm) FTS " +
            " ON FTS.[Key] = E.[Id]",
    new object[] { new SqlParameter("@searchTerm", searchTerm) })
    .AsQueryable();

看起来很有前途，它实际上使用全文搜索，功能非常强大。不过，您会注意到， DbSqlQuery ，从 SqlQuery 函数返回的类型不实现 IQueryable 。在这里，它被强制为正确的返回类型与 AsQueryable（）扩展，但这打破了组合链。将在服务器上执行的唯一语句是上面代码中指定的语句。在OData URL上指定的任何附加条款将在API托管Web服务器上进行服务，而不会从数据库引擎的索引和专门的基于集合的功能中获益。

Looks promising, it actually uses Full Text Searching, and is quite functional. However, you'll note that DbSqlQuery, the type returned from the SqlQuery function does not implement IQueryable. Here, it is coerced to the right return type with the AsQueryable() extension but, this breaks the "chain of composition". The only statement that will be performed on the server is the one specified in the code above. Any additional clauses, specified on the OData URL will be serviced on the API hosting web server, without benefitting from the indices and specialised set based functionality of the database engine.

总结

访问MS SQL Server的全文搜索的最方便的方法是什么？ CONTAINSTABLE 功能与实体框架5代码第一模型并获取可组合结果？

What is the most expedient way of accessing MS SQL Server's Full Text Search CONTAINSTABLE function with an Entity Framework 5 Code First model and acquiring a "composable" result?

我需要写我自己的 IQueryProvider ？我可以以某种方式扩展EF吗？

Do I need to write my own IQueryProvider? Can I extend EF in some way?

我不想使用Lucene.Net，我不想使用数据库生成的模型。也许我可以添加额外的包或等待EF6，这有帮助吗？

I don't want to use Lucene.Net, I don't want to use a Database Generated Model. Perhaps I could add extra packages or wait for EF6, would that help?

推荐答案

这不是完美的，但你可以完成什么你之后有两次调用数据库。
第一个调用将从CONTAINSTABLE中检索匹配键的列表，然后第二个调用将是使用从第一个调用返回的ID的可组合查询。

It is not perfect, but you can accomplish what you are after with 2 calls to the database. The first call would retrieve a list of matching key's from CONTAINSTABLE and then the second call would be your composable query utilizing the IDs that you returned from the first call.

//Get the Keys from the FTS
var ids = context.Database.SqlQuery<int>( 
          "Select [KEY] from CONTAINSTABLE([SomeEntity], [BigText], @searchTerm)", 
          new object[] { new SqlParameter("@searchTerm", searchTerm) });

//Use the IDs as an initial filter on the query
var composablequery = context.SomeEntities.Where(d => ids.Contains(d.Id));

//add on whatever other parameters were captured to the 'composablequery' variable
composablequery = composablequery.Where(.....)

这篇关于“可组合”全文检索与代码第一模型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！