实体框架，代码优先和全文检索 [英] Entity Framework, Code First and Full Text Search
问题描述
我知道有很多问题涉及全文搜索和实体框架，但我希望这个问题有所不同。
I realize that a lot of questions have been asked relating to full text search and Entity Framework, but I hope this question is a bit different.
我正在使用实体框架，代码首先需要做一个全文搜索。当我需要执行全文搜索时，我通常也会有其他的标准/限制，例如跳过前500行，或者在另一列上进行过滤等。
I am using Entity Framework, Code First and need to do a full text search. When I need to perform the full text search, I will typically have other criteria/restrictions as well - like skip the first 500 rows, or filter on another column, etc.
我看到这已经使用表值函数处理了 - 参见 http://sqlblogcasts.com/blogs/simons/archive/2008/12/18/LINQ-to-SQL---Enabling-Fulltext-searching.aspx 。这似乎是正确的想法。
I see that this has been handled using table valued functions - see http://sqlblogcasts.com/blogs/simons/archive/2008/12/18/LINQ-to-SQL---Enabling-Fulltext-searching.aspx. And this seems like the right idea.
不幸的是，在Entity Framework 5.0之前不支持表值函数（即使这样，我相信它们不支持Code First ）。
Unfortunately, table valued functions are not supported until Entity Framework 5.0 (and even then, I believe, they are not supported for Code First).
我真正的问题是为实体框架4.3和实体框架5.0处理这一点的最佳方法是什么建议。但是要具体说明：
My real question is what are the suggestions for the best way to handle this, both for Entity Framework 4.3 and Entity Framework 5.0. But to be specific:
-
除了动态SQL（通过
System.Data.Entity.DbSet）例如，SqlQuery）是否有可用于Entity Framework 4.3的选项？
Other than dynamic SQL (via
System.Data.Entity.DbSet.SqlQuery, for example), are there any options available for Entity Framework 4.3?
如果升级到Entity Framework 5.0，是有一种方法可以使用代码优先的表值函数？
If I upgrade to Entity Framework 5.0, is there a way I can use table valued functions with code first?
谢谢，
Eric
Thanks, Eric
推荐答案
使用EF6中引入的拦截器，您可以在linq中标记全文搜索，然后以db命令替换，如 http://www.entityframework.info/Home/FullTextSearch ：
Using interceptors introduced in EF6, you could mark the full text search in linq and then replace it in dbcommand as described in http://www.entityframework.info/Home/FullTextSearch:
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;
}
}
}
}
}
static class LanguageExtensions
{
public static bool In<T>(this T source, params T[] list)
{
return (list as IList<T>).Contains(source);
}
}
例如，如果你有类FTS-索引字段NoteText：
For example, if you have class Note with FTS-indexed field NoteText:
public class Note
{
public int NoteId { get; set; }
public string NoteText { get; set; }
}
和EF地图
public class NoteMap : EntityTypeConfiguration<Note>
{
public NoteMap()
{
// Primary Key
HasKey(t => t.NoteId);
}
}
及其上下文：
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());
}
}
您可以对FTS查询有相当简单的语法： / p>
you can have quite simple syntax to FTS query:
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();
}
}
}
/ p>
That will generate SQL like
exec sp_executesql N'SELECT TOP (10)
[Extent1].[NoteId] AS [NoteId],
[Extent1].[NoteText] AS [NoteText]
FROM [NS].[NOTES] AS [Extent1]
WHERE contains([Extent1].[NoteText], @p__linq__0)',N'@p__linq__0 char(4096)',@p__linq__0='(john)
请注意你应该使用局部变量，并且不能将表达式内的FTS包装移动到
Please notice that you should use local variable and cannot move FTS wrapper inside expression like
var q = db.Notes.Where(n => n.NoteText.Contains(FtsInterceptor.Fts("john")));
这篇关于实体框架，代码优先和全文检索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
