Entity Framework Code First - field indexing and full text search

By the nature of my work, I often have to do various small projects, mainly, these are sites written in ASP.NET MVC. In any modern project there is data, and therefore a database, which means you need to work with it somehow.
If we discard all the discussions about the pros and cons, then I hasten to inform you that my choice fell on the Entity Framework Code First . During the development of the project, I pay attention only to business logic and do not spend time on database design and other standard actions. An unpleasant surprise when using this approach was for me the lack of an opportunity “out of the box” for the Entity Framework to build an index across the fields, as well as use a convenient and modern full-text search mechanism.

After hours of googling, having tested dozens of different methods from StackOverflow and other similar sites, I came to the conclusion that there is no obvious and simple solution to the problem, so I decided to make my own, which will be discussed further.

Implementation

The main requirement to solve the problem is the ease of integration into any new (existing) project. In Code First, it is customary to customize everything with attributes, so it would be good to do this:
')

public class SomeClass { public int Id { get; set; } [Index] public string Name { get; set; } [FullTextIndex] public string Description { get; set; } } 

at the same time, I would not want to override the DatabaseInitializer and do other non-trivial actions.

In my work, I use Visual Studio 2013 Ultimate. Create a new project of the Class Library type, immediately add the Entity Framework 6 Beta 1 to it using the NuGet console (Package Manager Console):

PM> Install-Package EntityFramework -Pre

Create the Index and FullTextSearch attributes, as well as the listing for FullTextSearch:

 public class IndexAttribute : Attribute { } public class FullTextIndexAttribute : Attribute { } public class FullTextIndex { public enum SearchAlgorithm { Contains, FreeText } } 

If you have previously worked with full-text search, then you probably understand why you need Contains and FreeText, if not, then you are here .
Next, create an abstract class inherited from DbContext:

 public abstract class DbContextIndexed : DbContext { private static bool Complete; private int? language; public int Language { get { return language.HasValue ? language.Value : 1049; //1049 -   } set { language = value; } } protected override void Dispose(bool disposing) { if (!Complete) { Complete = true; CalculateIndexes(); } base.Dispose(disposing); } private void CalculateIndexes() { if (GetCompleteFlag()) return; //    DbContext foreach (var property in this.GetType() .GetProperties() .Where(f => f.PropertyType.BaseType != null && f.PropertyType.BaseType.Name == "DbQuery`1")) { var currentEntityType = property.PropertyType.GetGenericArguments().FirstOrDefault(); if (currentEntityType == null || currentEntityType.BaseType.FullName != "System.Object") continue; //     var tableAttribute = currentEntityType .GetCustomAttributes(typeof(TableAttribute), false).FirstOrDefault() as TableAttribute; var tableName = tableAttribute != null ? tableAttribute.Name : property.Name; //      Index,     BuildingIndexes(tableName, currentEntityType.GetProperties() .Where(f => f.GetCustomAttributes(typeof(IndexAttribute), false) .Any())); //      FullTextIndex,     BuildingFullTextIndexes(tableName, currentEntityType.GetProperties() .Where(f => f.GetCustomAttributes(typeof(FullTextIndexAttribute), false) .Any())); } CreateCompleteFlag(); } private void BuildingIndexes(string tableName, IEnumerable<PropertyInfo> propertyes) { foreach (var property in propertyes) Database.ExecuteSqlCommand(String.Format("CREATE INDEX IX_{0} ON {1} ({0})", property.Name, tableName)); } private void BuildingFullTextIndexes(string tableName, IEnumerable<PropertyInfo> propertyes) { var fullTextColumns = string.Empty; foreach (var property in propertyes) fullTextColumns += String.Format("{0}{1} language {2}", (string.IsNullOrWhiteSpace(fullTextColumns) ? null : ","), property.Name, Language); //   Database.ExecuteSqlCommand(System.Data.Entity.TransactionalBehavior.DoNotEnsureTransaction, String.Format("IF NOT EXISTS (SELECT * FROM sysindexes WHERE id=object_id('{1}') and name='IX_{2}') CREATE UNIQUE INDEX IX_{2} ON {1} ({2}); CREATE FULLTEXT CATALOG FTXC_{1} AS DEFAULT; CREATE FULLTEXT INDEX ON {1}({0}) KEY INDEX [IX_{2}] ON [FTXC_{1}]", fullTextColumns, tableName, "Id")); } private void CreateCompleteFlag() { Database.ExecuteSqlCommand(System.Data.Entity.TransactionalBehavior.DoNotEnsureTransaction, "CREATE TABLE [dbo].[__IndexBuildingHistory]( [DataContext] [nvarchar](255) NOT NULL, [Complete] [bit] NOT NULL, CONSTRAINT [PK___IndexBuildingHistory] PRIMARY KEY CLUSTERED ([DataContext] ASC))"); } private bool GetCompleteFlag() { var queryResult = Database.SqlQuery(typeof(string), "IF OBJECT_ID('__IndexBuildingHistory', 'U') IS NOT NULL SELECT 'True' AS 'Result' ELSE SELECT 'False' AS 'Result'") .GetEnumerator(); queryResult.MoveNext(); return bool.Parse(queryResult.Current as string); } } 

In order not to inflate the post, here the summary and some comments are intentionally removed, the full version on GitHub . If we briefly explain, EF creates a model during the initial access to DbContext, we cannot build indexes on the constructor accordingly, it remains the easiest way to build them after creating the model, when trying to destroy an instance of DbContext. Further, in order not to load the database each time with several queries and an attempt to create, in the best EF traditions, we will create an __IndexBuildingHistory service table in the database, the presence of which will indicate the presence of indices. The rest is obvious.

In general, if you now create a model, mark it with attributes and start a project, then the indexes will be successfully created, however, we still need to conveniently use the full-text index, for this we will create an extension class:

 public static class IQueryableExtension { public static IQueryable<T> FullTextSearch<T>(this DbSet<T> queryable, Expression<Func<T, bool>> func, FullTextIndex.SearchAlgorithm algorithm = FullTextIndex.SearchAlgorithm.FreeText) where T : class { var internalSet = queryable.AsQueryable() .GetType() .GetProperty("System.Data.Entity.Internal.Linq.IInternalSetAdapter.InternalSet", BindingFlags.NonPublic | BindingFlags.Instance) .GetValue(queryable.AsQueryable()); var entitySet = (EntitySet)internalSet.GetType() .GetProperty("EntitySet") .GetValue(internalSet); var searchType = algorithm == FullTextIndex.SearchAlgorithm.Contains ? "CONTAINS" : "FREETEXT"; var columnName = ((MemberExpression)((BinaryExpression)func.Body).Left).Member.Name; var searchPattern = ((ConstantExpression)((BinaryExpression)func.Body).Right).Value; return queryable.SqlQuery( String.Format("SELECT * FROM {0} WHERE {1};", entitySet.Name, String.Format("{0}({1},'{2}')", searchType, columnName, searchPattern))) .AsQueryable(); } } 

That's all, it would seem, such a popular problem as indexes and full-text search requires special attention from the creators of the Entity Framework, however, there is no simple solution to date. This implementation more than meets my requirements for indexing, if something is missing for you (error handling, settings - for example, a list of stop words, etc.), you can pick up the project yourself from GitHub and modify it, or write to me . The article would be quite boring if we did not try how it all works, so we turn to use.

Using

1. Create a project Console application
2. Add Entity Framework 6 beta via NuGet
3. Add a link to the library (if you have not read about the implementation, then you can download the finished library, links at the end of the article)
4. Create a simple entity, without nestings and links, for example this is enough:

 public class Animal { public int Id { get; set; } [Index] [StringLength(200)] public string Name { get; set; } [FullTextIndex] public string Description { get; set; } public int Family { get; set; } [FullTextIndex] public string AdditionalDescription { get; set; } } 

The entity is an animal, with the name (Name), according to which we build a regular index, a description (Description) - we construct a full-text index and other fields for the view, we will not use them. Pay attention to the string [StringLength (200)], when creating an index on string fields it is required, because MSSQL allows you to build indexes on fields whose size does not exceed 900 bytes - how much is in characters depends on the database coding you choose.

5. Create a database context:

 public class DataContext : DbContextIndexed { public DbSet<Animal> Animals { get; set; } } 

the only difference here is in the inheritance, usually you inherit from DbContext, and now from our DbContextIndexed

6. In Programm.cs, add a context call to trigger the creation of a database:

 static void Main(string[] args) { using (var context = new DataContext()) { var temp = context.Animals.ToList(); } } 

7. In the config file of the project, enter the database connection string with the name DataContext:

 <configuration> <connectionStrings> <add name="DataContext" connectionString="Data Source=(local)\SQL; Initial Catalog=EFCF; Integrated Security=true;" providerName="System.Data.SqlClient"/> </connectionStrings> </configuration> 

8. Press F5 to create a database when the program is completed, using Managment Studio you can make sure that everything is working as we planned:


9. Now, let's try adding data to try the search:

 using (var context = new DataContext()) { context.Animals.Add(new Animal { Name = "", Description = "   ,   ." }); context.Animals.Add(new Animal { Name = "", Description = "     ,    ." }); context.Animals.Add(new Animal { Name = "", Description = " -    ." }); context.Animals.Add(new Animal { Name = "", Description = "  ." }); context.Animals.Add(new Animal { Name = "", Description = "  ." }); context.Animals.Add(new Animal { Name = "  ", Description = "  ,     ." }); context.SaveChanges(); } 

let's start the data to be recorded in the database, now let's try to search:

 using (var context = new DataContext()) { foreach (var pet in context.Animals.FullTextSearch(f => f.Description == "")) Console.WriteLine("{0} - {1}", pet.Name, pet.Description); } 

the result is as follows:


I have installed the MSSQL 2008R2 version, so the result is good, but not perfect. As far as I know, in the 2013 version we would still get the panther value, since "Cat", too, would study.

I think that quite simple, and most importantly, using the “standard” method, you can use full-text search and build indexes by fields. This implementation is enough for 95% of small projects, but I sincerely hope that the creators of the Entity Framework still implement this functionality "in the box."

Sources

Download ready library:
in zip format
in dll format
Project posted on GitHub
Entity Framework 6 beta on the Nuget website