ASP.NET Core  

Using SQL Server Full-Text Search in an ASP.NET Core + Angular App

Practical Guide to Building Efficient Search in Full-Stack Applications

Modern applications often require robust search functionality. Users expect search to be fast, relevant, and capable of handling complex queries, including phrases, stemming, and ranking results. SQL Server Full-Text Search (FTS) is a powerful tool that allows developers to implement rich search capabilities directly in the database.

In this article, we will explore how to implement SQL Server Full-Text Search in an ASP.NET Core backend and integrate it seamlessly with an Angular frontend. We will discuss architecture, backend implementation, Angular integration, and best practices for production applications.

1. Why Full-Text Search?

Before diving into implementation, let’s understand why we need Full-Text Search instead of simple LIKE queries:

1.1 Limitations of LIKE

  • LIKE '%term%' performs table scans, causing performance degradation on large datasets.

  • Cannot search for phrases or multiple word forms efficiently.

  • Does not provide relevance ranking.

1.2 Advantages of SQL Server Full-Text Search

  • Indexes are optimized for textual search.

  • Supports phrase search, proximity search, and stemming.

  • Can rank results based on relevance using CONTAINS, FREETEXT, and CONTAINSTABLE.

  • Scales to millions of rows with acceptable performance.

For large-scale enterprise apps, FTS is a practical choice without introducing external search engines like ElasticSearch, unless advanced features are required.

2. Architecture Overview

A typical architecture for Angular + ASP.NET Core + SQL Server FTS looks like this:

Angular Frontend
    |
    | Search Query
    v
ASP.NET Core API
    |
    | Executes FTS query on SQL Server
    v
SQL Server Full-Text Index
    |
    | Returns top results
    v
ASP.NET Core API
    |
    v
Angular Frontend (Display results)

2.1 Frontend Responsibilities

  • Collect search input from users.

  • Call backend API asynchronously.

  • Display results with relevance score.

  • Handle debouncing and progressive feedback.

2.2 Backend Responsibilities

  • Validate search input.

  • Generate secure FTS queries.

  • Return paginated, ranked results.

  • Optionally log searches for analytics.

3. Setting Up SQL Server Full-Text Search

3.1 Enable Full-Text Search

  • Ensure SQL Server instance has Full-Text Search feature installed.

  • Confirm using SQL Server Management Studio (SSMS):

SELECT FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') AS IsFullTextInstalled;

3.2 Create Full-Text Index

Suppose we have a Products table:

CREATE TABLE Products
(
    ProductId INT PRIMARY KEY IDENTITY,
    Name NVARCHAR(200),
    Description NVARCHAR(MAX),
    Category NVARCHAR(100)
);

To enable FTS:

  1. Create a Full-Text Catalog:

CREATE FULLTEXT CATALOG ProductCatalog AS DEFAULT;

  1. Create Full-Text Index on columns:

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

  • LANGUAGE 1033 is for English.

  • Full-Text Indexes are updated automatically after inserts/updates.

  • SQL Server supports incremental updates for performance.

4. ASP.NET Core Backend Implementation

We use Entity Framework Core and raw SQL queries to leverage FTS.

4.1 Product Entity

public class Product
{
    public int ProductId { get; set; }
    public string Name { get; set; } = string.Empty;
    public string Description { get; set; } = string.Empty;
    public string Category { get; set; } = string.Empty;
}

4.2 ProductDbContext

public class ProductDbContext : DbContext
{
    public ProductDbContext(DbContextOptions<ProductDbContext> options) : base(options) {}

    public DbSet<Product> Products { get; set; } = null!;
}

4.3 Search Service

We encapsulate FTS logic in a service.

using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;

public interface IProductSearchService
{
    Task<List<ProductSearchResult>> SearchAsync(string query, int page = 1, int pageSize = 20);
}

public class ProductSearchService : IProductSearchService
{
    private readonly ProductDbContext _context;

    public ProductSearchService(ProductDbContext context)
    {
        _context = context;
    }

    public async Task<List<ProductSearchResult>> SearchAsync(string query, int page = 1, int pageSize = 20)
    {
        if (string.IsNullOrWhiteSpace(query))
            return new List<ProductSearchResult>();

        var sql = @"
            SELECT p.ProductId, p.Name, p.Description, p.Category,
                   KEY_TBL.RANK
            FROM Products p
            INNER JOIN CONTAINSTABLE(Products, (Name, Description), @search) AS KEY_TBL
            ON p.ProductId = KEY_TBL.[KEY]
            ORDER BY KEY_TBL.RANK DESC
            OFFSET @offset ROWS FETCH NEXT @pageSize ROWS ONLY;
        ";

        var parameters = new[]
        {
            new SqlParameter("@search", query),
            new SqlParameter("@offset", (page - 1) * pageSize),
            new SqlParameter("@pageSize", pageSize)
        };

        return await _context.Set<ProductSearchResult>()
                             .FromSqlRaw(sql, parameters)
                             .ToListAsync();
    }
}

public class ProductSearchResult
{
    public int ProductId { get; set; }
    public string Name { get; set; } = string.Empty;
    public string Description { get; set; } = string.Empty;
    public string Category { get; set; } = string.Empty;
    public int RANK { get; set; }
}

4.4 Controller

[Route("api/[controller]")]
[ApiController]
public class ProductsController : ControllerBase
{
    private readonly IProductSearchService _searchService;

    public ProductsController(IProductSearchService searchService)
    {
        _searchService = searchService;
    }

    [HttpGet("search")]
    public async Task<IActionResult> Search([FromQuery] string query, [FromQuery] int page = 1)
    {
        if (string.IsNullOrWhiteSpace(query))
            return BadRequest("Query cannot be empty.");

        var results = await _searchService.SearchAsync(query, page);
        return Ok(results);
    }
}

5. Angular Frontend Integration

5.1 Search Component

import { Component } from '@angular/core';
import { FormControl } from '@angular/forms';
import { debounceTime, switchMap } from 'rxjs';
import { ProductService } from '../services/product.service';

@Component({
  selector: 'app-product-search',
  templateUrl: './product-search.component.html',
})
export class ProductSearchComponent {
  searchControl = new FormControl('');
  results: any[] = [];
  loading = false;

  constructor(private productService: ProductService) {
    this.searchControl.valueChanges
      .pipe(
        debounceTime(300),
        switchMap(query => {
          this.loading = true;
          return this.productService.searchProducts(query);
        })
      )
      .subscribe({
        next: data => {
          this.results = data;
          this.loading = false;
        },
        error: err => {
          console.error(err);
          this.loading = false;
        }
      });
  }
}

5.2 HTML Template

<div class="search-box">
  <input type="text" [formControl]="searchControl" placeholder="Search products..." />
</div>

<div *ngIf="loading">Searching...</div>

<ul *ngIf="results.length > 0">
  <li *ngFor="let product of results">
    <strong>{{ product.name }}</strong> - {{ product.description }} (Rank: {{ product.rank }})
  </li>
</ul>

<div *ngIf="!loading && results.length === 0">No results found.</div>

5.3 Product Service

import { Injectable } from '@angular/core';
import { HttpClient, HttpParams } from '@angular/common/http';
import { Observable } from 'rxjs';

@Injectable({ providedIn: 'root' })
export class ProductService {
  private apiUrl = 'https://localhost:5001/api/products';

  constructor(private http: HttpClient) {}

  searchProducts(query: string, page = 1): Observable<any> {
    const params = new HttpParams().set('query', query).set('page', page.toString());
    return this.http.get<any>(`${this.apiUrl}/search`, { params });
  }
}

6. Angular Best Practices

  1. Debouncing Input: Prevent excessive API calls for every keystroke.

  2. Async Pipe: For streaming results, use async pipe to reduce manual subscription handling.

  3. Error Handling: Show user-friendly messages for server or network errors.

  4. Pagination: Fetch results in pages to reduce payload size.

  5. Change Detection Strategy OnPush: Improves performance for large result sets.

7. Security Considerations

  • SQL Injection Protection: Always use parameters instead of string interpolation.

  • Rate Limiting: Avoid abuse for search endpoints.

  • Validation: Limit query length and disallow dangerous characters if needed.

8. Performance Optimization

  1. Full-Text Index Maintenance: Rebuild or reorganize periodically for large datasets.

  2. Filtered Indexes: Combine FTS with filtered indexes for additional performance.

  3. Pagination: Always limit the number of results per request.

  4. Asynchronous Queries: Use async/await for non-blocking backend calls.

  5. Caching: Cache common search queries for faster response.

9. Advanced FTS Features

  • Proximity Search: FORMSOF(INFLECTIONAL, "run") for variations of a word.

  • Weighted Columns: Boost certain columns in CONTAINSTABLE to rank results higher.

  • Phrase Search: Use quotes "multi word phrase" in CONTAINS.

  • Stop Words: SQL Server ignores common words; adjust stoplists for your use case.

Summary

By combining Angular with ASP.NET Core and SQL Server Full-Text Search, you can build a scalable, fast, and relevant search experience for enterprise applications. Key takeaways for senior developers:

  1. Use CONTAINSTABLE / FREETEXTTABLE for ranked search results.

  2. Parameterize all queries to prevent SQL injection.

  3. Implement debouncing and pagination on the frontend for performance.

  4. Consider caching and async execution for heavy traffic.

  5. Monitor and maintain full-text indexes to ensure fast queries.

This architecture allows you to deliver an enterprise-grade search feature without introducing external search engines, keeping the stack simple and maintainable.

Membership not found