SqlVector Deep Dive: Bringing AI Vector Search to EF Core and Dapper

Introduction

Vector (embedding) support in databases is no longer a fringe feature — it’s becoming essential for AI, semantic search, recommendation engines, and more. With the introduction of the vector data type in Azure SQL / SQL Server, and first-class support in .NET via SqlVector, you can now store, query, and operate on embeddings directly inside your relational database.

In this post, I’ll walk you through how to use SqlVector with EF Core 9, EF Core 10, and Dapper. Along the way, I’ll provide deeper explanations of embeddings, similarity metrics, and vector indexing. At the end, there’s a workshop section for beginners so you can try it yourself.

---

Background: What Is a Vector Data Type & Why It Matters

Before diving into code, let’s clarify what we mean by “vector” here and why Microsoft added support for it:

• A vector in this context is just an ordered array of floats (e.g. float[1536]) that encodes meaningful features of data — e.g. the semantic embedding of a document, a user profile, an image, etc.
• The process of turning raw data (text, images) into those numbers is called vectorization or embedding generation.
• The power of embeddings is that “semantically similar” items tend to have vector representations that are close in vector space (under cosine distance, dot product, etc.).
• SQL Server / Azure SQL now supports a built-in VECTOR(n) type, which stores vector data in a binary-optimized format, while exposing functions like VECTOR_DISTANCE to compute similarity. Microsoft Learn+1
• Internally, Microsoft supports both exact nearest neighbor search (compute distance to every vector) and, in SQL Server 2025, approximate vector search (DiskANN) for scale. Microsoft Learn+2GitHub+2
• The advantage: you no longer need a separate vector database (like Milvus or Pinecone). You can keep your data, embeddings, and relational models all in one SQL system and query them together. Microsoft for Developers+2Microsoft for Developers+2

Important constraints / details:

• The maximum supported vector dimension is 1998 for a VECTOR(n) column. Microsoft for Developers+2GitHub+2
• You can cast JSON arrays to vectors in T-SQL, or vice versa, for flexibility. Microsoft Learn
• Approximate search (via VECTOR_SEARCH) is in preview and requires SQL Server 2025 or later. GitHub+2Microsoft Learn+2

---

Using SqlVector in EF Core 9 (via extension)

EF Core 9 predates built-in vector support, so you rely on an external plugin to enable vector operations with Azure SQL / SQL Server. That plugin is EFCore.SqlServer.VectorSearch. GitHub+2NuGet+2

Here’s how to set it up:

1. Install the NuGet package

dotnet add package EFCore.SqlServer.VectorSearch

2. Configure your DbContext

builder.Services.AddDbContext<MyContext>(options =>
    options.UseSqlServer(connString, sqlOptions => sqlOptions.UseVectorSearch()));

builder.Services.AddDbContext<MyContext>(options =>
    options.UseSqlServer(connString, sqlOptions => sqlOptions.UseVectorSearch()));

3. Model the vector property in your entity

public class MyEntity
{
    public int Id { get; set; }
    public float[] Embedding { get; set; }
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<MyEntity>()
        .Property(e => e.Embedding)
        .HasColumnType("vector(1536)");
}

4. Query using vector distance

var queryVec = new float[1536]; // your embedding
var top = context.MyEntitys
    .OrderBy(e => EF.Functions.VectorDistance("cosine", e.Embedding, queryVec))
    .Take(5)
    .ToList();

Under the covers, the plugin maps the float[] field to SqlVector<float> or a similar internal representation, handles parameterization, and translates VectorDistance into T-SQL. (Note: vector plugin is intended for EF 8 / EF 9; for EF ≥ 10 it’s redundant.) GitHub+2GitHub+2

Pros and caveats:

• It gives you LINQ-friendly syntax for vectors in EF Core 9.
• It’s a prerelease / community plugin, so the APIs may evolve. NuGet+1
• One common issue: you may see errors like “At least one of the arguments to EF.Functions.VectorDistance must be a vector” — that’s a clue your mapping or type isn’t properly configured. GitHub
• Also, VECTOR_SEARCH (approximate search) was not supported under this plugin at time of writing. GitHub+1

---

Using SqlVector in EF Core 10 (native support)

EF Core 10 brings first-class support for vectors, so you no longer need an extension library. Microsoft’s SQL Server provider includes vector search capabilities built in. GitHub+2Microsoft for Developers+2

Here’s the flow:

Defining your entity

You can annotate:

using System.ComponentModel.DataAnnotations.Schema;  // for [Column]

public class Article
{
    public int Id { get; set; }

    [Column(TypeName = "vector(1536)")]
    public SqlVector<float> Embedding { get; set; }

    public string Text { get; set; }
}

Or use Fluent API:

modelBuilder.Entity<Article>()
    .Property(a => a.Embedding)
    .HasColumnType("vector(1536)");

Inserting data

Generate embeddings via your embedding model (GPT, OpenAI, HuggingFace, etc.), then wrap them:

float[] emb = await embeddingClient.GenerateEmbeddingAsync("Some text");
var article = new Article {
    Text = "Hello, vector world",
    Embedding = new SqlVector<float>(emb)
};
context.Articles.Add(article);
await context.SaveChangesAsync();

Querying with similarity

float[] queryEmb = await embeddingClient.GenerateEmbeddingAsync("What is AI?");
var queryVec = new SqlVector<float>(queryEmb);

var top = await context.Articles
    .OrderBy(a => EF.Functions.VectorDistance("cosine", a.Embedding, queryVec))
    .Take(5)
    .ToListAsync();

EF Core handles translating VectorDistance into T-SQL’s VECTOR_DISTANCE call.

You can also mix vector distance with traditional predicates:

var filtered = await context.Articles
    .Where(a => a.Text.Contains("machine learning"))
    .OrderBy(a => EF.Functions.VectorDistance("cosine", a.Embedding, queryVec))
    .Take(3)
    .ToListAsync();

Because vector support is built-in, you no longer need UseVectorSearch() or third-party packages.

---

Using SqlVector in Dapper

Dapper does not “know” about SqlVector out of the box. However, since it builds on top of SqlClient, you can create a type handler that converts between your in-application vector format (e.g. float[]) and SqlVector<float>.

Sample TypeHandler

public class VectorTypeHandler : SqlMapper.TypeHandler<float[]>
{
    public override float[] Parse(object value)
    {
        // `value` is likely `SqlVector<float>`, so extract the memory
        return ((SqlVector<float>)value).Memory.ToArray();
    }

    public override void SetValue(IDbDataParameter parameter, float[]? value)
    {
        if (value != null)
        {
            parameter.Value = new SqlVector<float>(value);
            ((SqlParameter)parameter).SqlDbType = SqlDbType.Vector;  // use vector type enumeration
        }
        else
        {
            parameter.Value = DBNull.Value;
        }
    }
}

Registering and using

SqlMapper.AddTypeHandler(new VectorTypeHandler());

// Query
string sql = "SELECT Id, Text, Embedding FROM Articles WHERE ...";
var articles = connection.Query<Article>(sql);

Dapper will use your handler for the Embedding column. On insertion, set the parameter to float[], and your handler will wrap it in SqlVector<float> properly.

Be careful with nulls, dimensions, and making sure the SqlDbType is set to Vector so SQL Server knows what you mean.

---

Real-World Use Cases & Examples

Here are scenarios where native vector support shines:

1. Semantic search / Q&A
   Store document embeddings in SQL. For a user query, generate its embedding and find nearest neighbors via VECTOR_DISTANCE.
2. Recommendation engines
   Represent users or items as embeddings, then rank similar items by vector distance.
3. Hybrid search (keyword + vector)
   Use full-text search (e.g. FREETEXTTABLE or CONTAINS) combined with vector distance ranking (e.g. via Reciprocal Rank Fusion). Azure Samples repo shows combining BM25 + vector ranking. GitHub+1
4. Large-scale search / ANN
   For large datasets, use approximate search (DiskANN) via VECTOR_SEARCH (requires SQL Server 2025). This enables fast retrieval at scale. GitHub+2Microsoft for Developers+2
5. Embedding within relational workflows
   Because embedding columns live in your SQL schema, you can join or filter them with normal relational data (e.g. “only articles in category X, then rank by similarity”).

Example from Microsoft blog
They show how to classify documents (invoices vs. shipments) by generating embeddings, inserting them into a VECTOR(1536) column, and querying the top vector matches to decide class membership. Microsoft for Developers
They also illustrate inserting vectors via casting JSON arrays to VECTOR(...) and executing vector queries in T-SQL. Microsoft for Developers

---

Workshop for Beginners: Step-by-Step Guide

Here’s a more beginner-friendly walkthrough that explains concepts as well as code so you can try this end-to-end.

What you’ll build

A tiny app to:

1. Create a SQL table with a vector column
2. Generate embeddings from text
3. Insert data
4. Query by similarity
5. Try hybrid filtering + similarity

Key Concepts You Should Know

• Embedding dimension (e.g. 1536)
  Models like OpenAI’s embedding models return vectors of fixed size (for example, 1536). Your SQL VECTOR(n) must match that.
• Distance metric
  Common ones: cosine distance, Euclidean, dot product. In practice, cosine is widely used.
• Exact vs. Approximate search
  Exact search computes distance to every vector (good for small/medium datasets). Approximate (via DiskANN) sacrifices a little accuracy for speed on large datasets.
• Normalization
  Some embedding models return normalized vectors (length = 1); others do not, so using VECTOR_NORMALIZE, VECTOR_NORM, or ensuring normalization helps ensure consistent distance semantics.
• Indexing & performance
  SQL Server uses internal optimizations for vector storage. For approximate search, DiskANN provides index structures.

Step-by-step setup

1. Provision environment
   • Use Azure SQL (must support vector type) or SQL Server 2025.
   • Clone the Microsoft sample repo: Azure SQL DB Vector Search samples GitHub
2. Create table with vector column

CREATE TABLE Documents (
  Id INT IDENTITY PRIMARY KEY,
  Text NVARCHAR(MAX),
  Embedding VECTOR(1536)
);

3. Generate embeddings

Use whatever embedding service or library you like. Example (pseudo):

csharp
float[] emb = await embeddingService.GetEmbeddingAsync("Hello world");

4. Insert with EF Core 10

var doc = new Document {
  Text = "Hello world",
  Embedding = new SqlVector<float>(emb)
};
context.Documents.Add(doc);
await context.SaveChangesAsync();

csharp

var doc = new Document {
Text = “Hello world”,
Embedding = new SqlVector(emb)
};
context.Documents.Add(doc);
await context.SaveChangesAsync();

5. Run a similarity query

float[] queryEmb = await embeddingService.GetEmbeddingAsync("Machine learning intro");
var qVec = new SqlVector<float>(queryEmb);

var topDocs = await context.Documents
  .OrderBy(d => EF.Functions.VectorDistance("cosine", d.Embedding, qVec))
  .Take(5)
  .ToListAsync();

6. Hybrid filter + vector

var filtered = await context.Documents
  .Where(d => d.Text.Contains("AI"))
  .OrderBy(d => EF.Functions.VectorDistance("cosine", d.Embedding, qVec))
  .Take(3)
  .ToListAsync();

7. Experiment with approximate search (if on SQL Server 2025)
Use T-SQL VECTOR_SEARCH(...) in place of exhaustive ORDER BY VECTOR_DISTANCE, especially for large tables. See Microsoft sample repo for example scripts. GitHub

Measure performance & scale
Insert thousands or millions of rows. Time your queries. Observe how exact search scales linearly. Then try approximate search and see the speed/accuracy tradeoff.

---

Summary & Final Thoughts

• SqlVector in .NET + vector support in SQL Server enable you to build semantic search, recommendation, and AI features without introducing a separate vector database.
• In EF Core 9, use EFCore.SqlServer.VectorSearch plugin to bridge the gap.
• In EF Core 10, vector is built in — you just map SqlVector<float> and query via EF.Functions.VectorDistance.
• With Dapper, use a custom TypeHandler to map between float[] and SqlVector<float>.
• For small to mid datasets, exact nearest neighbor via VECTOR_DISTANCE may suffice. For large datasets, use approximate search / DiskANN (SQL Server 2025) via VECTOR_SEARCH.
• Hybrid search (keyword + vector) is powerful: combine full-text ranking and vector distance to get more relevant results.
• The Microsoft sample repository (Azure-SQL DB Vector Search) contains working examples in EF, Dapper, hybrid logic, and approximate search. GitHub

If you like, I can also generate the code snippets in your preferred language (C#, VB, etc.), or produce a slide deck summary. Do you want me to format this in HTML or Markdown for your blog next?

---

References & Further Reading (Microsoft + GitHub)

Dev blog: Vector Search with Azure SQL, Semantic Kernel & EF Core Microsoft for Developers

SQL Server vector support documentation: Vectors in SQL Server / Vector Search Microsoft Learn

Microsoft blog: Public Preview of Native Vector Support in Azure SQL Microsoft for Developers

Microsoft blog: SQL Server Native Vector Search for .NET Developers Microsoft for Developers

Azure Samples: azure-sql-db-vector-search repo and sample code GitHub

EFCore.SqlServer.VectorSearch plugin (GitHub) GitHub+2GitHub+2