Entityframework.docs: SQLite: Is there support for Full Text Search?

[lnaie]

Is it possible to do FTS with this provider? Via virtual tables and query operators: https://sqlite.org/fts5.html

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: dbc4d900-28a8-9c04-2443-7407f15a684f
• Version Independent ID: 545bb0b5-f15b-584e-cef7-7283a0e0729f
• Content: SQLite Database Provider - Limitations - EF Core
• Content Source: entity-framework/core/providers/sqlite/limitations.md
• Product: entity-framework
• GitHub Login: @rowanmiller
• Microsoft Alias: divega

[tgckpg]

Yes. It's essentially the same as creating a general table but you have to customize the migration queries.

Here are some example codes from one of my project:

• DbContext
• Migration
• Search

[lnaie]

Ok, I've looked into it and Microsoft.EntityFrameworkCore.Sqlite is built on top of SQLitePCLRaw.bundle_green (>= 1.1.12) which does not seem to have FTS5 feature implemented.
How does that work then?

[tgckpg]

I am not sure about the internal workings but I think the FTS5 extension is builtin to the SQLite library long ago. So as long as raw SQL is allowed then whatever the library provides is supported through the library.

Section 2.1

As of version 3.9.0 (2015-10-14), FTS5 is included as part of the SQLite amalgamation.

The basic idea is to create a VIRTUAL TABLE through raw SQL and querying it like a normal table ( with a twist ).

Since the search query for FTS is a bit different than that EFCore generates. You just need to also override it with IQueryable.FromSql. That's why you see FTSData.cs has a Search method.

The reason I notice this is I played with the SQLite CLI ( using it to read the db file that EFCore has generated ) and comparing the EFCore's raw SQL and don't really see the difference ( I can run PRAMA whatever from it which is neat! ).

[bricelam]

See https://github.com/aspnet/EntityFrameworkCore/issues/4823#issuecomment-381275855 for how to query in EF Core.

SQLitePCLRaw.bundle_green (>= 1.1.12) which does not seem to have FTS5

bundle_green uses the system version of SQLite on macOS and iOS. To get a version that has FTS5 on those platforms, use bundle_e_sqlite3 instead: (in your csproj)

<ItemGroup>
  <!--<PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="2.2.2"/>-->
  <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite.Core" Version="2.2.2"/>
  <PackageReference Include="SQLitePCLRaw.bundle_e_sqlite3" Version="1.1.12"/>
</ItemGroup>

[lnaie]

thanks everyone, very helpful.