Entity Framework, first code and full text search

Question

I understand that many questions have been asked regarding full-text search and Entity Framework, but I hope this question is slightly different.

I am using Entity Framework, Code First and should do a full text search. When I need to do a full text search, usually I will have other criteria / restrictions - for example, skip the first 500 rows or filter another column, etc.

I see that this was handled using table functions - see http://sqlblogcasts.com/blogs/simons/archive/2008/12/18/LINQ-to-SQL---Enabling-Fulltext-searching.aspx . And that seems like the right idea.

Unfortunately, table functions are not supported until Entity Framework 5.0 (and even then, I believe, they are not supported for Code First).

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:

• Besides dynamic SQL (e.g. via System.Data.Entity.DbSet.SqlQuery ) are there any options for Entity Framework 4.3?

• If I upgrade to Entity Framework 5.0, is there any way I can use table-valued functions first?

Thanks Eric

Answer 1

Using the interceptors introduced in EF6, you can mark the full text search in linq and then replace it with 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); } } 

For example, if you have a Note class with an FTS-indexed NoteText field:

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

and EF for him

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

and context for it:

 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()); } } 

you can have a pretty simple syntax for an FTS request:

 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(); } } } 

This will generate SQL as

 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) 

Note that you must use a local variable and cannot move the FTS shell inside an expression, for example

 var q = db.Notes.Where(n => n.NoteText.Contains(FtsInterceptor.Fts("john"))); 

Answer 2

I found that the easiest way to implement this is to configure and configure full-text search in SQL Server, and then use the stored procedure. Pass your SQL arguments, let the database do its job, and return either a complex object or match the results with an entity. You do not have to have dynamic SQL, but it may be optimal. For example, if you need paging, you can pass PageNumber and PageSize for each query without the need for dynamic SQL. However, if the number of arguments varies for each request, this will be the optimal solution.

Answer 3

As the other guys said, I would say I'm starting to use Lucene.NET

Lucene has a pretty high learning curve, but I found a wrapper for it called " SimpleLucene " that can be found on CodePlex

Let me give you a few code blocks from a blog to show you how easy it is to use. I just started using it, but got it very quickly.

First, get some entities from your repository or in your case use the Entity Framework

 public class Repository { public IList<Product> Products { get { return new List<Product> { new Product { Id = 1, Name = "Football" }, new Product { Id = 2, Name = "Coffee Cup"}, new Product { Id = 3, Name = "Nike Trainers"}, new Product { Id = 4, Name = "Apple iPod Nano"}, new Product { Id = 5, Name = "Asus eeePC"}, }; } } } 

The next thing you want to do is create an index definition

 public class ProductIndexDefinition : IIndexDefinition<Product> { public Document Convert(Product p) { var document = new Document(); document.Add(new Field("id", p.Id.ToString(), Field.Store.YES, Field.Index.NOT_ANALYZED)); document.Add(new Field("name", p.Name, Field.Store.YES, Field.Index.ANALYZED)); return document; } public Term GetIndex(Product p) { return new Term("id", p.Id.ToString()); } } 

and create a search index for it.

 var writer = new DirectoryIndexWriter( new DirectoryInfo(@"c:\index"), true); var service = new IndexService(); service.IndexEntities(writer, Repository().Products, ProductIndexDefinition()); 

So now you have a search index. The only thing to do is ... search! You can do some pretty interesting things, but it can be that simple: (for more detailed examples, see the blog or documentation on codeplex)

 var searcher = new DirectoryIndexSearcher( new DirectoryInfo(@"c:\index"), true); var query = new TermQuery(new Term("name", "Football")); var searchService = new SearchService(); Func<Document, ProductSearchResult> converter = (doc) => { return new ProductSearchResult { Id = int.Parse(doc.GetValues("id")[0]), Name = doc.GetValues("name")[0] }; }; IList<Product> results = searchService.SearchIndex(searcher, query, converter); 

Answer 4

I recently had a similar requirement, and in the end I wrote the IQueryable extension specifically for full-text access to the Microsoft text index, its available here IQueryableFreeTextExtensions

Answer 5

An example here http://www.entityframework.info/Home/FullTextSearch is not a complete solution. You will need to understand how full-text search works. Imagine that you have a search field and the user types 2 words to search. The above code throws an exception. First you need to do the preprocessing in the search phrase in order to pass it to the query using a logical AND or OR.

For example, your search phrase is "blah blah2", then you need to convert it to:

 var searchTerm = @"\"blah\" AND/OR \"blah2\" "; 

Complete solution:

  value = Regex.Replace(value, @"\s+", " "); //replace multiplespaces value = Regex.Replace(value, @"[^a-zA-Z0-9 -]", "").Trim();//remove non-alphanumeric characters and trim spaces if (value.Any(Char.IsWhiteSpace)) { value = PreProcessSearchKey(value); } public static string PreProcessSearchKey(string searchKey) { var splitedKeyWords = searchKey.Split(null); //split from whitespaces // string[] addDoubleQuotes = new string[splitedKeyWords.Length]; for (int j = 0; j < splitedKeyWords.Length; j++) { splitedKeyWords[j] = $"\"{splitedKeyWords[j]}\""; } return string.Join(" AND ", splitedKeyWords); } 

This method uses the logical AND operator. You can pass this as an argument and use the method for AND or OR statements.

You must avoid alphanumeric characters, otherwise it will throw exceptions when the user enters alphanumeric characters, and you do not have a check of the site model level on the server.