Using SQLite Full-Text Search in Entity Framework

Introduction

SQLite database supports an advanced functionality of full-text search (FTS) and full-text indexing described comprehensively in the SQLite documentation: SQLite FTS3 and FTS4 Extensions.
We decided to meet the needs of our users willing to take advantage of the full-text search in Entity Framework and implemented the full-text search functionality in our Devart dotConnect for SQLite ADO.NET Entity Framework provider.
This article deals with the following:

Full-Text Search in LINQ to Entities

SQLite Full-text search implementation has its own peculiarities, so entities that you want to use full-text search on must meet the following requirements:

  • As SQLite performs full-text search not on the table columns, but rather on a virtual column that has the same name as the table, a full text search enabled entity must have a string property, mapped to the column with the same name as the table. You should not assign anything to this property or query this property itself. It is used only as an argument of the full-text search functions.
  • SQLite full text search enabled tables have only string columns, so entities, mapped to them, must have only string properties. Any modifiers, such as MaxLength attribute will be ignored when creating a table using Code-First migrations. The only exception is the rowid integer column, generated by SQLite automatically. So, if you need an int database-generated entity key, you may create a property, mapped to the rowid column with the DatabaseGenerated(DatabaseGeneratedOption.Identity) attribute. Note that the “rowid” column must be lowercase.

Full-Text Search in LINQ to Entities

The SQLiteTextFunctions class located in the Devart.Data.SQLite.Entity.dll assembly is used to form full-text search specific queries in LINQ to Entities. It has the following methods for using SQLite full-text search functionality:

  • Match – performs the actual full-text search on the data
  • MatchInfo – returns a statistic information about matches of the full-text search operation, performed in this query
  • Offsets – returns the locations of the searched phrase tokens
  • Snippet – returns the snippets of the text around the found matches.

For example, the following query:

Will be translated to the following SQL:

Full-Text Search Tables in Code-First Migrations

To customize Code-First Migrations for the purpose of creating full-text search tables, the SQLiteCreateTableConfiguration class is used, which is located in the Devart.Data.SQLite.Entity.Migrations.dll assembly.
SQLiteCreateTableConfiguration is designed to customize the CreateTable operation. This class allows specifying the SQLite full-text search parameters for the table being created (the TableType and FtsTokenizer properties). In order to make a table full-text search enabled, you need to specify TableType as SQLiteTableType.Fts3 or SQLiteTableType.Fts4.

Examples of customizations are given in the tutorial below.

Tutorial Overview

In this tutorial you will learn how to create an application which will use the following technologies:

Follow the link at the end of this article to download the complete application sample.

Prerequisites

1. Create a console C# project in Visual Studio 2010 SP1 and call it SQLiteFullTextSearch.
2. In the project, add references to the following assemblies:

  • System.Data.Entity;
  • Devart.Data;
  • Devart.Data.SQLite;
  • Devart.Data.SQLite.Entity;
  • Devart.Data.SQLite.Entity.Migrations.

3. Add the latest public version of EF Code-First to the project: run Package Manager Console via the Visual Studio Tools menu -> Library Package Manager -> Package Manager Console, and in the displayed window run the following command:

As a result, the EntityFramework.dll assembly is added to the project, App.config is supplemented with records and the packages.config file is created.
4. Add a new MyContext.cs file to the project, and place an empty model in it:

5. Run the following command in Package Manager Console:

As a result, the Configuration class is added to the project.
6. Complement the existing Configuration class constructor

by specifying the SQL generator:

7. Setting the connection string for our class of the MyContext model will be performed in the config file of the application. Add the connection string to the App.config file:

Using Full-Text Search

1. Add the Book.cs file to the project:

Here we use the rowid column, which is auto-generated by SQLite for all the tables, to map the entity key.

The Fts property is created for performing full-text search. It is mapped to the virtual column, having the same name as the table.
2. Add the following line to the MyContext class:

3. To generate migration, run the following command in Package Manager Console:

As a result, a migration class with the following content is added to the project:

4. Complement the migration by specifying the storage engine type for the table, and creating a full-text index on the Content column, without specifying explicitly the name of the generated index:

5. Update the database using the migration, by running the following command in Package Manager Console:

As a result, commands for model objects creation will be executed

Also, commands for creating the system MigrationHistory table and filling it with migration data will be executed.

Full-Text Search Sample Using Natural Language and Phrase Queries

In this tutorial, we have turned on SQLiteMonitor to view executed DDL and DML statements in the Devart dbMonitor application. However, keep in mind that the use of SQLiteMonitor is feasible for the purpose of testing and debugging but should be limited in production environments, since monitoring can decrease the performance of your application.
To use monitoring, add this code to your application

and run dbMonitor.
Let’s write the code filling the table with data and forming full-text search queries.

Conclusion

Thus, we have shown how to use SQLite full-text search functionality in Entity Framework applications, including the development process, based on Code-First Migrations. We are looking forward to receiving feedback from you via comments, the Entity Framework forum and our contact form, if it’s necessary to rework or improve support of full-text indexing and searching in our provider.
You can download the archive with the complete version of the sample from this tutorial here.

Comments are closed.