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 peculiarities in SQLite
• Full-Text Search in LINQ to Entities
• Full-Text Search Tables in Code-First Migrations
• Tutorial Overview
• Prerequisites
• Using Full-Text Search
• Conclusion

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:

  var firstQuery = ctx.Books
                  .Where(b => SQLiteTextFunctions.Match(b.Fts, "search expression"))
                  .Select(b => new { b.Rowid, b.Name });

Will be translated to the following SQL:

SELECT 
Extent1."rowid",
Extent1.Name
FROM Books AS Extent1
WHERE Extent1.Books MATCH 'search expression'

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:

• SQLite Full Text Search;
• Entity Framework;
• Code-based Code-First Migrations;
• Query execution monitoring via Devart dbMonitor.

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:

Install-Package EntityFramework

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:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Entity;
using Devart.Data.SQLite;

namespace SQLiteFullTextSearch {

  public class MyContext: DbContext {

    public MyContext() {
    }

    public MyContext(SQLiteConnection connection)
      : base(connection, false) {
    }

    static MyContext() {
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder) {

    }

  }
}

5. Run the following command in Package Manager Console:

Enable-Migrations

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

    public Configuration()
    {
      AutomaticMigrationsEnabled = false;
    }

by specifying the SQL generator:

    public Configuration()
    {
      AutomaticMigrationsEnabled = false;

      SetSqlGenerator(Devart.Data.SQLite.Entity.Migrations.SQLiteConnectionInfo.InvariantName,
              new Devart.Data.SQLite.Entity.Migrations.SQLiteEntityMigrationSqlGenerator());
    }

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:

<connectionStrings>
  <add name="MyContext" connectionString="Data Source=d:Test.db;FailIfMissing=false" 
      providerName="Devart.Data.SQLite" />
</connectionStrings>

Using Full-Text Search

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

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Text;

namespace SQLiteFullTextSearch {

  public class Book {

    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    [Column("rowid")]
    public int Rowid { get; set; }

    public string Author { get; set; }

    public string Name { get; set; }

    public string Content { get; set; }

    [Column("Books")]
    public string Fts { get; set; }

  }
}

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:

public DbSet<Book> Books { get; set; }

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

Add-Migration AddBook

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

    public partial class AddBook : DbMigration
    {
        public override void Up()
        {
            CreateTable(
                "Books",
                c => new
                    {
                        Rowid = c.Int(nullable: false, identity: true),
                        Author = c.String(maxLength: 200),
                        Name = c.String(maxLength: 300),
                        Content = c.String(),
                        Books = c.String(),
                    })
                .PrimaryKey(t => t.Rowid);

        }

        public override void Down()
        {
            DropTable("Books");
        }
    }

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:

    using Devart.Data.SQLite.Entity;
    using Devart.Data.SQLite.Entity.Migrations;

    public partial class AddBook : DbMigration
    {
        public override void Up()
        {
            CreateTable(
                "Books",
                c => new
                    {
                        Rowid = c.Int(nullable: false, identity: true),
                        Author = c.String(maxLength: 200),
                        Name = c.String(maxLength: 300),
                        Content = c.String(),
                        Books = c.String(),
                    },
                    anonymousArguments: new SQLiteCreateTableConfiguration()
                    {
                        TableType = SQLiteTableType.Fts4,
                        FtsTokenizer = SQLiteFtsTokenizer.Porter
                    })
                .PrimaryKey(t => t.Rowid);

        }

        public override void Down()
        {
            DropTable("Books");
        }
    }

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

Update-Database -Verbose

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

CREATE VIRTUAL TABLE Books USING fts4(Author, Name, Content, tokenize=porter)

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

var monitor = new SQLiteMonitor() { IsActive = true };

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

using (var ctx = new SQLiteFullTextSearch.MyContext()) {

  // Load information into the Books table
  ctx.Books.Add(new SQLiteFullTextSearch.Book() {
    Author = "William Shakespeare",
    Name = "The Tragedy of King Richard the Second",
    Content = @"ACT 1
SCENE I. London. A Room in the palace.
[Enter KING RICHARD, attended; JOHN OF GAUNT, with other NOBLES.]
KING RICHARD.
  Old John of Gaunt, time-honoured Lancaster,
  Hast thou, according to thy oath and band,
  Brought hither Henry Hereford thy bold son,
  Here to make good the boisterous late appeal,
  Which then our leisure would not let us hear,
  Against the Duke of Norfolk, Thomas Mowbray?
..."
  });
  ctx.Books.Add(new SQLiteFullTextSearch.Book() {
    Author = "William Shakespeare",
    Name = "The Life and Death of King John",
    Content = @"ACT 1.
SCENE I. Northampton. A Room of State in the Palace.
[Enter KING JOHN, QUEEN ELINOR, PEMBROKE, ESSEX, SALISBURY, and others, with CHATILLON.]
KING JOHN.
  Now, say, Chatillon, what would France with us?
CHATILLON.
  Thus, after greeting, speaks the King of France,
  In my behaviour, to the majesty,
  The borrow'd majesty of England here.
..."
  });
  ctx.Books.Add(new SQLiteFullTextSearch.Book() {
    Author = "William Shakespeare",
    Name = "The Life of Henry the Fifth",
    Content = @"ACT 1.
SCENE I. London. An ante-chamber in the King's palace.
[Enter the Archbishop of Canterbury and the Bishop of Ely.]
CANTERBURY.
  My lord, I'll tell you: that self bill is urg'd,
  Which in the eleventh year of the last king's reign
  Was like, and had indeed against us pass'd,
  But that the scambling and unquiet time
  Did push it out of farther question.
..."
  });
  ctx.SaveChanges();

  // Write an analogue of the SQL query:
  //  SELECT rowid, name FROM Books
  //  WHERE Books MATCH 'king palace Norfolk';
  var firstQuery = ctx.Books
    .Where(b => SQLiteTextFunctions.Match(b.Fts, "king palace Norfolk"))
    .Select(b => new { b.Rowid, b.Name } );
  var firstQueryResults = firstQuery.ToList();

  // Output:
  Console.WriteLine();
  Console.WriteLine("== Querying the table ==");
  Console.WriteLine();
  Console.WriteLine("1) Natural language");
  Console.WriteLine();
  Console.WriteLine("SQL:");
  Console.WriteLine(firstQuery.ToString());
  Console.WriteLine();
  Console.WriteLine("Data:");
  Console.WriteLine("{0,-5} | {1}", "Rowid", "Name");
  Console.WriteLine(new string('-', 50));
  foreach (var item in firstQueryResults)
    Console.WriteLine("{0,-5} | {1} ", item.Rowid, item.Name);        

  // Write an analogue of the SQL query:
  //   SELECT rowid, name FROM books
  //   WHERE Books MATCH '"king''s palace"'
  var secondQuery = ctx.Books
      .Where(b => SQLiteTextFunctions.Match(b.Fts, ""king's palace""))
      .Select(b => new { b.Rowid, b.Name });
  var secondQueryResults = secondQuery.ToList();

  // Output:
  Console.WriteLine();
  Console.WriteLine("2) Phrase query");

  Console.WriteLine();
  Console.WriteLine("SQL:");
  Console.WriteLine(secondQuery.ToString());
  Console.WriteLine();
  Console.WriteLine("Data:");
  Console.WriteLine("{0,-5} | {1}", "Rowid", "Name");
  Console.WriteLine(new string('-', 50));
  foreach (var item in secondQueryResults)
    Console.WriteLine("{0,-5} | {1} ", item.Rowid, item.Name);
}

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.