Chapter 6: Introduction to Vector Stores - Why SQL isn't enough

Theoretical Foundations

The fundamental limitation of relational databases like SQL Server, PostgreSQL, or SQLite in the context of modern Artificial Intelligence lies in their reliance on structured data and exact matching. While EF Core excels at managing relational integrity and querying structured entities using LINQ, it operates on a deterministic, binary logic system: a row either matches a WHERE clause exactly, or it does not. This paradigm breaks down when dealing with the nuances of human language, image recognition, and semantic meaning, which exist in a continuous, high-dimensional space.

To bridge the gap between rigid database schemas and fluid human intent, we must introduce Vector Embeddings and Vector Databases.

The Limits of Deterministic Querying

In previous chapters, we utilized LINQ to perform precise lookups. For instance, if we have an Inventory entity, we can query:

var result = context.Products
    .Where(p => p.Name == "Wireless Mouse")
    .ToList();

This works perfectly for structured data. However, AI applications often require semantic search. If a user asks, "What are the best wireless pointing devices?", a standard SQL query using LIKE "%pointing%" is brittle. It relies on keyword matching rather than understanding that "pointing device" is semantically synonymous with "mouse."

SQL databases attempt to solve this with Full-Text Search (FTS), using algorithms like TF-IDF (Term Frequency-Inverse Document Frequency). While better than simple LIKE operators, FTS still struggles with:

1. Polysemy: Words with multiple meanings (e.g., "Apple" the fruit vs. "Apple" the company).
2. Synonymy: Different words with the same meaning (e.g., "car" vs. "automobile").
3. Context: The meaning of a word changes based on surrounding words.

The Analogy: The Library vs. The Semantic Map

Imagine a traditional SQL database as a massive Library organized strictly by the Dewey Decimal System.

• If you want a book about "Dogs," you go to the 636.7 section.
• If you ask the librarian for "Man's best friend," the librarian (the SQL engine) might struggle. They can only look up the literal string "Man's best friend" in the card catalog. If the book is titled "Canine Companions," the librarian cannot find it unless you explicitly know the cross-reference.

Now, imagine a Vector Database as a Semantic Map of a city.

• On this map, concepts are physical locations.
• "Dog" is located in a neighborhood called "Pets."
• "Cat" is nearby.
• "Car" is miles away in the "Transportation" district.
• "Automobile" is right next to "Car."

If you ask for "Man's best friend," the system doesn't look for a specific label. Instead, it drops a pin on the map and looks for the nearest locations. It finds "Dog" and "Canine" because they are spatially close on the map, even if the label "Man's best friend" doesn't exist.

Vector Embeddings: The Language of Machines

To create this "Semantic Map," we must convert unstructured data (text, images, audio) into a format that computers can measure mathematically. This is done via Vector Embeddings.

An embedding is a dense vector—a list of floating-point numbers—representing the semantic meaning of an input. Modern AI models (like OpenAI's text-embedding-ada-002 or open-source models like BERT) ingest raw text and output a vector, typically ranging from 384 to 1536 dimensions (numbers).

How it works conceptually:

1. Input: "The quick brown fox jumps over the lazy dog."
2. Model Processing: The AI model analyzes the syntax and semantic context.
3. Output: [0.021, -0.543, 0.991, ..., 0.004] (a vector of 1536 numbers).

In this high-dimensional space, similar concepts cluster together. The vector for "cat" will be mathematically close to "kitten" but far from "truck."

The Mathematics of Similarity: Cosine Similarity

How do we measure "closeness" on this map? We cannot simply subtract the numbers, as the magnitude (length) of the vector might be misleading. Instead, we use Cosine Similarity.

Cosine similarity measures the cosine of the angle between two vectors.

• Angle 0°: Vectors point in the exact same direction. Similarity = 1.0 (Perfect match).
• Angle 90°: Vectors are orthogonal (unrelated). Similarity = 0.0.
• Angle 180°: Vectors point in opposite directions. Similarity = -1.0 (Opposite meaning).

In the context of AI data access, we don't care about the magnitude of the vector (which might represent the length of the text), only the direction (which represents the meaning).

Why SQL Fails at High-Dimensional Search

Let's look at why a standard SQL engine cannot efficiently perform this search.

In a SQL database, indexing is usually done via B-Trees or Hash Maps. These structures are excellent for retrieving exact values or ranges (e.g., WHERE Age > 20). However, they are linear structures designed for 1D or 2D data.

To find the "nearest neighbor" in a 1536-dimensional space using SQL, you would theoretically have to calculate the distance between your query vector and every single row in the table. This is known as a Brute Force or Flat Scan.

$$ \text{Cost} = O(N \times D) $$ Where \(N\) is the number of records and \(D\) is the dimensionality.

If you have 1 million documents with 1536-dimensional vectors, a single query requires trillions of floating-point operations. SQL databases are not optimized for this type of linear algebra operation; they are optimized for set theory and logic.

Vector Database Architecture

Vector databases (like Pinecone, Weaviate, or Milvus) are specialized engines designed to store and query these high-dimensional vectors efficiently. They utilize algorithms that would be impossible or inefficient to implement purely within EF Core or standard SQL.

1. Approximate Nearest Neighbor (ANN) Search

Unlike "Exact" Nearest Neighbor (which guarantees the perfect result but is slow), ANN algorithms trade a tiny amount of accuracy for massive speed improvements. They pre-organize the data so that searching is \(O(\log N)\) rather than \(O(N)\).

2. Hierarchical Navigable Small World (HNSW) Graphs

This is the most popular indexing algorithm used in modern vector stores.

• Concept: Imagine a multi-layered graph.
• Top Layer: A sparse map of the entire data, allowing for long jumps across the dataset.
• Bottom Layer: A dense map allowing for fine-grained precision.
• Search Process: You enter the top layer, find the closest node, drop down to the next layer, refine your search, and repeat until you reach the bottom layer.

Hold "Ctrl" to enable pan & zoom

A multi-layered map illustrates a hierarchical search where you enter the top layer to find the closest node and progressively drop down to lower layers to refine your search until reaching the bottom.

Integrating Vector Stores with EF Core: The Hybrid Approach

In a production AI application, we rarely replace SQL databases entirely. Instead, we employ a Hybrid Search strategy. This is where the theoretical foundations meet practical architecture.

The Architecture:

1. Structured Data (SQL/EF Core): Stores metadata, relationships, timestamps, and exact identifiers (e.g., ProductId, Price, StockCount).
2. Unstructured Data (Vector DB): Stores the semantic representation of descriptions, reviews, or images.

The Workflow:

1. Ingestion:
   • An Product entity is created in EF Core.
   • The description text is sent to an Embedding Model.
   • The resulting vector is stored in Pinecone/Weaviate, linked by the ProductId.
2. Retrieval (RAG - Retrieval Augmented Generation):
   • User query: "Affordable running shoes."
   • Step A (Vector Search): Convert query to vector. Query the Vector DB for semantically similar items. Get top 10 ProductIds.
   • Step B (Relational Filter): Pass those ProductIds to EF Core.
   • Step C (Refinement): Filter further using structured logic: WHERE Price < 100 AND Stock > 0.

Why this matters for C# Developers: In modern C#, we treat these two systems as complementary data sources. While EF Core manages the DbContext and change tracking for our domain entities, we use specialized SDKs (like Pinecone.NET or Weaviate.Client) to handle the vector operations.

The power of C# lies in its ability to orchestrate this seamlessly. We can use IAsyncEnumerable to stream results from the vector database and hydrate our EF Core entities in parallel, creating a unified view of data that is both structurally sound and semantically intelligent.

The "What If": Scaling and Latency

If we attempt to implement vector search directly within SQL (using user-defined functions or extensions like pgvector), we face specific constraints:

• Indexing Overhead: Maintaining HNSW indexes in a transactional SQL database can lock tables and degrade write performance.
• Compute Isolation: Vector search is CPU-bound and memory-intensive. Running it on the same DB server as your transactional workload can starve resources.

By separating the concerns—using EF Core for transactional integrity and a dedicated Vector Store for semantic retrieval—we ensure that our AI applications remain scalable. The Vector DB handles the "fuzzy" search, while EF Core handles the "precise" state management.

Theoretical Foundations

To build intelligent data access systems with EF Core, we must accept that SQL is a tool for logic, while Vector Stores are tools for meaning.

• SQL (EF Core) answers: "Is this ID equal to 5?"
• Vector DB answers: "Is this concept similar to 'joy'?"

The future of data access is not choosing one over the other, but combining them. We use EF Core to ensure data consistency and manage relationships, and we use Vector Databases to unlock the semantic potential hidden within our unstructured text fields. This hybrid model forms the backbone of modern RAG applications, allowing us to query our data not just by what it is, but by what it means.

Basic Code Example

using System;
using System.Collections.Generic;
using System.Linq;
using System.Numerics;

// Simulating a Vector Database (like Pinecone or Weaviate) in-memory for this "Hello World" example.
// In a real application, you would replace this class with an actual client SDK.
public class VectorStore
{
    // The core data structure: A dictionary mapping an ID to its vector representation.
    // In production, this would be a distributed, optimized index (e.g., HNSW graph).
    private readonly Dictionary<string, float[]> _index = new();

    // Inserts a vector into the store.
    public void Upsert(string id, float[] vector)
    {
        _index[id] = vector;
    }

    // Performs a similarity search using Cosine Similarity.
    // Returns the top 'k' most similar vectors.
    public IEnumerable<(string Id, float Similarity)> Search(float[] queryVector, int topK = 3)
    {
        return _index
            .Select(kvp => (
                Id: kvp.Key,
                Similarity: CosineSimilarity(queryVector, kvp.Value)
            ))
            .OrderByDescending(x => x.Similarity)
            .Take(topK);
    }

    // Helper method to calculate Cosine Similarity (range: -1 to 1, where 1 is identical).
    private static float CosineSimilarity(float[] vectorA, float[] vectorB)
    {
        if (vectorA.Length != vectorB.Length)
            throw new ArgumentException("Vectors must be of the same dimension.");

        float dotProduct = 0f;
        float magnitudeA = 0f;
        float magnitudeB = 0f;

        for (int i = 0; i < vectorA.Length; i++)
        {
            dotProduct += vectorA[i] * vectorB[i];
            magnitudeA += vectorA[i] * vectorA[i];
            magnitudeB += vectorB[i] * vectorB[i];
        }

        magnitudeA = MathF.Sqrt(magnitudeA);
        magnitudeB = MathF.Sqrt(magnitudeB);

        if (magnitudeA == 0 || magnitudeB == 0) return 0;

        return dotProduct / (magnitudeA * magnitudeB);
    }
}

// Simulating an Embedding Service (like OpenAI or Azure AI).
// This converts raw text into a high-dimensional vector.
public static class EmbeddingService
{
    // In a real scenario, this calls a remote API.
    // Here, we simulate it by generating deterministic vectors based on text length and characters
    // to ensure the example is reproducible and demonstrates the concept.
    public static float[] GenerateEmbedding(string text)
    {
        const int dimensions = 128; // Standard embedding size (e.g., OpenAI's text-embedding-ada-002 is 1536)
        var vector = new float[dimensions];

        // Simple deterministic hash-like generation for demonstration
        for (int i = 0; i < dimensions; i++)
        {
            // Modulate based on character code and index to create distinct patterns
            vector[i] = (text.Length % 10) + (i % 5) + (text.GetHashCode() % 100) / 1000f;
        }

        return vector;
    }
}

class Program
{
    static void Main()
    {
        // 1. Setup: Initialize our vector store.
        var vectorDb = new VectorStore();

        // 2. Data: Define a list of documents (unstructured data) we want to search through.
        var documents = new List<(string Id, string Content)>
        {
            ("doc_1", "The quick brown fox jumps over the lazy dog."),
            ("doc_2", "A quick brown dog races past a sleeping fox."),
            ("doc_3", "The weather today is sunny and warm."),
            ("doc_4", "I love programming in C# and .NET."),
            ("doc_5", "Machine learning involves training models on data.")
        };

        Console.WriteLine("--- Indexing Documents ---");

        // 3. Indexing: Convert text to vectors and store them.
        foreach (var doc in documents)
        {
            var embedding = EmbeddingService.GenerateEmbedding(doc.Content);
            vectorDb.Upsert(doc.Id, embedding);
            Console.WriteLine($"Indexed {doc.Id}: \"{doc.Content}\"");
        }

        Console.WriteLine("\n--- Performing Search ---");

        // 4. Querying: Define a search query.
        string query = "fast brown animal jumps";
        Console.WriteLine($"Query: \"{query}\"");

        // 5. Search: Convert query to vector and perform similarity search.
        var queryVector = EmbeddingService.GenerateEmbedding(query);
        var results = vectorDb.Search(queryVector, topK: 2);

        // 6. Results: Display the matches.
        Console.WriteLine("\nTop Matches:");
        foreach (var result in results)
        {
            // Find the original text for display purposes
            var originalDoc = documents.First(d => d.Id == result.Id);
            Console.WriteLine($"- ID: {result.Id} | Similarity: {result.Similarity:F4}");
            Console.WriteLine($"  Content: \"{originalDoc.Content}\"");
        }
    }
}

Line-by-Line Explanation

1. The VectorStore Class (The Database)

This class simulates the behavior of a specialized vector database like Pinecone or Weaviate.

• private readonly Dictionary<string, float[]> _index:

  • We use a Dictionary to store the data. The Key is a string ID (e.g., "doc_1"), and the Value is a float[] array representing the high-dimensional vector.
  • Architectural Note: In a real production system, this dictionary is replaced by a specialized index structure (like an HNSW graph or IVF index) that allows for Approximate Nearest Neighbor (ANN) search, which is much faster than scanning every vector.

• public void Upsert(string id, float[] vector):

  • This method handles data ingestion. "Upsert" means "Update or Insert." If the ID exists, we overwrite it; if not, we add it.
  • This is the equivalent of INSERT or UPDATE in SQL.

• public IEnumerable<(string Id, float Similarity)> Search(...):

  • This is the core retrieval mechanism. It takes a query vector and returns a list of tuples containing the document ID and the calculated similarity score.
  • OrderByDescending(x => x.Similarity): We sort by score to find the "best" matches. In vector search, higher similarity usually means better relevance.
  • Take(topK): We limit the results to the top K matches (e.g., top 3).

• private static float CosineSimilarity(...):

  • This implements the mathematical formula: \(\text{Cosine Similarity} = \frac{A \cdot B}{\|A\| \|B\|}\).
  • The Loop: We iterate through the dimensions of the vectors to calculate the Dot Product (sum of \(A_i \times B_i\)) and the Magnitudes (sum of squares, square root).
  • Why Cosine? It measures the angle between vectors, ignoring their magnitude. This is crucial for text search because it focuses on the semantic meaning rather than the document length.

2. The EmbeddingService Class (The Encoder)

This simulates an AI model (like OpenAI's text-embedding-ada-002) that converts unstructured text into structured numerical vectors.

• public static float[] GenerateEmbedding(string text):
  • In a real app, this would make an HTTP request to an AI endpoint.
  • For this "Hello World" example, we generate a deterministic vector based on the text's properties (length, hash code). This ensures that the same text always produces the same vector, allowing the search to work without needing an actual API key.

3. The Program Class (The Execution Flow)

This demonstrates the lifecycle of a vector search operation.

• Step 1: Initialization:

  • var vectorDb = new VectorStore(); creates our in-memory database.

• Step 2: Data Preparation:

  • We define a list of documents. Notice these are unstructured strings with no fixed schema.

• Step 3: Indexing (The "Write" Phase):

  • We loop through the documents.
  • EmbeddingService.GenerateEmbedding(doc.Content) converts the text into a numeric array.
  • vectorDb.Upsert(...) stores this vector in the database.
  • Crucial Concept: We are not storing the text in the vector store for the search logic (though we might store it as metadata). The search happens purely on the numerical vectors.

• Step 4: Querying (The "Read" Phase):

  • string query = "fast brown animal jumps";
  • We convert this query into a vector using the same GenerateEmbedding method. Important: The query must be embedded using the exact same model/dimensions as the documents.

• Step 5: Retrieval:

  • vectorDb.Search(queryVector, topK: 2) performs the mathematical comparison.
  • It compares the query vector against all stored document vectors and returns the ones with the highest cosine similarity.

• Step 6: Output:

  • The results show that "doc_1" and "doc_2" have the highest scores because they share words like "quick," "brown," and "fox/dog," which semantically overlap with "fast," "brown," and "animal."

Visualizing the Vector Space

The following diagram illustrates how text is transformed into a multi-dimensional space. Even though we cannot visualize 128 dimensions, we can conceptually map them to a 2D plane to understand how proximity equals relevance.

Hold "Ctrl" to enable pan & zoom

This diagram illustrates how textual data is transformed into a multi-dimensional vector space, where proximity between points in the 2D projection visually represents semantic relevance.

Common Pitfalls

1. Dimensionality Mismatch

• The Mistake: Embedding your documents using one model (e.g., OpenAI text-embedding-ada-002 which outputs 1536 dimensions) and your query using a different model (e.g., a local model outputting 384 dimensions).
• The Consequence: The CosineSimilarity calculation will throw an ArgumentException because the vectors cannot be compared mathematically if their lengths differ.
• The Fix: Ensure the exact same embedding model is used for indexing and querying.

2. The "Cold Start" Problem (Empty Index)

• The Mistake: Running a search query against a vector store that has no data indexed yet.
• The Consequence: The search returns an empty result set, which might break UI logic expecting data.
• The Fix: Always check if the index is populated or handle empty results gracefully in your application code.

3. Normalization Neglect

• The Mistake: Using Euclidean Distance (L2 norm) without normalizing vectors when dealing with text.
• The Consequence: Longer documents (which have larger vector magnitudes) might be ranked higher than shorter, more relevant documents simply because they are "bigger," not because they are more similar.
• The Fix: Use Cosine Similarity (which implicitly handles magnitude by normalizing vectors to unit length) or ensure vectors are normalized before storing them if using Euclidean distance.

4. Mixing Data Types

• The Mistake: Storing metadata (like dates or categories) inside the vector array itself.
• The Consequence: This corrupts the semantic meaning of the vector. The embedding model is trained to understand linguistic meaning, not numerical metadata.
• The Fix: Store vectors purely for semantic content. Use "Metadata Filtering" (a feature of vector databases) to filter by date or category during the search query, keeping the vector search pure.

The chapter continues with advanced code, exercises and solutions with analysis, you can find them on the ebook on Leanpub.com or Amazon

---

Loading knowledge check...

---

---

Code License: All code examples are released under the MIT License. Github repo.

Content Copyright: Copyright © 2026 Edgar Milvus | Privacy & Cookie Policy. All rights reserved.

All textual explanations, original diagrams, and illustrations are the intellectual property of the author. To support the maintenance of this site via AdSense, please read this content exclusively online. Copying, redistribution, or reproduction is strictly prohibited.