August 12, 2013 at 11:54Entity Framework Code First - Field Indexing and Full-Text Search
By the nature of my activity, I often have to do various small projects, mainly 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 that my choice fell on the Entity Framework Code First . During the development of the project, I pay attention exclusively to business logic and do not spend time on database design and other template actions. An unpleasant surprise when using this approach for me was the lack of the ability “out of the box” for the Entity Framework to build an index by fields, as well as to use the convenient and modern full-text search engine.
After hours of googling, having tested dozens of different methods from StackOverflow and other similar sites, I came to the conclusion that there was no obvious and simple solution to the problem, so I decided to make my own, and this will be discussed later.
Implementation
The main requirement for solving the problem is the ease of integration into any new (existing) project. In Code First, it is customary to configure everything with attributes, so it would be nice 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 redefine DatabaseInitializer and do other nontrivial actions.
In my work I use Visual Studio 2013 Ultimate. Create a new project of the Class Library type, immediately add 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 enumeration 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 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 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 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 are intentionally removed summary and some comments, the full version on GitHub'e . Briefly explained, EF creates the model during the initial call to DbContext, respectively, we cannot build indexes on the constructor; the simplest option is 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 traditions of EF, we will create an __IndexBuildingHistory utility table in the database, the presence of which will indicate the presence of indexes. The rest is obvious.
In general, if you create a model now, mark it with attributes and start the project, then the indexes will be created successfully, however, we still need a convenient use of the full-text index, for this we will create an extension class:
public static class IQueryableExtension
{
public static IQueryable FullTextSearch(this DbSet queryable,
Expression> 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, today there was no simple solution. This implementation more than covers my indexing requirements , if something is missing for you (error handling, settings, for example, a list of stop words, etc.), you can pick up the project from GitHub and modify it, or write me . The article would be completely boring if we had not tried how it all works, so we are moving on to using it.
Using
1. Create a Console application project
2. Add Entity Framework 6 beta via NuGet
3. Add a link to the library (if you have not read about the implementation, you can download the finished library, links at the end of the article)
4. Create a simple entity, without nesting 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 essence of the animal, with the name (Name), by which we will build a regular index, description (Description) - we will build 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 encoding you have chosen.
5. Create the database context:
public class DataContext : DbContextIndexed
{
public DbSet Animals { get; set; }
}
the only difference here is inheritance, usually you inherit from DbContext, and now from our DbContextIndexed
6. In Programm.cs add a call to the context to trigger the creation of the database:
static void Main(string[] args)
{
using (var context = new DataContext())
{
var temp = context.Animals.ToList();
}
}
7. In the config file of the project, write the connection string to the database with the name DataContext:
8. Press F5 to create the database when the program finishes, using Managment Studio you can verify that everything works as we planned:
9. Now, let's try adding data to try out 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();
}
run so that the data is written to the database, now 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 version of MSSQL 2008R2, so the result is good, but not perfect. As far as I know in the 2013th version, we would still get the panther value, because "Cat" would also study.
I believe that in a fairly simple, and most importantly, “standard” way, 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 will nevertheless implement this functionality in the box.
Sources
Download the finished library:
in zip
format in dll format
The project is posted on the GitHub
Entity Framework 6 beta on the Nuget website
