A＆QUOT;可组合＆QUOT;全文检索与C首先模型中$ C $ [英] 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.
如果你想看到的实体框架这些功能，投给他们上的用户语音网站，也许<一href="http://data.uservoice.com/forums/72025-entity-framework-feature-suggestions/suggestions/1560711-full-text-search">here和<一href="http://data.uservoice.com/forums/72025-entity-framework-feature-suggestions/suggestions/2686351-$c$c-first-support-for-table-valued-functions">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.
背景
我正在写一个的WebAPI REST服务通过OData的终点揭露一些pre存在的数据。我使用的<一个href="http://msdn.microsoft.com/en-us/library/jj890573%28v=vs.111%29.aspx"><$c$c>EntitySetContoller<TEntity, TKEY的＆GT; 为我做的所有繁重的工作。以及在<一个href="http://www.odata.org/documentation/odata-v2-documentation/uri-conventions/#4_Query_String_Options">standard 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服务器上的 [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第一种模式。
// 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));
是完全错误的，它组成一个，其中喜欢
[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();
看起来有前途的，它实际上使用全文搜索，而且是相当的功能。然而，你会注意到，<一个href="http://msdn.microsoft.com/en-us/library/gg679457%28v=vs.103%29.aspx"><$c$c>DbSqlQuery,从类SqlQuery 函数返回的类型不执行的IQueryable 。这里，将强制使用正确的返回类型与 AsQueryable已（）扩展，但是，这打破了产业链的构成。将要在服务器上执行的唯一的语句是在code以上指定的。任何附加条款，对OData的URL中指定将被服务的API托管Web服务器上，而不从indecies和数据库引擎的专业化一套基于功能中获益。
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 indecies and specialised set based functionality of the database engine.
总结
什么是访问MS SQL Server的全文搜索 CONTAINSTABLE的最方便的方法 功能与实体框架5 code首先模型，并获得了组合的结果呢？
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?
我是否需要写我自己的<一href="http://msdn.microsoft.com/en-us/library/system.linq.iqueryprovider.aspx"><$c$c>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?
推荐答案
它不是完美的，但你可以做到你是什么后，用2调用数据库。 第一次调用将检索匹配键的距离CONTAINSTABLE的列表，然后第二个电话是你利用你从第一次调用返回的标识组合的查询。
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(.....)
这篇关于A＆QUOT;可组合＆QUOT;全文检索与C首先模型中$ C $的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
