EF Core Full-Text Search — Complete Guide

In this tutorial, you'll learn about EF Core Full. We cover key concepts, practical examples, and best practices.

Your users search for products with multiple words, but LIKE '%term%' matches any substring, ignores word relevance, and cannot rank results. Full-text search provides linguistic-aware matching with ranking.

The Problem

var results = await db.Products
    .Where(p => p.Name.Contains("wireless mouse") || 
                p.Description.Contains("wireless mouse"))
    .ToListAsync();

Output: LIKE '%wireless mouse%' matches only the exact phrase "wireless mouse". Does not match "wireless Bluetooth mouse" or "mouse wireless". No ranking — results in arbitrary order.

Right (SQL Server)

First, create the full-text index (SQL script or migration):

migrationBuilder.Sql(@"
CREATE FULLTEXT CATALOG ProductCatalog AS DEFAULT;
CREATE FULLTEXT INDEX ON Products(Name, Description)
    KEY INDEX PK_Products
    ON ProductCatalog
    WITH CHANGE_TRACKING AUTO;");

Then query with EF Core:

var searchTerm = "wireless mouse";
var results = await db.Products
    .Where(p => EF.Functions.Contains(p.Name, searchTerm) ||
                EF.Functions.Contains(p.Description, searchTerm))
    .OrderByDescending(p => EF.Functions.FreeText(p.Description, searchTerm))
    .ToListAsync();

For ranking:

var ranked = await db.Products
    .Select(p => new
    {
        Product = p,
        Rank = EF.Functions.FreeText(p.Name, searchTerm) +
               EF.Functions.FreeText(p.Description, searchTerm) * 0.5
    })
    .Where(x => x.Rank > 0)
    .OrderByDescending(x => x.Rank)
    .ToListAsync();

Prevention

• Use EF.Functions.Contains for exact word/phrase matching with full-text indexes.
• Use EF.Functions.FreeText for fuzzy matching (inflectional forms, thesaurus).
• Use EF.Functions.FreeText ranking via OrderByDescending.
• Create full-text indexes on the columns you search.
• Configure thesaurus and stoplist for domain-specific text.
• Use Contains with boolean operators: "wireless AND (mouse OR keyboard)".
• Fall back to LIKE for simple prefix patterns where full-text is overkill.

Common Mistakes with core full text search

1. Using foldl instead of foldl' causing stack overflow on large lists
2. Forgetting deriving (Show, Eq) on custom data types needed for debugging
3. Placing the wildcard pattern first in case expressions, making all subsequent patterns unreachable

These mistakes appear frequently in real-world EF code. DodaTech's contributors have identified these patterns through analysis of open-source projects and production systems.

Practice Exercise

Write a pure function that safely divides two integers using Maybe, then test it with edge cases like division by zero and negative numbers.

This exercise reinforces the concepts covered in this guide. Try implementing it before checking online solutions.

FAQ

Full-text search powers DodaTech's product search and documentation search features. For more EF Core, visit DodaTech.