How to load Trigram tokenizer for sqlite using EF core?

Question

I am using Microsoft.EntityFrameworkCore 3.1.8 with my UWP app.

I need to run this sqlite query using Entity framework core and Code first approach:

CREATE VIRTUAL TABLE TrialFTS USING fts5(search, tokenize="trigram");

This gets executed in sqlite database directly without any errors and works fine.

But when I use it inside the migration:

public partial class FTSFirstMigration : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.Sql("CREATE VIRTUAL TABLE ProductFTS USING fts5(search, tokenize=\"trigram\");");          
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropTable(
                name: "ProductFTS");
        }
    }

It fails and throws the error: SQLite Error 1: 'no such tokenizer: trigram'

How to solve this? How to load this tokenizer module explicitly to the sqlite database?

score 0 · Answer 1 · answered Mar 24 '23 at 12:42

0

As reported here the tokenizer was added in Sqlite 3.34.0 on 2020-12-01.

You can check which version you are currently using with

select sqlite_version();

answered Mar 24 '23 at 12:42

Andrea B.

659
4
9

it shows 3.35.5 – Sayansen Mar 27 '23 at 10:15
@Sayansen A sqlite db doesn't have an inherent version. The library you are using specifies the version. You say that the CREATE command "gets executed in sqlite database directly without any errors and works fine". What tool are you using to execute it? You get the error "when I use it inside the migration". You should try the sqlite_version() inside the same migration library to check what version of the sqlite library it's using. – Andrea B. Mar 27 '23 at 11:12

How to load Trigram tokenizer for sqlite using EF core?

1 Answers1