Implementing Full-Text Search in Entity Framework Core

Introduction

Searching for text in a database is one of the most common operations in web applications. Many developers start with simple LIKE queries, but as applications grow, this approach becomes inefficient and lacks flexibility.

Enter Full-Text Search (FTS)—a powerful feature in SQL Server that allows for complex text searches, ranking, and linguistic analysis.

In this guide, we'll explore how to implement Full-Text Search in Entity Framework Core (EF Core) using:

• EF.Functions.FreeText()

• EF.Functions.Contains()

• Raw SQL Queries (FromSqlRaw())

• Boolean and Proximity Search

• Prefix Matching and Ranking

By the end, you'll have a solid understanding of how to efficiently implement Full-Text Search in your .NET applications.

Why Do We Need Full-Text Search?

SQL’s standard LIKE operator works well for basic substring searches, but it has several limitations:

1. Performance Issues – LIKE queries don't use indexes efficiently, making them slow on large datasets.

2. Lack of Ranking – LIKE doesn’t provide ranking or ordering based on relevance.

3. Limited Matching – LIKE matches only exact substrings and cannot recognize synonyms, stemming (e.g., run, running), or inflected forms of words.

Full-Text Search (FTS) solves these problems by allowing:

• Word-based searches instead of simple substring searches.

• Ranking results based on relevance.

• Fuzzy matching (searching for car may match automobile).

• Boolean and proximity searches (AND, OR, NEAR).

Now, let's implement Full-Text Search in Entity Framework Core.

Step 1: Enable Full-Text Search in SQL Server

Before using Full-Text Search in EF Core, we must enable it in SQL Server.

Check if Full-Text Search is Installed

SELECT FULLTEXTSERVICEPROPERTY('IsFullTextInstalled');

If the result is 1, Full-Text Search is installed. 0 means it is not installed.

Install Full-Text Search (If Not Installed)

Install via SQL Server Installation Center

1. Open SQL Server Installation Center.

2. Click "Installation" in the left panel.

3. Click "New SQL Server stand-alone installation or add features to an existing installation."

4. Select your existing SQL Server instance and click Next.

5. In the Feature Selection window, check Full-Text and Semantic Extractions for Search.

6. Complete the installation by following the wizard steps.

Create a Full-Text Catalog

CREATE FULLTEXT CATALOG MyFullTextCatalog AS DEFAULT;

Create a Full-Text Index

Assuming we have a Products table:

CREATE FULLTEXT INDEX ON Products(Name, Description)
KEY INDEX PK_Products
ON MyFullTextCatalog;

Sample Data in Products Table

CREATE TABLE Product (
    Id INT PRIMARY KEY,
    Name NVARCHAR(255) NOT NULL,
    Description NVARCHAR(500) NOT NULL
);
INSERT INTO Product (Id, Name, Description)
VALUES 
    (1, 'Wireless Charger', 'A fast 10W wireless phone charger.'),
    (2, 'Laptop Charger', 'A 65W USB-C power adapter.'),
    (3, 'Bluetooth Speaker', 'A portable wireless speaker.'),
    (4, 'Gaming Mouse', 'A high-DPI gaming mouse.');

select * from Product

Method 1: Using EF.Functions.FreeText()

What is FreeText()?

• Finds words based on meaning, not just exact matches.

• Understands synonyms and language-specific variations.

using (var context = new ApplicationDbContext())
{
    string searchTerm = "wireless charger";

    var results = context.Products
        .Where(p => EF.Functions.FreeText(p.Name, searchTerm) ||
                    EF.Functions.FreeText(p.Description, searchTerm))
        .ToList();

    foreach (var product in results)
    {
        Console.WriteLine($"{product.Name} - {product.Description}");
    }
}

Expected Output: Finds "Bluetooth Speaker" because it contains "wireless".

Method 2: Using EF.Functions.Contains()

What is Contains()?

• Searches for exact words.

• Supports Boolean logic (AND, OR).

using (var context = new ApplicationDbContext())
{
    string searchTerm = "charger";

    var results = context.Products
        .Where(p => EF.Functions.Contains(p.Name, searchTerm) ||
                    EF.Functions.Contains(p.Description, searchTerm))
        .ToList();

    foreach (var product in results)
    {
        Console.WriteLine($"{product.Name} - {product.Description}");
    }
}

Expected Output: Finds only exact "charger" matches.

Using Contains() with Boolean Operators
Find both "wireless" and "charger":

string searchTerm = "\"wireless\" AND \"charger\"";

var results = context.Products
    .Where(p => EF.Functions.Contains(p.Name, searchTerm) ||
                EF.Functions.Contains(p.Description, searchTerm))
    .ToList();

Using Contains() with Prefix Matching

Find words starting with "charg*":

string searchTerm = "\"charg*\"";

var results = context.Products
    .Where(p => EF.Functions.Contains(p.Name, searchTerm) ||
                EF.Functions.Contains(p.Description, searchTerm))
    .ToList();

Expected Output:

• Excludes "Bluetooth Speaker" because it doesn’t contain "charger".

Method 3: Using FromSqlRaw() with FREETEXT

using (var context = new ApplicationDbContext())
{
    string searchTerm = "wireless charger";

    var results = context.Products
        .FromSqlRaw("SELECT * FROM Products WHERE FREETEXT((Name, Description), {0})", searchTerm)
        .ToList();

    foreach (var product in results)
    {
        Console.WriteLine($"{product.Name} - {product.Description}");
    }
}

Expected Output:

Method 4: Using FromSqlRaw() with CONTAINS

using (var context = new ApplicationDbContext())
{
    string searchTerm = "charger";

    var results = context.Products
        .FromSqlRaw("SELECT * FROM Products WHERE CONTAINS((Name, Description), {0})", searchTerm)
        .ToList();

    foreach (var product in results)
    {
        Console.WriteLine($"{product.Name} - {product.Description}");
    }
}

Expected Output:

Performance Optimization

Use Proper Indexing – Ensure primary keys are indexed for Full-Text.
Use Pagination – .Take(n).Skip(m) to limit results.
Maintain Indexes – Regularly rebuild Full-Text indexes:

ALTER FULLTEXT INDEX ON Products START FULL POPULATION;

Comparison of FreeText() vs. Contains() vs. FromSqlRaw()

Full-Text Search is essential for fast and flexible searching.

• Use FreeText() for meaning-based searches.

• Use Contains() for exact and Boolean logic searches.

• Use FromSqlRaw() for optimized Full-Text queries.

Implementing Full-Text Search with Ranking-Based Sorting in Entity Framework Core

When implementing Full-Text Search (FTS), ranking results by relevance is essential. SQL Server provides CONTAINSTABLE and FREETEXTTABLE, which return a rank score for each result. Higher scores indicate better matches.

How Ranking Works in Full-Text Search

• CONTAINSTABLE: Returns results ranked by how well they match a specific search term.

• FREETEXTTABLE: Similar to FREETEXT, but returns ranked results based on meaning-based search.

• RANK column: The higher the value, the more relevant the result.

Modify Query with Ranking (CONTAINSTABLE)

We use CONTAINSTABLE() to get ranked search results and then join it with the Products table.

using (var context = new ApplicationDbContext())
{
    string searchTerm = "wireless charger";

    var results = context.Products
        .FromSqlRaw(@"
            SELECT p.*, ftt.RANK
            FROM Products p
            INNER JOIN CONTAINSTABLE(Products, (Name, Description), {0}) AS ftt
            ON p.Id = ftt.[KEY]
            ORDER BY ftt.RANK DESC", searchTerm)
        .ToList();

    foreach (var product in results)
    {
        Console.WriteLine($"{product.Name} - {product.Description} (Rank: {product.GetType().GetProperty("RANK")?.GetValue(product)})");
    }
}

Expected Output:

• Wireless Charger has the highest rank because it matches both "wireless" and "charger".

• Bluetooth Speaker has a lower rank because it only matches "wireless".

Ranking with FREETEXTTABLE (Semantic Search)

FREETEXTTABLE works like FREETEXT() but returns rank scores.

using (var context = new ApplicationDbContext())
{
    string searchTerm = "wireless charger";

    var results = context.Products
        .FromSqlRaw(@"
            SELECT p.*, ftt.RANK
            FROM Products p
            INNER JOIN FREETEXTTABLE(Products, (Name, Description), {0}) AS ftt
            ON p.Id = ftt.[KEY]
            ORDER BY ftt.RANK DESC", searchTerm)
        .ToList();
}

Best for semantic searches, where results should match by meaning, not exact words.

When to Use CONTAINSTABLE vs. FREETEXTTABLE?

• Use CONTAINSTABLE() for precise ranking when searching for exact words.

• Use FREETEXTTABLE() for semantic ranking when searching for meaning-based words.

• Both allow ranking-based sorting, making search results smarter.