dotConnect for SQLite Documentation
In This Topic
    Full-Text Search Tutorial
    In This Topic

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

    Note that SQLite full-text search is supported in Entity Framework v4 - v6. It is not supported in Entity Framework Core.

    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 EF Code-First NuGet package to the project (note that Entity Framework Core does not support full-text search, so you need to use Entity Framework v6): 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) {

    }

  }
}

      
Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports System.Data.Entity
Imports Devart.Data.SQLite

Namespace SQLiteFullTextSearch

  Public Class MyContext
    Inherits DbContext

    Public Sub New()
    End Sub

    Public Sub New(connection As SQLiteConnection)
      MyBase.New(connection, False)
    End Sub

    Shared Sub New()
    End Sub

    Protected Overrides Sub OnModelCreating(modelBuilder As DbModelBuilder)

    End Sub

  End Class
End Namespace

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

      
    Public Sub New()
      AutomaticMigrationsEnabled = False
    End Sub

      by specifying the SQL generator:

          public Configuration()
    {
      AutomaticMigrationsEnabled = false;

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

      
    Public Sub New()
      AutomaticMigrationsEnabled = False
    
      SetSqlGenerator( _
        Devart.Data.SQLite.Entity.Migrations.SQLiteConnectionInfo.InvariantName, _
        New Devart.Data.SQLite.Entity.Migrations.SQLiteEntityMigrationSqlGenerator())
    End Sub
    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>

    Creating Classes and Migration

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

  }
}

      
Imports System.Collections.Generic
Imports System.ComponentModel.DataAnnotations
Imports System.Linq
Imports System.Text

Namespace SQLiteFullTextSearch

  Public Class Book

    <Key> _
    <DatabaseGenerated(DatabaseGeneratedOption.Identity)> _
    <Column("rowid")> _
    Public Property Rowid() As Integer
      Get
        Return m_Rowid
      End Get
      Set
        m_Rowid = Value
      End Set
    End Property
    Private m_Rowid As Integer

    Public Property Author() As String
      Get
        Return m_Author
      End Get
      Set
        m_Author = Value
      End Set
    End Property
    Private m_Author As String

    Public Property Name() As String
      Get
        Return m_Name
      End Get
      Set
        m_Name = Value
      End Set
    End Property
    Private m_Name As String

    Public Property Content() As String
      Get
        Return m_Content
      End Get
      Set
        m_Content = Value
      End Set
    End Property
    Private m_Content As String

    <Column("Books")> _
    Public Property Fts() As String
      Get
        Return m_Fts
      End Get
      Set
        m_Fts = Value
      End Set
    End Property
    Private m_Fts As String

  End Class
End Namespace

      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. For more information on these properties refer to Full-Text Search Support in Entity Framework.

    2. Add the following line to the MyContext class:

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

      
Public Property Books() As DbSet(Of Book)
  Get
    Return m_Books
  End Get
  Set
    m_Books = Value
  End Set
End Property
Private m_Books As DbSet(Of Book)

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

      
Public Partial Class AddBook
  Inherits DbMigration
  Public Overrides Sub Up()
    CreateTable("Books", Function(c) New With { 
      .Rowid = c.Int(nullable := False, identity := True), 
      .Author = c.[String](maxLength := 200), 
      .Name = c.[String](maxLength := 300), 
      .Content = c.[String](), 
      .Books = c.[String]() 
    }).PrimaryKey(Function(t) t.Rowid)

  End Sub

  Public Overrides Sub Down()
    DropTable("Books")
  End Sub
End Class

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

      
Imports Devart.Data.SQLite.Entity
Imports Devart.Data.SQLite.Entity.Migrations

Public Partial Class AddBook
  Inherits DbMigration
  Public Overrides Sub Up()
    CreateTable("Books", Function(c) New With { 
      .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() With { _
      .TableType = SQLiteTableType.Fts4, _
      .FtsTokenizer = SQLiteFtsTokenizer.Porter _
    }).PrimaryKey(Function(t) t.Rowid)

  End Sub

  Public Overrides Sub Down()
    DropTable("Books")
  End Sub
End Class

    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

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

    
Dim monitor = New SQLiteMonitor() With { _
  .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.Content, "\"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);
  Console.ReadLine();
}
    
Using ctx = New SQLiteFullTextSearch.MyContext()

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

  ' Write an analogue of the SQL query:
  '  SELECT rowid, name FROM Books
  '  WHERE Books MATCH 'king palace Norfolk';
  Dim firstQuery = From b In ctx.Books _
                   Where SQLiteTextFunctions.Match(b.Fts, "king palace Norfolk") _
                   Select _
                      b.Rowid, _
                      b.Name
  Dim 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("-"C, 50))
  For Each item In firstQueryResults
    Console.WriteLine("{0,-5} | {1} ", item.Rowid, item.Name)
  Next

  ' Write an analogue of the SQL query:
  '   SELECT rowid, name FROM Books
  '   WHERE Books MATCH '"king''s palace"'
  Dim firstQuery = From b In ctx.Books _
                   Where SQLiteTextFunctions.Match(b.Fts, """king's palace""") _
                   Select _
                      b.Rowid, _
                      b.Name
  Dim 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("-"C, 50))
  For Each item In secondQueryResults
    Console.WriteLine("{0,-5} | {1} ", item.Rowid, item.Name)
  Next
  Console.ReadLine()
End Using

    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.

    See Also

    Full-Text Search Support

     

     

    © 2008 - 2024 Devart. All Rights Reserved.

    [email protected]