You've built a search feature. Users type "running shoes lightweight" into a text box, and somewhere in your repository layer there's a .Where(p => p.Name.Contains(searchTerm)) that generates a LIKE '%running shoes lightweight%' query. It works — until someone searches for "run shoe light" and gets zero results because LIKE matches character sequences, not meaning. You know SQL Server has full-text search. You've probably even written the raw SQL before: CREATE FULLTEXT CATALOG, CREATE FULLTEXT INDEX, then FREETEXT() or CONTAINS() in your queries. But wiring that into EF Core has always felt like stepping outside the ORM. You'd manually add SQL to migrations, use FromSqlRaw for the table-valued functions, and lose the composability that makes LINQ worthwhile.

EF Core 11 changes this. Full-text catalogs and indexes can now be declared in your model and managed through migrations. The predicate functions EF.Functions.FreeText() and EF.Functions.Contains() have been available since EF Core 2.1, but EF Core 11 adds the piece that's been missing for six years: LINQ translation of FREETEXTTABLE() and CONTAINSTABLE() — the table-valued functions that return ranked results. No more FromSqlRaw. No more manual joins on key columns stitched together with string interpolation. Just LINQ.

Setting up full-text search through migrations

Before EF Core 11, configuring full-text search meant adding raw SQL to your migration files. You'd create an empty migration, paste in the CREATE FULLTEXT CATALOG and CREATE FULLTEXT INDEX statements, and hope nobody forgot the suppressTransaction: true call (full-text DDL statements can't run inside a transaction on SQL Server). If the index needed to change — say you added a column — you'd write more raw SQL.

EF Core 11 lets you declare everything in OnModelCreating:

Data/AppDbContext.cs 
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.HasFullTextCatalog("ftCatalog");

    modelBuilder.Entity<Article>()
        .HasFullTextIndex(a => a.Contents)
        .HasKeyIndex("PK_Articles")
        .OnCatalog("ftCatalog");
}

When you scaffold a migration, EF generates the appropriate DDL:

query.sql 
CREATE FULLTEXT CATALOG [ftCatalog];
CREATE FULLTEXT INDEX ON [Articles]([Contents]) KEY INDEX [PK_Articles] ON [ftCatalog];

The HasKeyIndex() call specifies which unique, non-nullable, single-column index to use as the full-text key. This is almost always your primary key index. OnCatalog() assigns the index to a specific catalog — think of catalogs as logical containers for grouping your full-text indexes.

Multi-column indexes and language configuration

Real-world search rarely targets a single column. If you're building a knowledge base or product catalogue, you'll want to search across titles, descriptions, and perhaps tags simultaneously. EF Core 11 supports multi-column full-text indexes with per-column language settings:

Data/AppDbContext.cs 
modelBuilder.Entity<Article>()
    .HasFullTextIndex(a => new { a.Title, a.Contents })
    .HasKeyIndex("PK_Articles")
    .OnCatalog("ftCatalog")
    .WithChangeTracking(FullTextChangeTracking.Manual)
    .HasLanguage("Title", "English")
    .HasLanguage("Contents", "English");

The language setting controls how SQL Server tokenises and stems words in each column. Setting it to "English" means a search for "running" will also match "run," "runs," and "ran" — the inflectional forms that LIKE will never find.

You can also configure the catalog itself as the default and control accent sensitivity:

Example.cs 
modelBuilder.HasFullTextCatalog("ftCatalog")
    .IsDefault()
    .IsAccentSensitive(false);

// TIP

Set IsAccentSensitive(false) if your data contains mixed accented and unaccented text. A search for "cafe" will then match "cafe" as well.

Change tracking modes

The WithChangeTracking call controls how SQL Server keeps the full-text index in sync with the underlying table:

For most OLTP workloads, leave it on automatic. Switch to manual if you're doing bulk data loads and want to populate the index once afterwards.

Filtering with full-text predicates

The predicate functions have been in EF Core since version 2.1, so if you're already on an older version you can use these today — you just need the full-text catalog and index set up on your database.

FreeText: linguistic matching

EF.Functions.FreeText() performs a loose, meaning-based search. It breaks the search string into individual terms, applies stemming, and finds rows where any of the terms (or their inflectional forms) appear:

Features/Search/ArticleSearchService.cs 
public async Task<List<Article>> SearchArticles(string query)
{
    return await context.Articles
        .Where(a => EF.Functions.FreeText(a.Contents, query))
        .ToListAsync();
}

This generates:

query.sql 
SELECT [a].[Id], [a].[Title], [a].[Contents]
FROM [Articles] AS [a]
WHERE FREETEXT([a].[Contents], @query)

A search for "running shoes" matches articles containing "run," "shoe," "runs," "shoes," and other inflectional variants. This is the right choice when your users type natural-language queries and you want broad recall.

Contains: precise matching

EF.Functions.Contains() gives you finer control. It supports exact phrases, prefix matching, proximity searches, and Boolean operators:

Example.cs 
// Prefix search — matches "optimise," "optimisation," "optimiser"
var results = await context.Articles
    .Where(a => EF.Functions.Contains(a.Contents, "\"optim*\""))
    .ToListAsync();

// Proximity search — "cache" near "invalidation" within 5 terms
var results = await context.Articles
    .Where(a => EF.Functions.Contains(a.Contents, "NEAR((cache, invalidation), 5)"))
    .ToListAsync();

// Boolean — must contain "middleware" but not "legacy"
var results = await context.Articles
    .Where(a => EF.Functions.Contains(a.Contents, "middleware AND NOT legacy"))
    .ToListAsync();

The search expressions follow SQL Server's CONTAINS query syntax, which is significantly more powerful than most developers realise. Prefix searches with the * wildcard are particularly useful for autocomplete scenarios.

// WARNING

The search expression is passed directly to SQL Server's CONTAINS function. If you're accepting user input, validate or sanitise it — malformed expressions will throw a SqlException at runtime, not a compile-time error.

Ranked results with table-valued functions

Predicates tell you whether a row matches. Table-valued functions tell you how well it matches. If you're building a search page where results need to be ordered by relevance, this is what you've been waiting for.

FreeTextTable

FreeTextTable() is the ranked equivalent of FreeText(). It returns a FullTextSearchResult<TEntity> that includes both the entity and a ranking score:

Features/Search/ArticleSearchService.cs 
public async Task<List<SearchResult>> SearchWithRanking(string query)
{
    return await context.Articles
        .FreeTextTable(a => a.Contents, query, topN: 50)
        .Select(r => new SearchResult
        {
            Article = r.Value,
            Rank = r.Rank
        })
        .OrderByDescending(r => r.Rank)
        .ToListAsync();
}

The generated SQL uses FREETEXTTABLE, which returns a KEY column (the primary key of the matching row) and a RANK column (an integer indicating relevance). EF Core handles the join between the table-valued function and your entity table automatically.

The topN parameter limits how many matches SQL Server's full-text engine evaluates. This isn't just a convenience — it's a performance lever. Full-text ranking is expensive, and asking the engine to rank a million matches when you only need the top 50 wastes resources.

You can also search across multiple columns:

Example.cs 
var results = await context.Articles
    .FreeTextTable(a => new { a.Title, a.Contents }, query)
    .Select(r => new { Article = r.Value, r.Rank })
    .OrderByDescending(r => r.Rank)
    .ToListAsync();

Or search all indexed columns by omitting the column selector and using the explicit generic overload:

Example.cs 
var results = await context.Articles
    .Join(
        context.Articles.FreeTextTable<Article, int>(query, topN: 20),
        a => a.Id,
        ftt => ftt.Key,
        (a, ftt) => new { Article = a, ftt.Rank })
    .OrderByDescending(r => r.Rank)
    .ToListAsync();

ContainsTable

ContainsTable() pairs the precise query syntax of CONTAINS with ranking scores. This is where full-text search becomes genuinely powerful — you can write weighted search expressions and get results ordered by how strongly they match:

Features/Search/ArticleSearchService.cs 
public async Task<List<SearchResult>> PrecisionSearch(string query)
{
    return await context.Articles
        .Join(
            context.Articles.ContainsTable<Article, int>(query),
            a => a.Id,
            ftt => ftt.Key,
            (a, ftt) => new SearchResult
            {
                Article = a,
                Rank = ftt.Rank
            })
        .OrderByDescending(r => r.Rank)
        .ToListAsync();
}

The CONTAINSTABLE query syntax supports everything CONTAINS does — prefix matching, proximity, Boolean operators — plus ISABOUT, which lets you assign weights to individual terms:

Example.cs 
// Weighted search — "performance" matters more than "tips"
var results = await context.Articles
    .Join(
        context.Articles.ContainsTable<Article, int>(
            "ISABOUT(performance WEIGHT(0.9), tips WEIGHT(0.1))"),
        a => a.Id,
        ftt => ftt.Key,
        (a, ftt) => new { Article = a, ftt.Rank })
    .OrderByDescending(r => r.Rank)
    .ToListAsync();

This is the kind of query that used to require FromSqlRaw with hand-written joins. Having it composable in LINQ means you can chain .Where(), .Skip(), .Take(), and projection without dropping out of the query pipeline.

Specifying a language

Both FreeTextTable and ContainsTable accept an optional languageTerm parameter that controls linguistic matching:

Example.cs 
var results = await context.Articles
    .FreeTextTable(a => a.Contents, query, languageTerm: "English")
    .Select(r => new { Article = r.Value, r.Rank })
    .OrderByDescending(r => r.Rank)
    .ToListAsync();

This overrides the column-level language you set when creating the index. It's useful when your application supports multiple languages and you want to switch stemming behaviour at query time based on the user's locale.

Predicates vs table-valued functions: when to use which

The choice is straightforward:

Don't mix them unnecessarily. If you're just filtering articles by a search term and then ordering by publish date, EF.Functions.FreeText() in a Where() clause is simpler and performs well. Pull in the table-valued functions when ranking genuinely drives the user experience.

Common pitfalls

Forgetting to create the full-text index before querying. SQL Server will throw a runtime error if you call FREETEXT() or FREETEXTTABLE() on a table without a full-text index. Before EF Core 11, this was easy to miss because the index creation lived in raw SQL that someone might not have run. With migration-managed indexes, this becomes less likely — but make sure you've actually applied the migration.

Using LIKE and full-text search interchangeably. LIKE '%term%' forces a full table scan on every query and can't do stemming, proximity, or ranking. Full-text search uses an inverted index that's built and maintained separately. They solve different problems. If you need linguistic matching, use full-text search. If you need exact substring matching (e.g. searching for a UUID fragment), LIKE is the right tool.

Passing unsanitised user input to Contains. The CONTAINS predicate uses its own query syntax — characters like ", *, (, ), and keywords like AND, OR, NEAR have special meaning. Raw user input containing these characters will produce a SqlException. Either validate the input, escape special characters, or use FreeText instead (which treats the input as plain words).

Ignoring the topN parameter. Without topN, FREETEXTTABLE and CONTAINSTABLE rank every matching row in the table. On a table with millions of rows and a broad search term, this is expensive. Always set topN to a reasonable upper bound — your page size multiplied by a small factor is usually sufficient.

Assuming automatic change tracking is instantaneous. When you insert a row, the full-text index is updated asynchronously. There's a small delay — usually milliseconds, but under heavy write load it can stretch to seconds. If your integration tests insert a row and immediately search for it, they may intermittently fail. Either switch to manual population in test environments or add a brief delay.

Not specifying the key index correctly. The HasKeyIndex() call requires the name of an existing unique, non-nullable, single-column index. If your primary key is a composite key or you've renamed the default index, you'll need to specify the correct name. The migration will fail with a clear error if the index doesn't meet the requirements.

Summary