Modern search experiences demand more than simple keyword matching. Users expect a search box to understand meaning ("wireless audio device" should find "Bluetooth Headphones") while still respecting exact terms ("USB-C 65W" should match precisely). Neither vector search nor keyword search alone solves both problems well. Hybrid search combines them — and Reciprocal Rank Fusion (RRF) is the industry-standard algorithm for merging the two result sets.
In this article we will build a complete, fully working hybrid search endpoint using:
- .NET 10 Minimal APIs
- Entity Framework Core with the native SQL Server
vector type - SQL Server Full-Text Search (
CONTAINSTABLE) for keyword ranking - Microsoft.Extensions.AI (
IEmbeddingGenerator) for generating embeddings - Reciprocal Rank Fusion to merge both rankings into a single result list
How Hybrid Search Works
- Vector search — converts the query into an embedding and ranks products by cosine distance (semantic similarity).
- Keyword search — uses SQL Server Full-Text Search to rank products by lexical relevance.
- Fusion — merges both ranked lists with the RRF formula:
RRF(d) = Σ 1 / (k + rank(d)) where k = 60
A document appearing near the top of both lists accumulates a high combined score, while a document ranked highly in only one list still gets a fair chance to surface.
Step 1: Enable and Verify SQL Server Full-Text Search
Keyword ranking relies on SQL Server's Full-Text Search feature. Before writing any application code, make sure the feature is installed on your SQL Server instance:
SELECT FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') AS IsFullTextInstalled;
A result of 1 means Full-Text Search is installed. If it returns 0, re-run the SQL Server installer and add the Full-Text and Semantic Extractions for Search feature.
Step 2: Find the Primary Key Index of the Products Table
A full-text index requires a unique, single-column, non-nullable key index — typically the primary key. Find its name first:
SELECT name FROM sys.indexes
WHERE object_id = OBJECT_ID('dbo.Products') AND is_primary_key = 1;
In our example the primary key index is named PK_Products. Note this name — you will need it in Step 4.
Step 3: Create a Full-Text Catalog
A full-text catalog is a logical container for full-text indexes. Create one and mark it as the default:
CREATE FULLTEXT CATALOG ProductCatalog AS DEFAULT;
Step 4: Create the Full-Text Index on the Products Table
Now create the full-text index on the Name column. The LANGUAGE option controls word breaking and stemming — use 1033 for English (or 1055 for Turkish if your product names are in Turkish):
CREATE FULLTEXT INDEX ON dbo.Products
(
Name LANGUAGE 1033 -- 1033 = English, 1055 = Turkish
)
KEY INDEX PK_Products
ON ProductCatalog
WITH CHANGE_TRACKING AUTO;
GO
CHANGE_TRACKING AUTO keeps the index up to date automatically whenever rows are inserted, updated, or deleted.
Step 5: Verify the Index Population Status
Full-text index population runs asynchronously. Check whether it has finished:
SELECT OBJECTPROPERTYEX(OBJECT_ID('dbo.Products'), 'TableFullTextPopulateStatus');
-- 0 = population completed
Step 6: Test Full-Text Search Directly in SQL
Exact word match:
SELECT Id, Name
FROM dbo.Products
WHERE CONTAINS(Name, N'"Bluetooth"');
Prefix (wildcard) match:
SELECT Id, Name
FROM dbo.Products
WHERE CONTAINS(Name, N'"B*"');
Ranked results with CONTAINSTABLE — this is exactly what our application will use, because it returns a relevance RANK we can sort by:
SELECT p.Id, p.Name, ft.RANK
FROM CONTAINSTABLE(dbo.Products, Name, N'"Bluetooth"') ft
JOIN dbo.Products p ON p.Id = ft.[KEY]
ORDER BY ft.RANK DESC;
Step 7: The Product Entity and Seeding Embeddings
Each product stores its name and an embedding vector generated by an AI model. The SqlVector<float> type maps directly to SQL Server's native vector column type. The seed endpoint generates an embedding for every sample product and persists it:
// POST /products/seed — seeds sample products with their embeddings
group.MapPost("/seed", async (
AppDbContext dbContext,
IEmbeddingGenerator<string, Embedding<float>> embeddingGenerator) =>
{
if (await dbContext.Products.AnyAsync())
return Results.Ok("Products already seeded.");
var products = new List<Product>();
foreach (var name in SampleProducts)
{
var result = await embeddingGenerator.GenerateAsync([name]);
products.Add(new Product
{
Name = name,
Embedding = new SqlVector<float>(result[0].Vector)
});
}
dbContext.Products.AddRange(products);
await dbContext.SaveChangesAsync();
return Results.Ok($"{products.Count} products seeded.");
})
.WithSummary("Seeds sample products with their embeddings");
Step 8: Pure Vector Search (For Comparison)
Before combining approaches, here is the plain semantic search endpoint. It embeds the query and orders products by cosine distance using EF.Functions.VectorDistance:
// GET /products/search?q=... — semantic search
group.MapGet("/search", async (
string q,
AppDbContext dbContext,
IEmbeddingGenerator<string, Embedding<float>> embeddingGenerator) =>
{
var queryResult = await embeddingGenerator.GenerateAsync([q]);
var queryVector = new SqlVector<float>(queryResult[0].Vector);
var results = await dbContext.Products
.Where(p => p.Embedding != null)
.OrderBy(p => EF.Functions.VectorDistance("cosine", p.Embedding!.Value, queryVector))
.Take(5)
.Select(p => new
{
p.Id,
p.Name,
Distance = EF.Functions.VectorDistance("cosine", p.Embedding!.Value, queryVector)
})
.ToListAsync();
return Results.Ok(results);
})
.WithSummary("Performs semantic search over product names");
Step 9: The Hybrid Search Endpoint
The hybrid endpoint performs four sub-steps:
- Generate the query embedding.
- Run vector search — take the top 20 by cosine distance.
- Run keyword search — take the top 20 by
CONTAINSTABLE rank. - Fuse both ranked lists with RRF and return the top 5.
9.1 Generate the Query Embedding and Run Vector Search
// GET /products/hybrid-search?q=... — hybrid search (vector + keyword, RRF)
group.MapGet("/hybrid-search", async (
string q,
AppDbContext dbContext,
IEmbeddingGenerator<string, Embedding<float>> embeddingGenerator) =>
{
// 1. Generate query embedding
var queryResult = await embeddingGenerator.GenerateAsync([q]);
var queryVector = new SqlVector<float>(queryResult[0].Vector);
// 2. Vector search — semantic ranking by cosine distance
var vectorResults = await dbContext.Products
.Where(p => p.Embedding != null)
.OrderBy(p => EF.Functions.VectorDistance("cosine", p.Embedding!.Value, queryVector))
.Take(20)
.Select(p => new { p.Id, p.Name })
.ToListAsync();
9.2 Keyword Search with CONTAINSTABLE
The keyword leg uses a parameterized raw SQL query through SqlQuery<T>. EF Core turns the interpolated {q} into a SQL parameter, keeping the query safe from SQL injection:
// 3. Keyword search — lexical ranking via full-text search, most relevant first
var rankedKeywordResults = await dbContext.Database
.SqlQuery<KeywordSearchResult>($"""
SELECT TOP (20) p.[Id], p.[Name], kt.[RANK] AS [Rank]
FROM [Products] AS p
INNER JOIN CONTAINSTABLE([Products], [Name], {q}) AS kt ON p.[Id] = kt.[KEY]
ORDER BY kt.[RANK] DESC
""")
.ToListAsync();
var keywordResults = rankedKeywordResults
.Select(r => new { r.Id, r.Name })
.ToList();
The result of the raw SQL query is mapped through a small record:
// Maps the result of the CONTAINSTABLE full-text search query used in keyword ranking
private sealed record KeywordSearchResult(int Id, string Name, int Rank);
9.3 Reciprocal Rank Fusion
Both lists are already ordered by relevance, so the loop index i corresponds to the rank (0-based, hence i + 1 to make it 1-based). Products appearing in both lists have their scores summed via GetValueOrDefault:
// 4. Reciprocal Rank Fusion (RRF, k=60)
const double k = 60.0;
var scores = new Dictionary<int, double>();
for (var i = 0; i < vectorResults.Count; i++)
scores[vectorResults[i].Id] = scores.GetValueOrDefault(vectorResults[i].Id) + 1.0 / (k + i + 1);
for (var i = 0; i < keywordResults.Count; i++)
scores[keywordResults[i].Id] = scores.GetValueOrDefault(keywordResults[i].Id) + 1.0 / (k + i + 1);
var nameMap = vectorResults.Concat(keywordResults)
.GroupBy(r => r.Id)
.ToDictionary(g => g.Key, g => g.First().Name);
var results = scores
.Select(kv => new { Id = kv.Key, Name = nameMap[kv.Key], RrfScore = kv.Value })
.OrderByDescending(r => r.RrfScore)
.Take(5)
.ToList();
return Results.Ok(results);
})
.WithSummary("Performs hybrid search combining vector similarity and keyword matching using RRF");
Why k = 60?
The constant k = 60 comes from the original RRF research paper and is used by most search engines (including Azure AI Search and Elasticsearch). It dampens the influence of very high ranks so that a document ranked #1 in one list does not completely dominate documents that are consistently ranked #3–#5 across both lists.
Step 10: Try It Out
- Seed the database:
- Run a pure semantic search:
GET /products/search?q=wireless audio device
- Run the hybrid search:
GET /products/hybrid-search?q=bluetooth headphones
Example hybrid search response:
[
{ "id": 1, "name": "Wireless Bluetooth Headphones", "rrfScore": 0.03252 },
{ "id": 5, "name": "Bluetooth Neckband Earphones", "rrfScore": 0.03175 },
{ "id": 2, "name": "Noise Cancelling Earbuds", "rrfScore": 0.01587 },
{ "id": 3, "name": "Over-Ear Studio Headphones", "rrfScore": 0.01562 },
{ "id": 14, "name": "Mini Bluetooth Keyboard", "rrfScore": 0.01538 }
]
Notice how "Wireless Bluetooth Headphones" ranks first: it scores highly in both the semantic list (it means "bluetooth headphones") and the keyword list (it literally contains both words), so its RRF contributions add up.
Summary
- Enable and verify SQL Server Full-Text Search, then create a catalog and a full-text index on the
Name column. - Store product embeddings in SQL Server's native
vector column using SqlVector<float>. - Run vector search with
EF.Functions.VectorDistance and keyword search with CONTAINSTABLE, each returning a top-20 ranked list. - Merge both lists with Reciprocal Rank Fusion (k = 60) and return the top results.
Hybrid search gives you the best of both worlds: semantic understanding for natural-language queries and lexical precision for exact terms — all inside SQL Server, without any external search engine.
Appendix: Complete Hybrid Search Endpoint Code
Here is the finished, fully working hybrid search endpoint in one piece, ready to copy into your project:
using Microsoft.Data.SqlTypes;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.AI;
using WebApplication.API.Data;
namespace WebApplication.API.Endpoints;
public static class ProductsEndpoints
{
public static void MapProductEndpoints(this WebApplication app)
{
var group = app.MapGroup("/products").WithTags("Products");
// GET /products/hybrid-search?q=... — hybrid search (vector + keyword, RRF)
group.MapGet("/hybrid-search", async (
string q,
AppDbContext dbContext,
IEmbeddingGenerator<string, Embedding<float>> embeddingGenerator) =>
{
// 1. Generate query embedding
var queryResult = await embeddingGenerator.GenerateAsync([q]);
var queryVector = new SqlVector<float>(queryResult[0].Vector);
// 2. Vector search — semantic ranking by cosine distance
var vectorResults = await dbContext.Products
.Where(p => p.Embedding != null)
.OrderBy(p => EF.Functions.VectorDistance("cosine", p.Embedding!.Value, queryVector))
.Take(20)
.Select(p => new { p.Id, p.Name })
.ToListAsync();
// 3. Keyword search — lexical ranking using SQL Server full-text search
// (CONTAINSTABLE), most relevant first
var rankedKeywordResults = await dbContext.Database
.SqlQuery<KeywordSearchResult>($"""
SELECT TOP (20) p.[Id], p.[Name], kt.[RANK] AS [Rank]
FROM [Products] AS p
INNER JOIN CONTAINSTABLE([Products], [Name], {q}) AS kt ON p.[Id] = kt.[KEY]
ORDER BY kt.[RANK] DESC
""")
.ToListAsync();
var keywordResults = rankedKeywordResults
.Select(r => new { r.Id, r.Name })
.ToList();
// 4. Reciprocal Rank Fusion (RRF, k=60)
const double k = 60.0;
var scores = new Dictionary<int, double>();
for (var i = 0; i < vectorResults.Count; i++)
scores[vectorResults[i].Id] = scores.GetValueOrDefault(vectorResults[i].Id) + 1.0 / (k + i + 1);
for (var i = 0; i < keywordResults.Count; i++)
scores[keywordResults[i].Id] = scores.GetValueOrDefault(keywordResults[i].Id) + 1.0 / (k + i + 1);
var nameMap = vectorResults.Concat(keywordResults)
.GroupBy(r => r.Id)
.ToDictionary(g => g.Key, g => g.First().Name);
var results = scores
.Select(kv => new { Id = kv.Key, Name = nameMap[kv.Key], RrfScore = kv.Value })
.OrderByDescending(r => r.RrfScore)
.Take(5)
.ToList();
return Results.Ok(results);
})
.WithSummary("Performs hybrid search combining vector similarity and keyword matching using RRF");
}
// Maps the result of the CONTAINSTABLE full-text search query used in keyword ranking
private sealed record KeywordSearchResult(int Id, string Name, int Rank);
}
