How to Implement Hybrid Search in ASP.NET Core 10 Using C#, PostgreSQL pgvector and Full-Text Search – Complete Guide (2026)
How to Implement Hybrid Search in ASP.NET Core 10 Using C#, PostgreSQL pgvector and Full-Text Search – Complete Guide (2026)
A developer searches for “500.30 after deployment,” while another asks “why does my web service fail to start on Windows?” These questions may point to the same support article, but they provide different retrieval signals. The first contains a precise technical term; the second describes intent. A useful search API should be able to use both signals without pretending their scores mean the same thing.
This tutorial implements hybrid search in ASP.NET Core 10 using C#, PostgreSQL pgvector and full-text search. PostgreSQL retrieves lexical candidates from a generated tsvector column and semantic candidates from stored embeddings. A small, explicit C# Reciprocal Rank Fusion component merges the ranked document IDs. The endpoint also exposes keyword-only and semantic-only modes for comparison.
How does hybrid search combine keywords and vectors?
Run two retrieval methods over the same eligible documents, keep a bounded ranked list from each, deduplicate document IDs and sum their reciprocal-rank contributions. Return the highest fused scores. This avoids adding incompatible raw lexical and vector scores, but it does not guarantee higher relevance: candidate quality, language configuration, filters and evaluation still matter.
1. Hybrid search versus keyword-only and vector-only retrieval
Dense embeddings represent text as floating-point coordinates learned by a model. They can help retrieve related wording, but may blur distinctions that matter for product codes, version numbers and error messages. A nearest vector is not proof of an exact match or a correct answer.
Lexical retrieval uses terms extracted from documents and queries. PostgreSQL full-text search normalizes text through parsers and dictionaries; it can handle inflection and phrase structure. It is not a raw substring search, nor is ts_rank a sparse neural embedding model. “Sparse lexical retrieval” in this guide refers to term-based evidence from PostgreSQL, not a separately trained sparse-vector encoder.
| Method | Useful signal | Important limitation |
|---|---|---|
| Keyword-only | Technical terminology, phrase and term matching | Vocabulary mismatch; tokenization can alter identifiers |
| Vector-only | Paraphrases and conceptual similarity | Can miss precise distinctions and exact strings |
| Hybrid with RRF | Agreement and complementary candidates across ranked lists | Cannot rescue a document absent from both lists |
| Exact code equality | A normalized identifier in a structured column | Does not answer broader natural-language intent |
This implementation is a retrieval API, not a chatbot. It returns source content and ranking evidence. There is no generated answer, agent or claim that a fused score is a probability. The distinct contribution is full-text candidate generation and fusion, rather than another embedding-generation or RAG tutorial.
2. PostgreSQL tsvector, tsquery, websearch_to_tsquery and GIN
A tsvector stores normalized lexemes with positional information; a tsquery expresses matching conditions. The @@ operator checks whether the document representation matches the query. We use an explicit english configuration for both indexing and query parsing, so behavior does not depend on a session’s default text-search configuration.
websearch_to_tsquery accepts web-style query input: ordinary terms, quoted phrases, OR and exclusions. This improves input tolerance compared with requiring callers to write tsquery syntax. It is still a query language: for example, a dash can mean NOT. Parameterization prevents SQL injection, but does not turn those search operators into literal characters.
These are commands to run, not captured database output. Stop words can disappear, stemming can change terms and punctuation can be interpreted differently from a human’s code-reading expectations. Use a structured Code column and equality filter for literal identifiers; do not promise full-text search preserves every SKU character.
The generated SearchVector column updates when its source fields change. A GIN index helps identify matching rows. PostgreSQL then calculates ts_rank for those matches and sorts them. GIN does not directly supply the requested relevance order, and ts_rank is not BM25. See PostgreSQL text-search controls, GIN indexing guidance and Npgsql full-text mapping.
English is a deliberate limitation for this example. The embedding model supports multilingual text, but that does not make the english lexical dictionary multilingual. For mixed-language collections, design and evaluate a language/configuration policy instead of silently applying one dictionary to everything.
3. Reciprocal Rank Fusion with a worked numerical example
For a document d, use RRF(d) = Σ 1 / (c + rank_i(d)) over lists that contain it. Ranks start at 1. A missing document contributes zero from that list. This implementation sets c=60 and gives the keyword and vector lists equal weight. The constant is unrelated to TopK or CandidateLimit.
The original RRF paper motivates rank fusion. Its experimental results do not prove an improvement on your dataset. Here, c=60 is a starting configuration, not an automatically optimal setting.
Suppose the keyword list is A, B, C and the vector list is B, D, A. These are constructed rankings, not model outputs:
| Document | Keyword rank | Vector rank | Contribution calculation | RRF score |
|---|---|---|---|---|
| A | 1 | 3 | 1/61 + 1/63 | 0.032266458 |
| B | 2 | 1 | 1/62 + 1/61 | 0.032522475 |
| C | 3 | Absent | 1/63 | 0.015873016 |
| D | Absent | 2 | 1/62 | 0.016129032 |
The fused order is B, A, D, C. B benefits from appearing near the top of both lists. Raw ts_rank and cosine distance values never enter this calculation. Floating-point division must be used: integer division such as 1/(60+rank) would produce zero for these ranks.
RRF deliberately discards score magnitude. A document barely ahead of another and a document far ahead both get adjacent ordinal ranks. It can also promote a mediocre item that appears in both lists. Deduplicate once per retriever, define tie handling and evaluate candidate budgets. Two equal-weight lists contribute at most 2/61 for a document ranked first in both, so a value around 0.03 is not “3% relevance.”
4. ASP.NET Core 10 project and compatible packages
Install the .NET 10 SDK, a compatible editor or Visual Studio workload, Docker with Linux containers, Ollama and Postman. The example runs ASP.NET Core and Ollama on the host and PostgreSQL in Docker. Hardware requirements and model latency depend on your environment; no throughput claim is made.
Microsoft EF runtime/design/tool versions are aligned. Npgsql has its own patch schedule, and its 10.0.3 package declares an EF Core 10-compatible dependency range. Pgvector.EntityFrameworkCore 0.3.0 documents EF Core 9/10 support. These are stable selections; do not substitute 11.x previews merely because NuGet displays them. Verify Npgsql dependencies, the vector adapter and EF design tooling when reproducing the tutorial.
5. Start PostgreSQL pgvector, pull Ollama and configure secrets
Use a fresh database for this standalone project. The Docker mapping below uses host port 5433 so it does not collide with another local PostgreSQL instance on 5432. The named volume preserves data; recreating a container does not reset credentials in an initialized database.
The official image installs the extension binaries; the EF migration enables vector in this database. Ollama must be installed and running before the pull command. The selected Nomic model uses full-size 768-dimensional vectors, a 512-token sequence limit and different document/query prefixes. Record the pulled model identity; do not silently update weights while keeping old stored vectors.
Keep these values private in your local Postman environment. User Secrets avoids committing credentials but does not encrypt its storage. The local database disables TLS only for this loopback development setup; remote production connections need verified TLS and a least-privilege runtime account. The initial Docker role is administrative convenience, not the recommended production role. Environment overrides include ConnectionStrings__SearchDb and Demo__ReadKey.
6. Document entity, generated full-text column and both indexes
Code values are normalized to uppercase ASCII identifiers. The database has a regular index for equality filtering, a GIN index for full-text matching and a cosine HNSW index for vector candidates. SearchVector is generated by PostgreSQL from Title, Content and Code. The application must not assign a manually assembled tsvector on ingestion.
HasGeneratedTsVectorColumn expresses the lexical mapping. HasPostgresExtension enables vector through migrations; UseVector registers the .NET provider integration. These are separate responsibilities. A NuGet package cannot install extension binaries on a PostgreSQL server.
The design-time factory needs only database configuration, so migration generation does not require the API’s read/write keys or a running embedding model. Execute these commands from the project directory:
This SQL explains the intended objects; it is not a second creation step after EF migrations. Provider-generated SQL can include coalesce expressions, migration history and different index names. Inspect it rather than replacing it blindly with the explanatory excerpt. Use reviewed migration scripts or bundles in production and avoid mixing EnsureCreated with migrations.
7. Complete local embedding client
The client calls the current POST /api/embed contract: input in, embeddings array out. It applies search_document: for indexed text and search_query: for query text, sets truncate=false and checks a single finite nonzero vector of length 768. The code does not mix the older /api/embeddings prompt contract with this response shape.
The 60-second linked token is a cooperative operation budget, not a measured optimal timeout. Caller cancellation flows into HTTP and database operations. Model loading or slow CPU inference may need a different budget based on measurement. A timeout does not prove the model instantly stopped all work.
The application limits title/content/query characters, but character count is not token count. Prefixes and the document code are part of the model input. A 512-token rejection maps to a safe 422 response; other model-side 400 rejections use the same general message. In production, use the appropriate tokenizer and deliberate chunking rather than silently discarding input.
Dimension equality alone is not model compatibility. Space is an application-managed contract label, not a digest of the model weights. Re-embed and version the corpus when changing weights, prefixes or preprocessing.
8. Implement Reciprocal Rank Fusion as pure C#
The helper receives already ordered, bounded candidate lists. It removes duplicate IDs within each list so one retriever cannot cast repeated votes. Rank numbering starts at 1 after deduplication. A document found in both lists receives both contributions; a missing rank stays null and contributes zero.
The final result uses descending RRF score and then ID for equal fused scores. The lexical branch also uses ID to order equal ts_rank values. The vector branch preserves raw distance ordering for index eligibility; tied vector distances can still be returned in a different order by the database, so the overall ranking is not guaranteed reproducible across changing executions.
In keyword-only or semantic-only mode, the same response score is a one-list RRF score. That preserves the branch’s order but is not its native relevance score. KeywordScore and CosineDistance remain separate nullable fields for diagnosis. Do not compare a one-list RRF score directly with a two-list score as if it measured improved relevance.
For this isolated check, copy Contracts.cs and RrfFusion.cs into a separate .NET 10 console project and use the snippet as its Program.cs. It does not require a database or provider. The check is provided for you to run; it was not executed as C# here. The displayed mathematical totals were independently verified with Python.
9. Keyword retrieval, semantic retrieval and the hybrid endpoint service
The lexical LINQ expression stays server-side: Matches translates to @@, WebSearchToTsQuery parses input in PostgreSQL, and Rank maps to ts_rank. Do not evaluate WebSearchToTsQuery in an ordinary C# local variable before composing the expression; it is a translation API, not a local query parser.
The vector branch uses cosine distance, sorted ascending with a bounded Take. Cosine similarity is one minus distance, but this API reports distance directly. Do not change the SQL ORDER BY into a descending arithmetic score and assume HNSW remains eligible. See pgvector-dotnet translation examples.
Both branches share Space, Category and ExactCode filters before their candidate limits. Category is case-sensitive in this example. ExactCode is normalized through the same policy as ingestion. These are relevance filters, not tenant authorization. Production permission predicates must be derived from trusted identity and applied to every branch.
Database queries run sequentially on the same scoped DbContext. EF Core does not support concurrent operations on one context. A repeatable-read transaction gives both queries a consistent database snapshot; inference completes before the transaction begins. HNSW remains approximate even inside a snapshot, and this transaction does not stabilize all future requests.
Indexing creates a new UUID every time. It is intentionally not an idempotent upsert. For durable production ingestion, use a source ID and version policy so retries after uncertain responses do not create duplicate corpus rows. Generated lexical data stays consistent with its source row; source text and dense vectors must also be updated together when implementing edits.
10. Complete Program.cs, access controls and safe errors
The reader key can call POST /api/search; the writer key can ingest and search. The route check normalizes case for its search allowlist. This is a small local demonstration, not a production authentication framework. Configure identity-provider validation, endpoint authorization policies, revocation and auditing before exposing a multi-user service.
The global handler returns safe ProblemDetails rather than database connection text or model response bodies. It logs type, status and trace ID. In .NET 10, handled exceptions can suppress framework diagnostics; this explicit event keeps failures visible without logging submitted queries or secrets.
The concurrency limiter permits four active endpoint requests per process, with no queue. It does not implement distributed, per-user or token-based quotas. The HttpClient disables redirects and uses a trusted configuration URL, so a caller cannot choose an arbitrary inference host. Keep model and database ports private.
Related foundations on ASP.NET PB: JWT authentication, IExceptionHandler error handling and IIS deployment. These explain surrounding concerns; they do not automatically provide document-level authorization for this search corpus.
11. Postman tests and a representative comparison dataset
Create private Postman variables baseUrl=http://localhost:5094, readKey and writeKey. Add Content-Type: application/json. Do not export environments containing secrets. Local HTTP is for the developer machine; production ingress must use HTTPS.
Ingest a small corpus
POST each object separately to {{baseUrl}}/api/documents with X-Api-Key: {{writeKey}}. The endpoint does not accept an array. Each successful request returns HTTP 200 with id, normalized code and title; UUIDs are generated at runtime.
Run the same query in three modes
POST {{baseUrl}}/api/search using X-Api-Key: {{readKey}}. Change only mode between keyword, semantic and hybrid:
Expect HTTP 200 with mode, candidateLimit, rrfConstant, keywordCandidates, vectorCandidates and results. Each hit includes native branch scores, nullable branch ranks and a fused score. No exact model rank or score is asserted here. Ordinary full-text terms are generally ANDed, so the lexical branch can be empty when the wording has no joint term match. That is useful evidence to record rather than hide.
The empty example describes an empty eligible corpus, not the measured result of the four-document test. A dependency failure does not return this response; it returns an error.
Test exact codes and search syntax separately
The equality predicate restricts both branches to IIS-500.30. It is not a guaranteed code-lookup endpoint: keyword-only mode still requires its text match, and approximate vector retrieval can return fewer candidates. If a business operation must fetch a specific code regardless of query relevance, implement a dedicated authorized equality lookup. Do not infer exact equality from a free-form query containing hyphens.
| Query / variation | What to inspect |
|---|---|
| 500.30 startup failure | Technical-token behavior and whether the correct error article is retrieved |
| How can I run my web service on a Windows server? | Paraphrase retrieval across modes |
| "application pool" | Phrase parsing in the lexical branch |
| hosting OR runtime | Explicit OR query behavior |
| the and or | Stop-word/query-language effects; no assumed lexical matches |
| Nonexistent category | No cross-category result leakage |
| ExactCode=IIS-500.31 | Both branches remain within the structured equality filter |
Negative tests
| Case | Expected contract |
|---|---|
| Missing/invalid key | 401 before inference |
| Read key on document ingestion | 403; no insert |
| Invalid mode, blank query or TopK outside 1–20 | 400 |
| CandidateLimit below TopK or above 100 | 400 |
| Unknown JSON property or malformed body | 400 |
| Over-token input rejected by model | 422; no vector stored |
| Model missing/unreachable | 503 in semantic or hybrid mode |
| Wrong vector dimensions or malformed model response | 502 |
| Embedding budget exhausted | 504 unless the caller has cancelled |
| Database unavailable | 503; no silent keyword fallback |
| Concurrency capacity exceeded | 429 |
Keyword-only mode does not invoke Ollama and is an explicit diagnostic option, not an automatic degraded response. Hybrid mode fails if its required model branch fails. If you introduce fallback in production, declare the returned mode and log the degraded operation rather than labeling keyword-only results “hybrid.”
12. Evaluate retrieval with Recall@K and MRR
Create relevance judgments before tuning the fusion constant. For each representative query, record the document IDs a reviewer considers relevant. Include exact codes, paraphrases, ambiguous queries, no-answer cases and permission-filtered queries. Keep the same corpus snapshot and judgments when comparing keyword, semantic and hybrid runs.
Recall@K is the number of judged relevant documents in the first K results divided by the total number of judged relevant documents for that query. Reciprocal rank is 1 divided by the rank of the first relevant result, or zero if none is retrieved. MRR averages reciprocal rank across the chosen query set. If you cap the scan at K, call it MRR@K and apply that rule consistently.
For example, if two documents are judged relevant and the first three unique results contain one of them at position 2, Recall@3=1/2 and reciprocal rank@3=1/2. This is a constructed arithmetic example, not an observed performance improvement. The API returns unique IDs; the helper assumes ranked results follow that contract.
For queries with no judged relevant documents, recall has a zero denominator. Define a separate no-answer/false-positive evaluation and state how those queries are excluded or treated in aggregate metrics. Do not quietly assign perfect recall. Record how judgments were made and inspect disagreements.
Evaluate candidate recall before fusion quality: a document missing from both candidate lists cannot appear in final results. Tune candidate depth, lexical configuration and model/chunking choices before concluding RRF failed. Use a held-out query set for final comparison; repeatedly tuning and reporting the same few toy queries overstates evidence. No improvement percentage is claimed here.
13. Filtering, pagination, query plans and production tradeoffs
CandidateLimit is not TopK
CandidateLimit is the maximum retrieved from each branch; TopK is the final response size. With a limit of 50 per branch, fusion sees at most 100 unique documents before truncation. Increasing only TopK cannot recover documents excluded from both lists. Wider candidates increase database ranking work, transfer size and fusion memory; evaluate the tradeoff rather than choosing an arbitrary huge limit.
Pagination requires a stable candidate universe
This tutorial intentionally returns a bounded top-K response without offset pagination. Re-running approximate retrieval for each page can change candidates and create gaps or duplicates. For deeper browsing, materialize a permission-scoped ranked result set with an expiry and a search-session token, then page that stable set. Bind tokens to identity, filters, query, model/corpus version and ranking configuration.
Do not apply Skip independently to lexical and vector branches and fuse only those “pages.” RRF ranks are defined relative to each original candidate list; dropping earlier ranks changes the calculation. A database snapshot inside one request is not a cross-request pagination snapshot.
Inspect indexes at the matching boundary
The lexical GIN index supports @@ candidate matching, after which ts_rank sorting still costs work. HNSW uses vector_cosine_ops to match <=> ascending with LIMIT. The planner may reasonably prefer a sequential scan on a four-row table. Use EXPLAIN and appropriate development diagnostics rather than forcing an index merely to obtain a desired screenshot.
These illustrate the query shape, not a second hand-written implementation. The C# service composes parameterized LINQ and adds Category/Code predicates to both branches. Do not paste unresolved $1 values into psql or concatenate user input into SQL. Inspect generated SQL with ToQueryString in a private development environment.
HNSW is approximate; filtering can reduce candidate counts. Consider iterative scans and measured search-breadth settings when needed, but preserve authorization and evaluate recall. For selective structured filters, ordinary indexes, partitions or distinct corpora may be appropriate. The official iterative-scan guidance explains limitations.
Secure and operate the complete system
- Use real identities and document-level authorization before supporting multiple users or tenants.
- Keep API keys, connection strings and raw document/query text out of normal logs.
- Use verified TLS for remote database connections, HTTPS for the public API and private inference networking.
- Separate schema-deployment privileges from runtime read/write permissions.
- Bound payloads, token budgets, candidates, concurrency and result sizes.
- Restrict writer access to prevent corpus poisoning and implement source/version-based idempotency.
- Version model weights, preprocessing and lexical configuration; coordinate corpus rebuilds with releases.
- Track model latency, database latency, branch candidate counts, empty-list frequency and dependency failures separately.
- Evaluate deletion, backup and retention for both source text and derived embeddings.
Local Ollama inference does not make the whole system automatically private or free. Hardware, model licensing, logs and backups still require review. Retrieved text remains untrusted data if you later pass it into a generative model. Search relevance must never grant tool permissions.
For parallel retrieval in a higher-throughput version, use separate DbContext instances or a carefully designed single SQL statement. Separate contexts also raise consistency questions. The Microsoft DbContext guidance explicitly warns against sharing one context across concurrent operations.
14. Troubleshooting and frequently asked questions
| Problem | What to check |
|---|---|
| SearchVector does not exist or remains null | Apply the generated-column migration to the correct database; do not create an ordinary nullable tsvector property by hand. |
| WebSearchToTsQuery throws on the client | Keep the call inside the LINQ expression so Npgsql translates it to SQL. |
| Lexical candidates are empty | Inspect English normalization, stop words, AND/OR behavior and the actual query. Empty lexical evidence is not a database outage. |
| Exact product code retrieves a related but wrong code | Use normalized structured equality or a dedicated lookup. FTS parsing and embeddings do not guarantee literal identity. |
| HNSW is not selected | Check operator class, raw ascending distance ORDER BY, LIMIT, filters and table size. |
| Fewer than CandidateLimit results | Available rows, filters or approximate candidate scanning can reduce the count. |
| RRF scores are all zero | Use floating-point division and 1-based ranks; do not use integer division. |
| One document receives multiple votes from one branch | Deduplicate branch IDs before assigning ranks and summing contributions. |
| A second operation started on this context | Do not run both EF queries concurrently on a scoped DbContext. |
| Embedding dimensions mismatch | Verify model and output configuration; reject the result rather than padding or slicing it. |
| Credentials stop working after recreating a container | The named data volume preserves original database roles/passwords. Environment initialization does not rotate them. |
| Hybrid fails while keyword mode works | Check Ollama model availability, input token limits and the linked HTTP budget. No automatic fallback is configured. |
| Results leak across categories or tenants | Apply the same trusted predicates to each candidate branch; Category alone is not authorization. |
Is ts_rank the same as BM25?
No. It is PostgreSQL’s native ranking function with its own inputs and behavior. This guide does not install a BM25 extension or claim BM25 scoring.
Should I add ts_rank to cosine similarity?
Not without a calibrated score-combination design. Their scales and distributions differ. RRF avoids that direct addition by combining ordinal ranks.
Does RRF guarantee better results?
No. It can combine complementary evidence, but bad candidates, unsuitable language processing and irrelevant agreement can hurt results. Measure with representative judgments.
Why is the RRF constant 60?
It is a widely used starting value from the original method. It smooths rank contributions. It is not the number of neighbors and is not universally optimal.
Can a document appear without a lexical match?
Yes, through the semantic branch. A document in only one list gets one contribution. In hybrid mode, lexical exclusion syntax is not automatically a global vector filter; enforce business exclusions as explicit predicates on both branches.
Does ExactCode force a result?
No. It constrains eligible documents. Branch matching and retrieval still apply. Use an authorized equality endpoint for guaranteed identifier lookup behavior.
What happens if Ollama is unavailable?
Semantic and hybrid modes fail safely. An explicitly requested keyword-only search avoids inference. If you add fallback, mark the degraded mode in the response.
Can this work for multilingual documents?
The vector model can represent multiple languages, but the lexical branch here uses English dictionaries. Choose a language strategy and evaluate each supported language before describing the whole API as multilingual search.
Was the project tested?
It was reviewed against official package/API documentation, but not compiled or executed. The four RRF totals were independently checked in Python; the isolated C# check and Postman scenarios are provided for local verification. No runtime screenshots or benchmark gains are claimed.
15. Conclusion: make both retrieval branches observable
A reliable hybrid search implementation makes its assumptions visible: compatible dense vectors, a declared lexical configuration, bounded candidates, shared filters and an explicit fusion formula. This guide keeps native scores and branch ranks in the response so you can diagnose why a document moved rather than treating the final score as unexplained AI confidence.
Run the implementation locally, verify migrations and negative cases, then build relevance judgments for your own query mix. Compare keyword, semantic and hybrid modes before tuning candidate depth or adding rerankers. Exact identifiers, authorization and durable ingestion remain application responsibilities.
Future supporting tutorials
- Diagnosing missing hybrid candidates with branch-level recall traces.
- Migrating PostgreSQL text-search dictionaries without losing evaluation reproducibility.
- Comparing equal-weight and weighted RRF with a held-out query set.
These are suggestions only; no additional posts were created.
16. References and research notes
- Npgsql full-text mapping and LINQ translations
- Npgsql full-text Rank/Matches APIs
- PostgreSQL text-search controls
- PostgreSQL full-text tables and generated indexes
- PostgreSQL GIN index guidance
- pgvector operators, HNSW and hybrid search
- pgvector-dotnet integration
- Official pgvector-dotnet hybrid example
- Original RRF paper
- Microsoft explanation of RRF
- Npgsql EF provider package
- Pgvector.EntityFrameworkCore package
- EF Core design package
- Ollama embedding endpoint
- Selected embedding model and prefixes
- EF DbContext lifetime and concurrency
Research checked on 4 November 2026. The implementation follows PostgreSQL/Npgsql/pgvector contracts; Azure’s RRF explanation is conceptual background, not a claim that this PostgreSQL API has Azure’s scoring ranges or parallel execution behavior.
Search-intent review focused on .NET developers combining pgvector and PostgreSQL full-text search, diagnosing mismatched score scales, missing exact codes and index/query errors. The DigitalOcean hybrid-query walkthrough and official .NET sample were reviewed as comparison material. This tutorial adds explicit mode comparisons, filter consistency, code-equality boundaries, failure contracts and an isolated fusion check. No keyword volumes, difficulty scores or measured relevance gains are invented.
Comments
Post a Comment