LINQ to SQL Full Text Search: Use CONTAINS From C

LINQ to SQL full text search is not exposed by the provider. The standard Contains() method compiles to a LIKE '%term%' query, which bypasses the full-text index. To use CONTAINS or FREETEXT you must call the T-SQL with DataContext.ExecuteQuery and pass parameters yourself. Create a full-text catalog and index first.

The examples were tested on SQL Server 2019 CU18 at compatibility level 150; behaviour differs before SQL Server 2016 SP1. A LIKE '%term%' predicate that comes from string.Contains() cannot use the full-text index, and large tables scan instead.

Why LINQ to SQL Does Not Expose Full-Text Search

SQL Server full text search from LINQ is unavailable because the provider translates a fixed operator set, and CONTAINS/FREETEXT are not included. C# string.Contains() becomes LIKE '%term%', which reads the raw column and never touches the full-text index. You therefore need ExecuteQuery after the catalog and index exist. The limitation still applies to SQL Server 2016 through 2022.

CONTAINS and FREETEXT are Transact-SQL predicates in the WHERE clause; they are never exposed as methods on Table<T>. The provider would need a dedicated translation for each predicate, and it does not have one.

A full-text index stores tokenised words and supports prefix, proximity, inflectional and thesaurus searches. LIKE returns exact characters only, so it misses stemmed forms such as drive, drives and driven unless you write extra patterns.

Prerequisites: Full-Text Catalog and Index

Create a full-text catalog and then a full-text index on the columns to search, following CREATE FULLTEXT INDEX (Transact-SQL). The catalog is a logical container for full-text indexes. A table or indexed view can have only one full-text index, and that index can contain up to 1,024 columns.

CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;
GO

CREATE FULLTEXT INDEX ON dbo.Posts
(
    Title,
    Body
)
KEY INDEX PK_Posts
ON ftCatalog
WITH STOPLIST = SYSTEM;
GO

The KEY INDEX clause names a unique, single-key, non-nullable index; an integer key is recommended. Only char, varchar, nchar, nvarchar, text, ntext, image, xml and varbinary(max) columns can be indexed. Add TYPE COLUMN only for varbinary(max) or image documents; character columns do not need it.

The LANGUAGE clause sets the word breaker and stemmer, defaulting to the instance language; word breakers and stemmers for that language must be installed or SQL Server returns an error. You can place the catalog on a specific filegroup with ON (FILEGROUP filegroup_name, fulltext_catalog_name) when the database uses multiple filegroups. The WITH clause can set CHANGE_TRACKING to MANUAL, AUTO, or OFF. This example creates a default catalog with the system stoplist.

Calling CONTAINS From LINQ to SQL

Call CONTAINS with DataContext.ExecuteQuery, not with a LINQ expression. ExecuteQuery runs parameterised T-SQL and maps rows to a class, so the full-text predicate reaches SQL Server intact. The return type must match the selected columns by name. Use NVARCHAR for the search value; full-text predicates accept nvarchar, and varchar triggers implicit conversion.

public class SearchResult
{
    public int PostId { get; set; }
    public string Title { get; set; }
}

using (var db = new PostsDataContext())
{
    string term = "index";
    var results = db.ExecuteQuery<SearchResult>(
        @"SELECT PostId, Title
          FROM dbo.Posts
          WHERE CONTAINS((Title, Body), {0})",
        term).ToList();
}

CONTAINS searches precise or fuzzy matches for words, phrases, near terms and weighted terms. It supports prefix terms, so pass index* for prefix matching, or index for an exact word. The predicate can search multiple columns with (Title, Body). Within the condition, combine terms with AND, OR, and AND NOT when the search is more complex. For relevance-ordered results, use CONTAINSTABLE with RANK; CONTAINS alone returns rows in the order table access produces.

If you prefer a stored procedure, create dbo.SearchPosts with the same predicate and call EXEC dbo.SearchPosts {0} through ExecuteQuery. FORMSOF(INFLECTIONAL, index) matches indexes, indexed and indexing when the language stemmer supports them. Use FREETEXT for meaning-based matches; it word-breaks, stems and uses the thesaurus, but does not allow FORMSOF, wildcards, NEAR or weights. In FREETEXT, the word and is treated as a stopword and discarded, unlike the AND keyword in CONTAINS.

LIKE vs CONTAINS vs LINQ Contains: Comparison

LIKE and LINQ Contains() both perform substring matching; CONTAINS performs word-based matching. A leading wildcard forces a scan, while a full-text predicate can use the full-text index. LINQ Contains() hides the translation, so a developer may believe the full-text index is in use. Check the query plan: CONTAINS shows a full-text index match; LIKE '%term%' shows a scan.

Use LIKE only when the pattern starts with a wildcard or the table is small. If relevance matters, use CONTAINSTABLE.

Verifying the Full-Text Index

Check sys.fulltext_catalogs and sys.fulltext_indexes after creating the index. sys.fulltext_catalogs shows the catalog name and default property; sys.fulltext_indexes shows one row per indexed table with change-tracking settings. Then query sys.dm_fts_index_keywords to confirm the indexed terms are present; for example, index or maintenance should appear after initial population. Run these checks after the first CREATE FULLTEXT INDEX and after large loads when automatic change tracking is off. If the DMV returns no rows, check that the table has data and the catalog is online.

Errors and Edge Cases

Error 7645, "Full-text search is not enabled", means the instance, database or table lacks a full-text component; confirm the catalog and index exist before changing code. A leading wildcard such as LIKE '%term%' cannot use the index, and CONTAINS does not support it. Common stopwords are not indexed; use STOPLIST = OFF only if you must match them. FREETEXT is less precise than CONTAINS, so choose CONTAINS when the search terms must match exactly. For mid-string search on a small table, LIKE is acceptable; on a large table keep it as a fallback.

FAQ

What full-text query returns the same as a LIKE '%text%'?

None exactly: CONTAINS only does prefix matching, not mid-string, so keep LIKE only for wildcard-initial searches.

Why doesn't CONTAINS find substrings?

Full-text search tokenises and matches word prefixes, so it does not act as an arbitrary substring search.

Can SQL Server use a full-text index for a LIKE query?

No; LIKE ignores full-text unless you use CONTAINS/FREETEXT, and a leading wildcard forces a scan.

Does LINQ to SQL support Contains for full-text search?

Only string.Contains in C#, compiled to LIKE; use ExecuteQuery for CONTAINS.

Is full-text search available in SQL Server Express?

Yes, with Advanced Services; normal Express without them lacks the full-text engine.

Sources

• Microsoft Learn: CREATE FULLTEXT INDEX (Transact-SQL)
• Microsoft Learn: CONTAINS (Transact-SQL)
• Microsoft Learn: FREETEXT (Transact-SQL)