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. SQLite full-text search is supported in Entity Framework v4 - v6. It is not supported in Entity Framework Core.

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.Rowidentity) attribute. Note that the "rowid" column must be lowercase.

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

Dim firstQuery = ctx.Books.Where( _
  Function(b) SQLiteTextFunctions.Match(b.Fts, "search expression")). _
    [Select](Function(b) New From { _
      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'

See Also

Full-Text Search Tutorial