Including Vector Databases in ASP.NET Core Search, such as Pinecone

Users now expect contextual retrieval, fuzzy relevance, and semantic search in addition to keyword matching. Vector databases offer effective similarity search and store vector embeddings (Pinecone, Milvus, Weaviate, etc.). They enable you to integrate semantic search to ASP.NET Core apps with low latency and great relevance when combined with OpenAI (or other embedding models).

With practical code examples, architecture advice, and diagrams, this post describes how to create, construct, and run a vector-search workflow using ASP.NET Core, OpenAI embeddings, and Pinecone. The information is appropriate for both novices and specialists, and the language is straightforward Indian English.

High-Level Workflow

User Query (Angular)
       ↓
ASP.NET Core API → Create embedding (OpenAI)
       ↓
Query Pinecone (vector DB) —> Pinecone returns nearest vector IDs + scores
       ↓
Fetch full metadata from SQL Server using IDs
       ↓
Aggregate, rank and return results to Angular
       ↓
User sees semantic search results

Flowchart

+--------------------+
|   User submits     |
|   search query     |
+---------+----------+
          |
          v
+---------+----------+
| API receives query |
+---------+----------+
          |
          v
+---------+----------+
| Create embedding   |
| (OpenAI/Model)     |
+---------+----------+
          |
          v
+---------+----------+
| Query Pinecone     |
+---------+----------+
          |
          v
+---------+----------+
| Pinecone returns   |
| top N vector IDs    |
+---------+----------+
          |
          v
+---------+----------+
| Fetch rows from DB |
+---------+----------+
          |
          v
+---------+----------+
| Return results to  |
| frontend (Angular) |
+--------------------+

Architecture Diagram (Visio-style)

+----------------------+       +---------------------+       +-------------------+
|   Angular Frontend   | <---> |  ASP.NET Core API   | <---> | OpenAI Embedding  |
| (Search UI, Filters) |       | (Search Controller) |       |  Service / Model  |
+----------------------+       +---------------------+       +-------------------+
                                         |
                                         |
                                         v
                                +------------------+
                                |   Pinecone       |
                                |   Vector DB      |
                                +------------------+
                                         |
                                         v
                                +------------------+
                                |   SQL Server     |
                                | (metadata, source|
                                |  documents)      |
                                +------------------+

ER Diagram (Minimal mapping)

+----------------------+         +----------------------+
|    Documents         | 1 --- * |  VectorIndex         |
+----------------------+         +----------------------+
| DocumentId (PK)      |         | VectorId (PK)        |
| Title                |         | DocumentId (FK)      |
| Content              |         | Namespace            |
| Language             |         | EmbeddingDimension   |
| CreatedOn            |         | PineconeId           |
+----------------------+         | Metadata (JSON)      |
                                 | InsertedOn           |
                                 +----------------------+

Documents stores canonical text, titles, URLs, etc.
VectorIndex tracks which vector belongs to which document and stores Pinecone ID / namespace / metadata for joins.

Sequence Diagram (smaller header)

User -> Angular: enters query
Angular -> API: /api/search?q=...
API -> OpenAI: POST embeddings (query)
OpenAI -> API: embedding vector
API -> Pinecone: query top K vectors
Pinecone -> API: vector IDs + scores
API -> SQL Server: SELECT * FROM Documents WHERE DocumentId IN (...)
SQL Server -> API: document rows
API -> Angular: aggregated search results
Angular -> User: display results

Implementation — Practical Guide

Prerequisites

ASP.NET Core 7/8 project
OpenAI API key (or other embedding service)
Pinecone account + API key + environment (or any vector DB)
SQL Server for metadata
Angular front-end to call API

Note: Use secure secrets management (Azure Key Vault, GitHub Secrets) — never hardcode keys.

Data Model (SQL Server)

Example DDL

CREATE TABLE Documents (
  DocumentId UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
  Title NVARCHAR(500),
  Content NVARCHAR(MAX),
  Url NVARCHAR(1000),
  Language NVARCHAR(20),
  CreatedOn DATETIME2 DEFAULT SYSUTCDATETIME()
);

CREATE TABLE VectorIndex (
  VectorId UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
  DocumentId UNIQUEIDENTIFIER NOT NULL,
  PineconeId NVARCHAR(200) NOT NULL,
  Namespace NVARCHAR(200) DEFAULT 'default',
  EmbeddingDim INT,
  Metadata NVARCHAR(MAX),
  InsertedOn DATETIME2 DEFAULT SYSUTCDATETIME(),
  FOREIGN KEY (DocumentId) REFERENCES Documents(DocumentId)
);

Embedding & Pinecone Integration (ASP.NET Core)

Below is a simple implementation using HttpClient. In production you may want to use an official SDK if available.

1. Configure services (Startup / Program.cs)

builder.Services.AddHttpClient("OpenAI", c =>
{
    c.BaseAddress = new Uri("https://api.openai.com/");
    c.DefaultRequestHeaders.Authorization =
        new AuthenticationHeaderValue("Bearer", configuration["OPENAI_API_KEY"]);
});

builder.Services.AddHttpClient("Pinecone", c =>
{
    c.BaseAddress = new Uri(configuration["PINECONE_BASE_URL"]); // e.g. https://your-project.svc.pinecone.io
    c.DefaultRequestHeaders.Add("Api-Key", configuration["PINECONE_API_KEY"]);
});

builder.Services.AddScoped<IEmbeddingService, OpenAiEmbeddingService>();
builder.Services.AddScoped<IVectorService, PineconeVectorService>();

2. Embedding service (OpenAI)

public class OpenAiEmbeddingService : IEmbeddingService
{
    private readonly HttpClient _client;
    private readonly string _model = "text-embedding-3-small"; // choose model

    public OpenAiEmbeddingService(IHttpClientFactory http)
    {
        _client = http.CreateClient("OpenAI");
    }

    public async Task<float[]> GetEmbeddingAsync(string text)
    {
        var payload = new
        {
            model = _model,
            input = text
        };

        var resp = await _client.PostAsJsonAsync("v1/embeddings", payload);
        resp.EnsureSuccessStatusCode();

        var body = await resp.Content.ReadFromJsonAsync<JsonDocument>();
        var arr = body.RootElement.GetProperty("data")[0].GetProperty("embedding").EnumerateArray()
                  .Select(e => e.GetSingle()).ToArray();
        return arr;
    }
}

3. Pinecone vector service (indexing & query)

public class PineconeVectorService : IVectorService
{
    private readonly HttpClient _client;
    private readonly string _indexName;

    public PineconeVectorService(IHttpClientFactory http, IConfiguration config)
    {
        _client = http.CreateClient("Pinecone");
        _indexName = config["PINECONE_INDEX"];
    }

    public async Task UpsertAsync(string id, float[] vector, object metadata)
    {
        var body = new
        {
            vectors = new[] {
                new {
                    id = id,
                    values = vector,
                    metadata = metadata
                }
            }
        };

        var resp = await _client.PostAsJsonAsync($"/vectors/upsert", body);
        resp.EnsureSuccessStatusCode();
    }

    public async Task<IEnumerable<(string id, float score)>> QueryAsync(float[] vector, int topK = 5)
    {
        var body = new {
            vector = vector,
            topK = topK,
            includeValues = false,
            includeMetadata = false
        };

        var resp = await _client.PostAsJsonAsync($"/query", body);
        resp.EnsureSuccessStatusCode();

        var result = await resp.Content.ReadFromJsonAsync<JsonDocument>();
        var matches = result.RootElement.GetProperty("matches").EnumerateArray();

        return matches.Select(m => (
            id: m.GetProperty("id").GetString(),
            score: m.GetProperty("score").GetSingle()
        )).ToList();
    }
}

Pinecone REST endpoints depend on your Pinecone project URL and API version. Some deployments require /indexes/{indexName}/query and /indexes/{indexName}/vectors/upsert. Adjust paths accordingly.

Example Controller: Index and Search

[ApiController]
[Route("api/search")]
public class SearchController : ControllerBase
{
    private readonly IEmbeddingService _embed;
    private readonly IVectorService _vectors;
    private readonly ApplicationDbContext _db;

    public SearchController(IEmbeddingService embed, IVectorService vectors, ApplicationDbContext db)
    {
        _embed = embed;
        _vectors = vectors;
        _db = db;
    }

    [HttpPost("index")]
    public async Task<IActionResult> Index([FromBody] DocumentDto dto)
    {
        // Save to DB
        var doc = new Document { Title = dto.Title, Content = dto.Content, Url = dto.Url };
        _db.Documents.Add(doc);
        await _db.SaveChangesAsync();

        // Create embedding
        var vector = await _embed.GetEmbeddingAsync(dto.Content);

        // Upsert to Pinecone with id = doc.DocumentId
        var meta = new { title = dto.Title, url = dto.Url };
        await _vectors.UpsertAsync(doc.DocumentId.ToString(), vector, meta);

        // Save vector index record
        _db.VectorIndex.Add(new VectorIndex {
          DocumentId = doc.DocumentId, PineconeId = doc.DocumentId.ToString(), EmbeddingDim = vector.Length,
          Metadata = JsonSerializer.Serialize(meta)
        });
        await _db.SaveChangesAsync();

        return Ok();
    }

    [HttpGet]
    public async Task<IActionResult> Search([FromQuery] string q, [FromQuery] int k = 5)
    {
        var v = await _embed.GetEmbeddingAsync(q);
        var matches = await _vectors.QueryAsync(v, k);

        var ids = matches.Select(m => Guid.Parse(m.id)).ToArray();
        var docs = await _db.Documents.Where(d => ids.Contains(d.DocumentId)).ToListAsync();

        // Optionally preserve order by score
        var ordered = ids.Select(id => docs.First(d => d.DocumentId == id));
        return Ok(ordered);
    }
}

Angular Frontend (simple)

Service

search(q: string) {
  return this.http.get<any[]>(`/api/search?q=${encodeURIComponent(q)}`);
}

Component

onSearch(text: string) {
  this.searchService.search(text).subscribe(res => {
    this.results = res;
  });
}

Operational Considerations & Best Practices

Dimension and model choice
- Choose an embedding model that balances cost, latency, and quality. Common OpenAI embedding models: text-embedding-3-small / text-embedding-3-large.
- Confirm Pinecone index dimension equals embedding size.
Batching
- For large indexing jobs, batch embeddings and upserts to reduce API calls and improve throughput.
Namespace & Metadata
- Use Pinecone namespaces to separate environments (dev/prod) or tenants.
- Store searchable metadata (title, url, type) for filtering and quick display.
Filtering
- Use metadata filters in Pinecone queries to restrict search to a subset (category, language, tenant).
Consistency
- Keep SQL Server as source of truth. Rebuild or reconcile vectors periodically (scheduled job) in case of drift.
Cost & Rate Limits
- Monitor usage of embedding API and Pinecone. Cache embeddings for repeated queries or reuse document embeddings.
Security
- Secure API keys using Key Vault / secret manager.
- Never expose Pinecone or OpenAI keys to frontend.
Latency
- Embed creation adds latency. For interactive search, consider caching popular query embeddings or precomputing suggestions.
Relevance Tuning
- Adjust topK, distance metric (cosine vs dot product) and post-filter re-ranking (BM25 hybrid rerank using keywords) for best UX.
Logging & Monitoring
- Log query latency, failure rates, top-K clickthrough for continuous improvement.

Advanced Patterns

Hybrid search (Vector + BM25): run a keyword search in SQL/ElasticSearch to narrow candidates, then re-rank by vector similarity. Good for precision and filter controls.
Re-ranking with a cross-encoder: for top-N candidates, use a heavier model to compute final relevance.
Semantic chunking: split long documents into overlapping chunks and store chunk-level vectors for fine-grained retrieval. Keep mapping to parent document.
Pinecone + embeddings cache: use Redis for caching top queries or precomputed embeddings.
Multi-lingual: detect language and use appropriate embeddings or translate before embedding.

Sample Reconciliation Job (pseudo)

Run nightly job
- Query DB for new/updated documents since last run
- Create embeddings in batches
- Upsert to Pinecone
- Update VectorIndex table with timestamp

This ensures index remains consistent.

Conclusion

Integrating Pinecone (or any vector DB) with ASP.NET Core and OpenAI embeddings offers powerful semantic search capabilities. Keep SQL Server as canonical storage for documents and metadata, while the vector DB handles fast nearest-neighbour search.

Key takeaways

Use embeddings to convert text into vectors.
Store vectors in Pinecone and metadata in SQL Server.
Query flow: embed query → Pinecone query → fetch metadata from SQL → present to user.
Apply batching, namespaces, caching, and monitoring to make it production-ready.

Best and Most Recommended ASP.NET Core 10.0 Hosting

Fortunately, there are a number of dependable and recommended web hosts available that can help you gain control of your website’s performance and improve your ASP.NET Core 10.0 web ranking. HostForLIFE.eu is highly recommended. In Europe, HostForLIFE.eu is the most popular option for first-time web hosts searching for an affordable plan. Their standard price begins at only €3.49 per month. Customers are permitted to choose quarterly and annual plans based on their preferences. HostForLIFE.eu guarantees “No Hidden Fees” and an industry-leading ’30 Days Cash Back’ policy. Customers who terminate their service within the first thirty days are eligible for a full refund.

By providing reseller hosting accounts, HostForLIFE.eu also gives its consumers the chance to generate income. You can purchase their reseller hosting account, host an unlimited number of websites on it, and even sell some of your hosting space to others. This is one of the most effective methods for making money online. They will take care of all your customers’ hosting needs, so you do not need to fret about hosting-related matters.