Full-Text Search with EF Core: SQL Server, PostgreSQL and SQLite
Objective
Where(a => a.Body.Contains("framework")) becomes LIKE '%framework%'. That
scans the table, ignores word forms and gives you no order of relevance. Full-text
search is the database feature built for that job, and EF Core 11 makes the SQL
Server side of it much more usable: the catalog and the index can live in the
model and be created by migrations, and the table-valued functions FREETEXTTABLE
and CONTAINSTABLE return a rank you can sort by.
This concept shows the same feature on three engines, because the code is not
portable between them: SQL Server (EF Core 11), PostgreSQL (the Npgsql provider)
and SQLite (FTS5 through raw SQL). Which parts were run matters, so here it is.
The SQLite and PostgreSQL sections were executed with real rows: SQLite on EF
Core 11.0.0, and PostgreSQL 18.7 (a throwaway container) with Npgsql 11.0.0 (the
stable build NuGet published on November 20, 2026) and EF Core 11.0.0. The SQL Server
SQL was generated offline (ToQueryString() and GenerateCreateScript(), no
server): it proves what EF generates, not what a server returns, and nothing here
ran against SQL Server 2025. The general EF.Functions rules are in
ef-core-functions-and-database-specific-translation.
Use Cases
- A search box over articles, tickets, products or documents where "framework" should also find "Frameworks".
- Best matches first, with a score to sort by; prefix search, exact phrases and "this word but not that one".
- Combining the exact words (full-text) with the meaning (vectors) in one query,
see
ef-core-vector-search-with-sql-server-2025.
Deep Dive
SQL Server: the catalog and the index belong in the model
Until EF Core 10 the full-text catalog and index were hand-written SQL in a migration. EF Core 11 models them:
csharpmodelBuilder.HasFullTextCatalog("ftCatalog");
modelBuilder.Entity<Article>()
.HasFullTextIndex(a => new { a.Title, a.Body })
.UseKeyIndex("PK_Articles")
.UseCatalog("ftCatalog");sqlCREATE FULLTEXT CATALOG [ftCatalog];
CREATE FULLTEXT INDEX ON [Articles]([Title], [Body])
KEY INDEX [PK_Articles] ON [ftCatalog];Two things came out of running it on 11.0.0:
UseKeyIndexis required. Without it model building fails withFull-text index '{'Title', 'Body'}' on entity type 'Article' does not have a KEY INDEX configured.plus a hint to callUseKeyIndex. Per the SQL Server documentation the key index must be a unique, single-column, non-nullable index, and the primary key is the usual choice.- The columns turn into
nvarchar(450).TitleandBodyarenvarchar(max)by default, and as soon as they are in the full-text index the generated DDL saysnvarchar(450). That is the SQL Server convention for any indexed string property (a plainHasIndex(x => x.Title)does the same). For a title it is harmless. For an article body it is a silent 450-character limit. Say what you mean:
csharpe.Property(a => a.Body).HasColumnType("nvarchar(max)");With that line the DDL keeps nvarchar(max) and the same CREATE FULLTEXT INDEX.
The SQL Server Full-Text Search component also has to be installed on the server,
and its availability varies by edition and hosting, so check yours.
SQL Server: predicates that filter
EF.Functions.FreeText and EF.Functions.Contains have existed for a long time.
They return a Boolean for Where:
csharpdb.Articles.Where(a => EF.Functions.FreeText(a.Title, "entity framework"));
db.Articles.Where(a => EF.Functions.Contains(a.Title, "\"entity*\" AND \"core\""));sqlWHERE FREETEXT([a].[Title], N'entity framework')
WHERE CONTAINS([a].[Title], N'"entity*" AND "core"')FreeText takes plain words and lets the engine expand them into word forms.
Contains takes a search condition with its own syntax: quoted phrases,
AND/OR/NOT, and * for a prefix. Neither gives you a score, so you cannot
sort by relevance with them.
SQL Server: ranking with FreeTextTable and ContainsTable
The new functions are table-valued: they return rows of key and rank, and you join them back to your table.
csharpvar results = await db.Articles
.FreeTextTable<Article, int>("entity framework", a => a.Title)
.Join(db.Articles, f => f.Key, a => a.Id, (f, a) => new { a.Title, f.Rank })
.OrderByDescending(x => x.Rank)
.ToListAsync();sqlSELECT [a0].[Title], [f].[RANK] AS [Rank]
FROM FREETEXTTABLE([Articles], [Title], @p) AS [f]
INNER JOIN [Articles] AS [a0] ON [f].[KEY] = [a0].[Id]
ORDER BY [f].[RANK] DESCContainsTable<Article, int>(searchCondition, column, language, topN) has the
same shape, with the "entity*" AND "core" syntax. The optional arguments are
the column (null searches all indexed columns and emits *), a language term
and a topN that SQL Server applies inside the function:
csharpdb.Articles.FreeTextTable<Article, int>("entity framework", null, "English", 5)sqlFROM FREETEXTTABLE([Articles], *, @p, LANGUAGE N'English', @p1) AS [f]The result type is FullTextSearchResult<TKey>, a struct with exactly Key and
Rank (an int). It does not carry the entity. The EF Core 11 documentation
sample reads r.Value from it, and that does not compile on 11.0.0, so the join
back by key shown above is the working form. The key type has to be written out
(<Article, int>), because the compiler cannot infer it.
PostgreSQL: a tsvector column and a GIN index
Npgsql models full-text search as a generated tsvector column, an index on it,
and query operators. The query function goes inside the lambda:
csharpmodelBuilder.Entity<Article>()
.HasGeneratedTsVectorColumn(a => a.SearchVector, "english", a => new { a.Title, a.Body })
.HasIndex(a => a.SearchVector).HasMethod("GIN");
db.Articles
.Where(a => a.SearchVector.Matches(EF.Functions.WebSearchToTsQuery("english", "entity framework -dapper")))
.OrderByDescending(a => a.SearchVector.Rank(EF.Functions.WebSearchToTsQuery("english", "entity framework -dapper")));sql"SearchVector" tsvector GENERATED ALWAYS AS (to_tsvector('english', "Title" || ' ' || "Body")) STORED NOT NULL
CREATE INDEX "IX_Articles_SearchVector" ON "Articles" USING GIN ("SearchVector");
WHERE a."SearchVector" @@ websearch_to_tsquery('english', 'entity framework -dapper')
ORDER BY ts_rank(a."SearchVector", websearch_to_tsquery('english', 'entity framework -dapper')) DESCHoisting the call into a local (var q = EF.Functions.WebSearchToTsQuery(...), then
Matches(q)) does not work: it throws InvalidOperationException: The 'WebSearchToTsQuery' method is not supported because the query has switched to client-evaluation. On 44 rows (four real articles, 40 filler) the run returned
"Entity Framework Core 11" (rank 0.1815) and "Frameworks everywhere" (0.1810) for
entity framework -dapper, and the same three rows for framework and frameworks.
websearch_to_tsquery accepts what people type in a search box (quoted phrases,
-word, or) and did not raise a syntax error on a dangling entity and (two rows
back), so it is the safest entry point for user text. The language ('english')
decides the stemming and the stop words, and it has to match the content. With
SET enable_seqscan = off, EXPLAIN showed a Bitmap Index Scan on the GIN index.
SQLite: FTS5, with a virtual table and raw SQL
SQLite has no EF mapping for full-text search, but the engine has FTS5 built in. EF
Core's part is to run your SQL. The table is an external content FTS5 table
that points at Articles, kept in step by three triggers (the one for updates
deletes the old row from the index and inserts the new one):
sqlCREATE VIRTUAL TABLE ArticlesFts USING fts5(
Title, Body, content='Articles', content_rowid='Id',
tokenize='unicode61 remove_diacritics 2');
CREATE TRIGGER Articles_ai AFTER INSERT ON Articles BEGIN
INSERT INTO ArticlesFts(rowid, Title, Body) VALUES (new.Id, new.Title, new.Body);
END;
CREATE TRIGGER Articles_ad AFTER DELETE ON Articles BEGIN
INSERT INTO ArticlesFts(ArticlesFts, rowid, Title, Body)
VALUES ('delete', old.Id, old.Title, old.Body);
END;
-- Articles_au (AFTER UPDATE): the same 'delete' row, then the new INSERTIn a real project that SQL goes in a migration with migrationBuilder.Sql(...),
because the model never sees ArticlesFts (the run used ExecuteSql). The query
is SqlQuery<T> over a join, ordered by bm25:
csharppublic record Hit(int Id, string Title, double Score);
var hits = db.Database.SqlQuery<Hit>($"""
SELECT a.Id AS Id, a.Title AS Title, -bm25(ArticlesFts, 10.0, 1.0) AS Score
FROM ArticlesFts
JOIN Articles AS a ON a.Id = ArticlesFts.rowid
WHERE ArticlesFts MATCH {match}
ORDER BY bm25(ArticlesFts, 10.0, 1.0)
""").ToList();bm25 returns a negative number where lower is better, so the query sorts
ascending and negates it for display. The two weights make a hit in Title count
ten times a hit in Body. With 44 articles in the table (four real ones and 40
filler rows), the run printed:
MATCH |
Result (score) |
|---|---|
entity framework |
1 "Entity Framework Core 11" (11.058), 3 "Dapper versus EF Core" (5.962) |
entit* (prefix) |
the same two rows, 1 first |
"entity framework" (phrase) |
the same two rows |
core NOT dapper |
only 1 |
title:core (one column) |
3 (5.620), 1 (5.529) |
configuracao |
"Configuração do banco" (6.679), thanks to remove_diacritics |
Four behaviors to know before shipping:
- The triggers are not optional. An external-content table does not follow the
table it points at. Counting hits directly on the virtual table
(
SELECT count(*) FROM ArticlesFts WHERE ArticlesFts MATCH 'pasta'), anExecuteDeleteof the article took the count from 1 to 0 with the delete trigger in place, and from 1 to 1 without it. The stale entry stays in the index, and theJOINback toArticlesis what hides it from the query above. Bulk operations such asExecuteDeleteandExecuteUpdateskip the change tracker, but not database triggers, which is why triggers are the right layer. - Tiny tables score zero. In the first run the table had only four rows, every
term appeared in half of them, and every
bm25score printed0.000. Scores need a corpus. The ordering above appeared only after the filler rows. MATCHhas its own syntax, and bad input throws. Passingentity ANDfrom a search box raisedSqliteException: SQLite Error 1: 'fts5: syntax error near ""'. The fix that was tested is to turn user text into a phrase by wrapping it in double quotes and doubling any inner quote:static string Quote(string s) => "\"" + s.Replace("\"", "\"\"") + "\"";.Quote("entity AND")returned no rows and no exception, and a hostile input (a" OR "b) became the harmless phrase"a"" OR ""b". The price is that users lose operators and prefixes unless you add them yourself.- It is not
LIKE.EF.Functions.Like(a.Body, "%framework%")matched one row by raw substring inBody, with no ranking and no index. FTS5 matched two rows because it searchedTitleandBodyand tokenized the words.
SQL Server has the same kind of trap: CONTAINS fails on a malformed search
condition, while FREETEXT is more forgiving (SQL Server documentation, not run here).
Trade-offs
- Three engines, three APIs. Nothing in this concept is portable. A feature that must run on SQL Server and PostgreSQL needs an interface of your own with two implementations, or a search service outside the database.
- The index is not instant everywhere. SQL Server populates a full-text index with its own process, so a fresh row may not be searchable yet (documented SQL Server behavior, not run here). Triggers and generated columns, as in the SQLite and PostgreSQL examples, keep the index transactional with the write.
- Language is part of the index. Stemming and stop words depend on the language you configure; a Portuguese catalog searched with English settings looks "flaky".
- Scores are not comparable. SQL Server's rank, PostgreSQL's
ts_rankand SQLite'sbm25use different scales: sort by them, do not hard-code a threshold. nvarchar(450)can truncate data. Review the DDL of any entity you index.- When the database is not enough. Typo tolerance, facets and synonyms across
millions of documents are for dedicated engines such as Meilisearch or
Elasticsearch, at the cost of a second copy of the data to keep in sync
(
full-text-and-vector-search-indexesin the system design track compares both).