实体框架、代码优先和全文搜索 [英] 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).
我真正的问题是对于 Entity Framework 4.3 和 Entity Framework 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?
谢谢，埃里克
推荐答案
使用 EF6 中引入的拦截器，您可以在 linq 中标记全文搜索，然后按照 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*LIKEs*@{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 的 Note 类:
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 查询:
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();
}
}
}
这将生成类似
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屋！
