How to perform full-text search in SQL Server from C# with EF Core

Full-text search is built for finding words, phrases and approximate word forms inside large SQL Server datasets, rather than comparing every row with a wildcard. Developers attending C# Corner Annual Conference 2020 will recognise the familiar LIKE '%cloud%' query, but that pattern becomes expensive as content grows and cannot rank matches. SQL Server Full-Text Search maintains a linguistic index and can return the strongest rows first.

EF Core does not expose SQL Server’s full-text predicates through a dedicated LINQ operator. In practice, the cleanest implementation keeps EF Core for mapping, change tracking and ordinary queries, then uses parameterised raw SQL where CONTAINS or FREETEXT is required. The surrounding design still matters: a safe search term, sensible filters, stable pagination and an index that reflects recent writes are all part of the feature.

Choose the matching strategy

Decide how much meaning the query should carry before writing C#. Literal substring matching, Boolean-style matching and natural-language matching solve different problems, and SQL Server exposes each through different syntax.

CONTAINS is usually the best default for a website search box because it supports words, exact phrases, prefix terms and Boolean operators without treating every input as prose. FREETEXT is valuable for an article archive or knowledge base, where users may type a question and expect stop words, stemming and broader language matching. LIKE remains appropriate for a short, bounded administrative lookup such as an exact code prefix; applying it to millions of long documents is a different performance proposition.

Create and maintain the full-text index

Full-Text Search is an engine component on SQL Server. Azure SQL Database also supports the feature, although it does not use the same explicit catalogue-creation step. On SQL Server, a catalogue contains full-text indexes, and each index records the words and positions from selected columns. These are database objects, not a client-side index created by EF Core.

For a SQL Server database, the basic setup looks like this:

CREATE FULLTEXT CATALOG ArticleCatalog AS DEFAULT;

CREATE FULLTEXT INDEX ON dbo.Articles
(
    Title LANGUAGE N'Australian English',
    Body LANGUAGE N'Australian English'
)
KEY INDEX PK_Articles;

The key index supplies document identifiers, while the indexed columns determine which fields participate in searching. Choose text columns rather than binary fields, and include the columns users actually search. For a mixed content model, indexing a normalised Title and Body is usually clearer than indexing every derived or duplicated value. Changes are reflected asynchronously, so a write can remain visible to normal SQL before the full-text index catches up.

For Australian content, configure the full-text language consistently as Australian English where the SQL Server installation provides it. Stemming then handles common inflections, but regional spelling still needs deliberate handling: a support corpus containing both organisation and organization may benefit from synonym expansion or application-side alias terms. This prevents Australian users from missing useful documents because a customer, partner or internal author used the other spelling.

Query from EF Core safely

Use a raw relational query only for the full-text predicate, then let EF Core materialise ordinary entities. FromSqlInterpolated converts the C# value into a SQL parameter, avoiding string concatenation and preserving a clear provider boundary.

public sealed class Article
{
    public int Id { get; set; }
    public string Title { get; set; } = "";
    public string Body { get; set; } = "";
}

public async Task<List<Article>> SearchAsync(
    DbContext db,
    string query,
    CancellationToken cancellationToken)
{
    var term = query.Trim();

    var articles = await db.Articles
        .FromSqlInterpolated($"""
            SELECT a.*
            FROM dbo.Articles AS a
            WHERE CONTAINS(a.Title, {term})
               OR CONTAINS(a.Body, {term})
            ORDER BY RANK DESC
            """)
        .AsNoTracking()
        .ToListAsync(cancellationToken);

    return articles;
}

The FormattableString overload is important because it creates a parameter rather than inserting the user’s text into the command. Passing a normal interpolated string or concatenating the value would weaken or remove that protection. The example also disables tracking because search results are read-only in most APIs. RANK uses SQL Server’s relevance score, giving tight phrases and repeated occurrences more prominence than isolated words.

EF Core 8 and later can map a scalar search result more directly with Database.SqlQueryRaw<T>(). Select RANK AS SearchRank, then apply OrderByDescending, Skip and Take in LINQ for predictable paging. Keep the raw SQL limited to the search and rank columns; projecting a lightweight DTO avoids loading long article bodies for a page that displays titles and excerpts only. A keyless entity with FromSqlRaw is the compatible alternative for projects on earlier EF Core releases.

Control relevance and result scope

Full-text predicates combine cleanly with ordinary SQL filters. A multi-tenant Australian SaaS product can add a.TenantId = @tenantId, a publication date or category to the same WHERE clause, provided the filter is parameterised too. This prevents a relevant row from one customer appearing in another customer’s results while allowing SQL Server to combine the filter with the indexed search operation.

Pagination must have a deterministic order. Rank can tie, so add the unique article identifier as a tie-breaker, and decide whether page numbers are acceptable for users who refine a query while paging. A cursor containing both rank and identifier is more reliable for an actively updated catalogue, although it requires a carefully designed comparison expression rather than a large OFFSET.

Search syntax changes the result. CONTAINS with "cloud native" seeks that phrase, while "dotnet*" requests prefix matching; both differ from a leading-wildcard LIKE. Users rarely need SQL Server’s complete Boolean grammar, so expose familiar options such as phrase and prefix only when the application builds them deliberately. Preserve the raw relevance score internally even if the public API displays a normalised value.

Harden search for production

Parameterisation prevents SQL injection, but a full-text condition is still a small search language. A user who submits data AND (azure OR cloud) can change Boolean behaviour even though the value remains a parameter. If the product expects literal words, construct an allow-listed expression from quoted terms. If it expects natural search, FREETEXT is usually less surprising than exposing advanced syntax to every visitor.

Keep search text within practical limits, reject control characters, and decide whether to apply a stop list. Trimming and minimum-length checks belong at the request boundary, while phrase handling and stemming belong to the SQL index. Also set a query timeout and cancellation token so an expensive broad query cannot occupy request resources indefinitely. Logging the normalised term, duration and result count supports tuning without storing sensitive search text unnecessarily.

Operational care is part of correctness. SQL Server tracks index changes after the initial population; ALTER FULLTEXT INDEX ... REORGANIZE can refresh the index, and OPTIMIZE can improve it, but both affect database resources. Schedule that work carefully because Australia observes daylight saving and moves between AEST and AEDT. Teams running SQL Server on premises should track catalogue growth and wait-time metrics, while Azure SQL users should still monitor database resources and service limits.

Australian organisations often mix existing SQL Server estates with Azure SQL for new applications, so an EF Core data layer should not assume a single hosting model. Health, education and government workloads may also be governed by the Australian Privacy Act and state or federal procurement requirements. Confirm that the selected Australian Azure region supports the required services and that the search configuration is covered by backup and recovery procedures.

Deliver a practical search endpoint

In ASP.NET Core, map the search route to a small service method, pass the request cancellation token through EF Core, and return a DTO containing the title, excerpt and relevance information. Do not serialise tracked entities or framework exception details to clients. If dates, enums or flattened fields need a stable JSON contract, this guide to custom JSON converters shows how to keep that conversion deliberate and consistent.

Expose the product’s language rather than SQL Server syntax. Australian teams should expect users to type colour even when technical documentation uses color. In customer-support content, people may also use informal terms such as arvo instead of afternoon; an alias dictionary can expand those inputs before querying. Keep the expansion rules versioned and measurable, because broad synonyms can improve recall while reducing precision. The original search term should remain available for analytics and refinement where privacy policies permit.

Search is also a distributed-systems concern for teams in Sydney, Melbourne and Brisbane. Azure regions around those cities can keep latency low, but replication, backups and indexing schedules still cross network and time-zone boundaries. A practical Australian deployment uses Australian English content rules, explicit data-residency choices, and a controlled degraded response for an unavailable search service. With those controls in place, the EF Core raw-query pattern provides a secure, repeatable bridge from SQL Server’s linguistic index to a responsive .NET application.