How I Implemented Full-Text Search on My Website with EF Core

When I added a search box to my website, I thought it would be easy. I just wanted users to type a word and find matching blog posts. My first try used a simple database query that looked for the word inside the text. It worked on my laptop with ten posts. Then I added a few hundred posts, and search became slow. Worse, when someone searched for "running", it missed posts that said "run" or "ran".

So I learned how to do full-text search the proper way. This post is the story of how I did it, written so a beginner can follow along.

A real-life way to picture it

Imagine you have a big cookbook with 500 recipes. A friend asks, "Which recipes use paneer?"

You could open the book and read every single page from start to finish. That works, but it takes a long time. This is like a slow database scan.

Now imagine the cookbook has an index at the back. You flip to the letter P, find "paneer", and it tells you: pages 12, 88, and 240. You jump straight there. That is full-text search. The database builds a smart index of words ahead of time, so when someone searches, it does not read every recipe. It just checks the index.

That one idea is the heart of everything below.

Why simple LIKE search is not enough

Most beginners start with a query like this.

// The slow way. Avoid this for real search.
var results = db.Posts
    .Where(p => p.Body.Contains(searchTerm))
    .ToList();

This turns into a SQL LIKE '%term%' query. It has three big problems.

Full-text search fixes all three. It keeps a word index, it understands word forms, and it can score each result so the best ones show first.

The big picture before we touch code

Before writing code, it helps to see the steps. Adding full-text search is always the same shape, no matter which database you use.

I will show two paths: SQL Server and PostgreSQL. Both are built into the database, so you do not need a separate search engine like Elasticsearch for a small or medium site. Pick the one your project already uses.

Path 1: SQL Server full-text search

SQL Server has had full-text search for many years. To use it, you create two things: a full-text catalog (a container) and a full-text index (the actual word list) on the column you want to search.

One important note for beginners. The LocalDB that comes with Visual Studio does not include the full-text feature. You need a full SQL Server instance, like SQL Server Express or the Developer edition, or a container.

Step 1: Create the catalog and index

You run this SQL once. In older EF Core versions you put it inside a migration as raw SQL. Starting with EF Core 11, you can configure catalogs and indexes directly in your model and EF generates the SQL for you.

-- Create a container for full-text data
CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;
 
-- Build the index on the Body column of Posts
CREATE FULLTEXT INDEX ON dbo.Posts(Body)
    KEY INDEX PK_Posts
    ON ftCatalog;

The KEY INDEX PK_Posts part tells SQL Server which unique key to use. Every full-text index needs a unique single-column key, and your primary key usually works fine.

Step 2: Query with CONTAINS and FREETEXT

EF Core gives you two methods you can use inside a Where clause: EF.Functions.Contains and EF.Functions.FreeText. They map to the SQL CONTAINS() and FREETEXT() predicates.

// FreeText is forgiving. It matches by meaning,
// so "running" also finds "run" and "ran".
var freeResults = await db.Posts
    .Where(p => EF.Functions.FreeText(p.Body, searchTerm))
    .ToListAsync();
 
// Contains is stricter and supports operators
// like AND, OR, and prefix matches with *.
var exactResults = await db.Posts
    .Where(p => EF.Functions.Contains(p.Body, "paneer AND recipe"))
    .ToListAsync();

Here is a simple way to decide which to use.

How a SQL Server query flows

Path 2: PostgreSQL full-text search

PostgreSQL takes a slightly different approach, and once you understand it, it is quite neat. It turns text into a special type called a tsvector. A tsvector is a sorted list of lexemes, which are normalized words. For example, "cats", "cat", and "catting" all reduce to the lexeme "cat". This is why searching for one form finds the others.

A search word is turned into a tsquery. You then ask, "does this tsvector match this tsquery?"

Step 1: Add a generated tsvector column

With the Npgsql provider for EF Core, you can ask the database to keep a tsvector column up to date automatically. This is called a stored generated column. You configure it in OnModelCreating.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Post>()
        .HasGeneratedTsVectorColumn(
            p => p.SearchVector,        // the tsvector property
            "english",                  // text search config
            p => new { p.Title, p.Body }) // columns to include
        .HasIndex(p => p.SearchVector)
        .HasMethod("GIN");              // fast index type
}

The SearchVector property in your Post class is of type NpgsqlTsVector. Because it is generated, you never set it by hand. PostgreSQL fills it in whenever the title or body changes.

Step 2: Add a GIN index

Notice the .HasMethod("GIN") line above. A GIN index is built for tsvector data and makes lookups very fast. Without it, PostgreSQL would still work, but it would scan more than needed. Always add the index for real use.

Step 3: Query with Matches

Now the search itself is short and readable.

// Matches returns posts whose SearchVector
// matches the search words. PostgreSQL handles
// word normalization for you.
var results = await db.Posts
    .Where(p => p.SearchVector.Matches(searchTerm))
    .ToListAsync();

You can also rank results so the strongest matches appear first, using EF.Functions.ToTsQuery and a rank function. Ranking is what makes a search feel "smart" instead of random.

Comparing the two paths

Both options live inside your database, which keeps your setup simple. Here is how I think about choosing.

The honest answer is: use whatever database you already have. Do not switch databases just for search. Both do the job well for most websites. Switching adds risk and work for little gain.

How I keep the index fresh

A common worry for beginners is, "what if I add a new post? Does search know about it?"

Good news: yes, automatically. Both databases update their full-text data in the background as rows change. In PostgreSQL, the generated column recalculates on every insert or update. In SQL Server, the full-text index updates as part of normal operation. You do not write extra code to keep them in sync. You build the index once, and the database maintains it.

Understanding stop words and stemming

Two words come up a lot when people talk about full-text search, and they sound scary but are simple once you see them.

The first is stop words. These are very common words like "the", "is", "and", and "a". They appear in almost every document, so they are useless for searching. If you searched for "the", you would get every single post. To save space and time, full-text search ignores stop words by default. This is why a search for "the recipe" really just searches for "recipe".

The second is stemming, sometimes called normalization. This is the trick where "running", "runs", and "ran" all collapse down to the same root word "run". The database does this when it builds the index and again when it reads your search term. Because both sides are stemmed the same way, they match. This is the single biggest reason full-text search feels smarter than LIKE.

Here is how the two ideas work together when text enters the index.

You do not write any of this yourself. The database does it. But knowing it happens helps you understand why your results look the way they do. If a search misses a common word, stop words are usually the reason.

Measuring if it actually helped

After I switched, I wanted proof it was faster, not just a feeling. The easy check is to time the same search before and after. In SQL Server you can turn on statistics, and in PostgreSQL you can put EXPLAIN ANALYZE in front of your query to see whether it used the index.

Here is a tiny way to time a search from C# itself, which is enough for a quick sanity check.

var watch = System.Diagnostics.Stopwatch.StartNew();
 
var results = await db.Posts
    .Where(p => EF.Functions.FreeText(p.Body, term))
    .ToListAsync();
 
watch.Stop();
Console.WriteLine($"Found {results.Count} posts in {watch.ElapsedMilliseconds} ms");

On my site, the same search dropped from over a second to a handful of milliseconds once the index was in place. Seeing the number drop made the work feel worth it.

A few lessons I learned the hard way

These are small things that cost me time, shared so they do not cost you time too.

• Test on a real database, not LocalDB. I spent an hour confused because full-text simply did not exist on LocalDB.
• Search the right column. I once indexed the wrong field and got no results. Double-check the column name.
• Trim and validate the search term. Empty or very long inputs can cause odd behavior. Guard against them in code.
• Do not over-engineer early. I almost installed a separate search server for a site with 300 posts. The built-in database search was more than enough.

// A tiny guard before searching keeps things safe.
public async Task<List<Post>> Search(string? term)
{
    if (string.IsNullOrWhiteSpace(term))
        return new List<Post>();
 
    term = term.Trim();
    return await db.Posts
        .Where(p => EF.Functions.FreeText(p.Body, term))
        .ToListAsync();
}

When to outgrow the database

The built-in search is great, but it has limits. If your site grows to millions of documents, or you need typo tolerance ("did you mean..."), search across many languages, or very advanced ranking, then a dedicated engine like Elasticsearch or OpenSearch starts to make sense. But that is a later problem. Start simple. Most sites never need to move on, and you can always add it when real numbers tell you to.

Quick recap

• Plain LIKE search is slow and misses word forms. Avoid it for real search.
• Full-text search uses a word index, like the index at the back of a cookbook.
• SQL Server: create a full-text catalog and index, then query with EF.Functions.FreeText or EF.Functions.Contains.
• PostgreSQL: use a generated tsvector column with a GIN index, then query with .Matches().
• Both databases keep the index fresh automatically when data changes.
• Use the database you already have. Do not switch just for search.
• Only move to a dedicated search engine when your site truly outgrows the built-in tools.

References and further reading

• EF Core SQL Server Full-Text Search (Microsoft Learn)
• Npgsql Full Text Search Documentation
• SQL Server Full-Text Search and EF Core (Brice Lambson)
• Postgres Full Text Search with EF Core (Egor Tarasov)