EF Core 11: Vector Search Features with SQL Server

In this article we will see the EF Core 11 Vector Search Feature.

Please note that, the following items come from EF Core 11. Only the first two are used directly in this application.

  • Vector properties are not loaded by default. A SqlVector<T> property is no longer included in the SELECT list when you materialize an entity. A 1536-dimension float vector is roughly 6 KB per row, and most queries never need it back. In this app the vector is only used inside ORDER BY, so nothing extra is transferred.
  • Exact vector distance. EF.Functions.VectorDistance(metric, vector1, vector2), introduced in EF Core 10, keeps working and maps to the T-SQL VECTOR_DISTANCE function. This is what the application uses.
  • Approximate vector search (experimental). EF Core 11 adds translation for the SQL Server VECTOR_SEARCH function, which runs against a DiskANN vector index. It is marked experimental on the SQL Server side, so the API may change. The application in this article does not use it.
  • Full-text search functions and FullJoin. EF Core 11 adds full-text search table-valued functions and translation for the new .NET 11 FullJoin operator.
  • JSON path check. EF.Functions.JsonPathExists() maps to SQL Server's JSON_PATH_EXISTS.
  • Leaner SQL. Redundant joins are pruned from split queries that include to-one navigations.

What is implemented in the code?

The application lets a user describe shipments in plain English and returns matching rows from AdventureWorks2025. It works in two phases.

One-time setup. AdventureWorks has no vector column, so a button on the page adds a vector(1536) column named Embedding to Production.Product. It then builds a short description for each product (name, subcategory, category, color), sends the descriptions to an Azure OpenAI embedding deployment, and saves the returned vectors.

Searching. When the user submits a prompt:

  1. An Azure OpenAI chat deployment converts the prompt into structured filters: product text, region, country code, customer name, date range, date field (ship, order or due date) and price range.
  2. If the prompt mentions a product, that text is embedded with the same embedding deployment.
  3. EF Core runs VectorDistance with the cosine metric against Product.Embedding and returns the 10 closest products.
  4. Those product IDs plus the remaining filters are applied to the order tables, and the shipment details are returned: order number, product, quantity, prices, order/ship/due dates, customer, ship method and ship-to address.

This means a prompt like "something to keep my head safe while cycling" can find helmets even though the word "helmet" never appears in the prompt.

 



Advantages of a vector column in a SQL Server database

  • One database instead of two. Embeddings live in the same row as the product they describe. You do not run, secure, back up and pay for a separate vector store.
  • Hybrid queries in one statement. You can combine similarity ranking with ordinary filters and joins (region, dates, price, customer) in a single query. That is exactly what this application does.
  • Transactions and consistency. Updating a product and its embedding happens in the same transaction. There is no sync job that can leave the two out of step.
  • Existing security and operations. Permissions, auditing, backups, high availability and monitoring you already use for SQL Server apply to the vectors too.
  • A real data type. The vector(n) type has a fixed dimension that SQL Server validates, and it is stored in a compact binary format. Storing the same numbers as JSON text or a plain varbinary column gives you none of that checking.
  • Familiar tools. You query with T-SQL, or with LINQ through EF Core, instead of learning a new query language.
  • Search by meaning. Users do not need to know exact product names or keywords.

Two things to keep in mind. The dimension is fixed when you create the column, so it must match your embedding model (1536 for text-embedding-3-small and text-embedding-ada-002). And exact search with VectorDistance compares the query against every candidate row, which is fine for the 500 or so AdventureWorks products but is the reason approximate indexes exist for very large tables.

Packages used

PackageVersion usedPurpose
Microsoft.EntityFrameworkCore.SqlServer11.0.0EF Core provider for SQL Server, including vector mapping and EF.Functions.VectorDistance
Microsoft.Data.SqlClient7.1.1 (pulled in by the EF Core provider)ADO.NET driver. Contains the SqlVector<T> type in the Microsoft.Data.SqlTypes namespace
Azure.AI.OpenAI2.1.0Azure OpenAI client for chat completions and embeddings (brings in the OpenAI package)

You do not add Microsoft.Data.SqlClient yourself. It arrives with the EF Core provider. In the sample reference code you may see Microsoft.Extensions.AI and its IEmbeddingGenerator abstraction used instead of the Azure OpenAI client directly. That is a fine alternative. I call the Azure OpenAI SDK directly to keep the dependency list short.

The SqlVector<T> class

SqlVector<T> is a struct in the Microsoft.Data.SqlTypes namespace, shipped in Microsoft.Data.SqlClient (6.1 and later). It is the .NET representation of the SQL Server vector data type. EF Core maps a SqlVector<float> property to a vector(n) column.

The members used or relevant here:

  • Constructor new SqlVector<float>(ReadOnlyMemory<float> values). You can pass a float[] directly because arrays convert implicitly to ReadOnlyMemory<T>.
  • Length: the number of dimensions.
  • Memory: the values as ReadOnlyMemory<T>, which is how you read a vector back.
  • IsNull and CreateNull(int length): a vector can be null, but SQL Server still needs to know its length, so the null factory takes a length.

Because it is a struct, a nullable property has to be declared as SqlVector<float>?. That is why Product.Embedding is nullable: products have no embedding until the setup step runs. In a query you pass the value with .Value, as in p.Embedding!.Value.

Creating one looks like this:

float[] values = await GetEmbeddingFromModelAsync("mountain bike"); // 1536 floats
var vector = new SqlVector<float>(values);

Console.WriteLine(vector.Length);      // 1536
ReadOnlyMemory<float> back = vector.Memory;

float (32-bit) is the element type used here and the one the embedding models return. Check the current Microsoft documentation for other element types your server version supports.

Building the application step by step

Prerequisites: the .NET 11 SDK, SQL Server 2025 (or Azure SQL) with the AdventureWorks2025 database restored, and an Azure OpenAI resource with one chat deployment and one embedding deployment.

Step 1: Create the ASP.NET Core MVC application

dotnet new mvc -n VectorShipmentApp -f net11.0
cd VectorShipmentApp

This generates the standard MVC project. The project files that come with the template are not repeated below. Only the files I added or changed are listed. In the template I also deleted HomeController.cs and the Views/Home folder and replaced them with ShipmentSearchController and Views/ShipmentSearch (Steps 7 and 8). In Views/Shared/_Layout.cshtml I changed the navigation links to point to ShipmentSearch and removed the Privacy links.

Step 2: Add packages

dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Azure.AI.OpenAI

The resulting package references in VectorShipmentApp.csproj:

<ItemGroup>
  <PackageReference Include="Azure.AI.OpenAI" Version="2.1.0" />
  <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="11.0.0" />
</ItemGroup>

The EF Core provider brings Microsoft.Data.SqlClient with it, which is where SqlVector<T> comes from.

Optional: if your machine has a private NuGet feed that fails to restore, add a nuget.config next to the project that uses only nuget.org:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <packageSources>
    <clear />
    <add key="nuget.org" value="https://api.nuget.org/v3/index.json" />
  </packageSources>
</configuration>

Step 3: Scaffold the AdventureWorks2025 database

You can generate entity classes with the EF Core tools:

dotnet tool install --global dotnet-ef
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet ef dbcontext scaffold "Server=.;Database=AdventureWorks2025;Trusted_Connection=True;TrustServerCertificate=True" Microsoft.EntityFrameworkCore.SqlServer -o Models --context AdventureWorksContext

That generates a class for every table in the database. This application needs only 11 of them, so the code below is a trimmed model with only the columns the application reads. It is the code I used, and it is what you should paste into your project. It differs from plain scaffolded output in two ways: Product has the new Embedding property, and the context contains no connection string (it comes from configuration, Step 4).

Models/Entities.cs

using System.ComponentModel.DataAnnotations.Schema;
using Microsoft.Data.SqlTypes;

namespace VectorShipmentApp.Models;

// Production.Product
public class Product
{
    public int ProductID { get; set; }
    public string Name { get; set; } = "";
    public string ProductNumber { get; set; } = "";
    public string? Color { get; set; }
    public string? Class { get; set; }
    public string? Style { get; set; }
    public decimal ListPrice { get; set; }
    public int? ProductSubcategoryID { get; set; }
    public ProductSubcategory? ProductSubcategory { get; set; }

    // New column added to Production.Product by the Setup button
    [Column(TypeName = "vector(1536)")]
    public SqlVector<float>? Embedding { get; set; }
}

public class ProductSubcategory
{
    public int ProductSubcategoryID { get; set; }
    public int ProductCategoryID { get; set; }
    public string Name { get; set; } = "";
    public ProductCategory ProductCategory { get; set; } = null!;
}

public class ProductCategory
{
    public int ProductCategoryID { get; set; }
    public string Name { get; set; } = "";
}

// Sales.SalesOrderHeader
public class SalesOrderHeader
{
    public int SalesOrderID { get; set; }
    public DateTime OrderDate { get; set; }
    public DateTime DueDate { get; set; }
    public DateTime? ShipDate { get; set; }
    public string? SalesOrderNumber { get; set; }
    public int CustomerID { get; set; }
    public int? TerritoryID { get; set; }
    public int ShipMethodID { get; set; }
    public int ShipToAddressID { get; set; }
    public decimal Freight { get; set; }
    public decimal TotalDue { get; set; }
    public Customer Customer { get; set; } = null!;
    public SalesTerritory? Territory { get; set; }
    public ShipMethod ShipMethod { get; set; } = null!;
    public Address ShipToAddress { get; set; } = null!;
}

// Sales.SalesOrderDetail
public class SalesOrderDetail
{
    public int SalesOrderID { get; set; }
    public int SalesOrderDetailID { get; set; }
    public short OrderQty { get; set; }
    public int ProductID { get; set; }
    public decimal UnitPrice { get; set; }
    public decimal LineTotal { get; set; }
    public SalesOrderHeader SalesOrder { get; set; } = null!;
    public Product Product { get; set; } = null!;
}

// Sales.Customer
public class Customer
{
    public int CustomerID { get; set; }
    public int? PersonID { get; set; }
    public Person? Person { get; set; }
}

// Person.Person
public class Person
{
    public int BusinessEntityID { get; set; }
    public string FirstName { get; set; } = "";
    public string LastName { get; set; } = "";
}

// Person.Address
public class Address
{
    public int AddressID { get; set; }
    public string AddressLine1 { get; set; } = "";
    public string City { get; set; } = "";
    public string PostalCode { get; set; } = "";
    public int StateProvinceID { get; set; }
    public StateProvince StateProvince { get; set; } = null!;
}

// Person.StateProvince
public class StateProvince
{
    public int StateProvinceID { get; set; }
    public string StateProvinceCode { get; set; } = "";
    public string CountryRegionCode { get; set; } = "";
    public string Name { get; set; } = "";
}

// Sales.SalesTerritory
public class SalesTerritory
{
    public int TerritoryID { get; set; }
    public string Name { get; set; } = "";
    public string CountryRegionCode { get; set; } = "";
    public string TerritoryGroup { get; set; } = "";
}

// Purchasing.ShipMethod
public class ShipMethod
{
    public int ShipMethodID { get; set; }
    public string Name { get; set; } = "";
}

Data/AdventureWorksContext.cs

using Microsoft.EntityFrameworkCore;
using VectorShipmentApp.Models;

namespace VectorShipmentApp.Data;

public class AdventureWorksContext : DbContext
{
    public AdventureWorksContext(DbContextOptions<AdventureWorksContext> options) : base(options) { }

    public DbSet<Product> Products => Set<Product>();
    public DbSet<SalesOrderHeader> SalesOrderHeaders => Set<SalesOrderHeader>();
    public DbSet<SalesOrderDetail> SalesOrderDetails => Set<SalesOrderDetail>();

    protected override void OnModelCreating(ModelBuilder b)
    {
        b.Entity<Product>(e =>
        {
            e.ToTable("Product", "Production").HasKey(x => x.ProductID);
            e.Property(x => x.ListPrice).HasColumnType("money");
            e.Property(x => x.Embedding).HasColumnType("vector(1536)");
        });
        b.Entity<ProductSubcategory>(e => e.ToTable("ProductSubcategory", "Production").HasKey(x => x.ProductSubcategoryID));
        b.Entity<ProductCategory>(e => e.ToTable("ProductCategory", "Production").HasKey(x => x.ProductCategoryID));

        b.Entity<SalesOrderHeader>(e =>
        {
            e.ToTable("SalesOrderHeader", "Sales").HasKey(x => x.SalesOrderID);
            e.Property(x => x.Freight).HasColumnType("money");
            e.Property(x => x.TotalDue).HasColumnType("money");
            e.HasOne(x => x.Territory).WithMany().HasForeignKey(x => x.TerritoryID);
            e.HasOne(x => x.ShipToAddress).WithMany().HasForeignKey(x => x.ShipToAddressID);
        });

        b.Entity<SalesOrderDetail>(e =>
        {
            e.ToTable("SalesOrderDetail", "Sales").HasKey(x => new { x.SalesOrderID, x.SalesOrderDetailID });
            e.Property(x => x.UnitPrice).HasColumnType("money");
            e.Property(x => x.LineTotal).HasColumnType("numeric(38,6)");
            e.HasOne(x => x.SalesOrder).WithMany().HasForeignKey(x => x.SalesOrderID);
            e.HasOne(x => x.Product).WithMany().HasForeignKey(x => x.ProductID);
        });

        b.Entity<Customer>(e =>
        {
            e.ToTable("Customer", "Sales").HasKey(x => x.CustomerID);
            e.HasOne(x => x.Person).WithMany().HasForeignKey(x => x.PersonID);
        });
        b.Entity<Person>().ToTable("Person", "Person").HasKey(x => x.BusinessEntityID);
        b.Entity<Address>().ToTable("Address", "Person").HasKey(x => x.AddressID);
        b.Entity<StateProvince>().ToTable("StateProvince", "Person").HasKey(x => x.StateProvinceID);
        b.Entity<SalesTerritory>(e =>
        {
            e.ToTable("SalesTerritory", "Sales").HasKey(x => x.TerritoryID);
            e.Property(x => x.TerritoryGroup).HasColumnName("Group");
        });
        b.Entity<ShipMethod>().ToTable("ShipMethod", "Purchasing").HasKey(x => x.ShipMethodID);
    }
}

Explanation

Entities.cs. Each class maps to one AdventureWorks table, and only the columns this application needs are declared. Product, ProductSubcategory and ProductCategory describe products. SalesOrderHeader holds the order, ship and due dates, the customer, territory, ship method and ship-to address. SalesOrderDetail holds the product, quantity and unit price for each order line. Customer and Person give the customer name, Address and StateProvince give the destination, SalesTerritory gives the sales region and ShipMethod gives the carrier.

The important property is on Product:

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

This is the same pattern as the reference sample, applied to an existing table. The column does not exist in AdventureWorks yet. The setup button in Step 6 adds it.

AdventureWorksContext.cs. The context exposes three DbSets (products, order headers and order details). The other entities are reached through navigation properties. OnModelCreating maps each class to its real schema and table name (for example Production.Product and Sales.SalesOrderHeader), sets the keys, maps money columns to decimal, and maps TerritoryGroup to the SQL column named Group, which is a reserved word. It also repeats HasColumnType("vector(1536)") for Embedding so the type is explicit. Foreign keys that do not follow EF naming conventions, such as Customer.PersonID pointing to Person.BusinessEntityID, are configured with HasOne(...).WithMany().HasForeignKey(...).

Step 4: Configuration and startup

Add the connection string and the Azure OpenAI settings to appsettings.json. Replace the placeholders with your own values. Do not commit a real key to source control. Use user secrets or environment variables for it.

appsettings.json

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "DefaultConnection": "Server=.;Database=AdventureWorks2025;Trusted_Connection=True;TrustServerCertificate=True"
  },
  "AzureOpenAI": {
    "Endpoint": "https://<your-resource-name>.openai.azure.com/",
    "Key": "<your-azure-openai-key>",
    "ChatDeployment": "<your-chat-deployment-name>",
    "EmbeddingDeployment": "<your-embedding-deployment-name>"
  }
}

Program.cs

using Microsoft.EntityFrameworkCore;
using VectorShipmentApp.Data;
using VectorShipmentApp.Services;

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddControllersWithViews();

builder.Services.AddDbContext<AdventureWorksContext>(o =>
    o.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

builder.Services.Configure<AzureOpenAIOptions>(builder.Configuration.GetSection("AzureOpenAI"));
builder.Services.AddSingleton<OpenAIService>();
builder.Services.AddScoped<ShipmentSearchService>();

var app = builder.Build();

if (!app.Environment.IsDevelopment())
{
    app.UseExceptionHandler("/ShipmentSearch/Error");
    app.UseHsts();
}

app.UseHttpsRedirection();
app.UseRouting();
app.UseAuthorization();
app.MapStaticAssets();

app.MapControllerRoute(
    name: "default",
    pattern: "{controller=ShipmentSearch}/{action=Index}/{id?}")
    .WithStaticAssets();

app.Run();

Explanation

appsettings.json has two additions. ConnectionStrings:DefaultConnection points to the local server and the AdventureWorks2025 database. The AzureOpenAI section holds the endpoint, the key, the name of the chat deployment and the name of the embedding deployment. The embedding deployment must return 1536 dimensions to match the column.

Program.cs registers four things. AddDbContext creates AdventureWorksContext with UseSqlServer and the connection string. Configure<AzureOpenAIOptions> binds the AzureOpenAI section to a settings class. OpenAIService is a singleton, because it only wraps a client that can be shared. ShipmentSearchService is scoped, because it uses the scoped DbContext. The default route is changed to ShipmentSearch/Index, and the error handler points to /ShipmentSearch/Error.

Step 5: The Azure OpenAI service

Services/OpenAIService.cs

using System.ClientModel;
using System.Text.Json;
using Azure;
using Azure.AI.OpenAI;
using OpenAI.Chat;

namespace VectorShipmentApp.Services;

public class AzureOpenAIOptions
{
    public string Endpoint { get; set; } = "";
    public string Key { get; set; } = "";
    public string ChatDeployment { get; set; } = "";
    public string EmbeddingDeployment { get; set; } = "";
}

public class SearchFilters
{
    public string? ProductName { get; set; }
    public string? Region { get; set; }
    public string? CountryCode { get; set; }
    public string? Customer { get; set; }
    public DateTime? DateFrom { get; set; }
    public DateTime? DateTo { get; set; }
    public string? DateField { get; set; }   // OrderDate | ShipDate | DueDate
    public decimal? MinPrice { get; set; }
    public decimal? MaxPrice { get; set; }
}

public class OpenAIService
{
    private readonly AzureOpenAIOptions _o;
    private readonly AzureOpenAIClient _client;

    public OpenAIService(Microsoft.Extensions.Options.IOptions<AzureOpenAIOptions> options)
    {
        _o = options.Value;
        _client = new AzureOpenAIClient(new Uri(_o.Endpoint), new AzureKeyCredential(_o.Key));
    }

    public async Task<float[]> EmbedAsync(string text)
    {
        var r = await _client.GetEmbeddingClient(_o.EmbeddingDeployment).GenerateEmbeddingAsync(text);
        return r.Value.ToFloats().ToArray();
    }

    public async Task<List<float[]>> EmbedBatchAsync(IList<string> texts)
    {
        var r = await _client.GetEmbeddingClient(_o.EmbeddingDeployment).GenerateEmbeddingsAsync(texts);
        return r.Value.OrderBy(e => e.Index).Select(e => e.ToFloats().ToArray()).ToList();
    }

    // Turns the user's natural language prompt into structured filters.
    public async Task<SearchFilters> ParsePromptAsync(string prompt)
    {
        var system = """
            You extract search filters for an AdventureWorks sales/shipment database (data spans 2011-2014).
            Return ONLY a JSON object with these keys (use null when not mentioned):
              productName  : the product the user is asking about (free text, e.g. "mountain bike", "red helmet")
              region       : a sales territory, state/province or city name (e.g. "Northwest", "Texas", "London")
              countryCode  : ISO 2-letter country code if a country is mentioned (e.g. US, CA, FR, GB, DE, AU)
              customer     : customer person name (first and/or last name)
              dateFrom     : ISO date yyyy-MM-dd
              dateTo       : ISO date yyyy-MM-dd (inclusive; a whole month or year becomes the full range)
              dateField    : "OrderDate", "ShipDate" or "DueDate" (delivery = DueDate, shipped = ShipDate, default ShipDate)
              minPrice     : number, unit price lower bound
              maxPrice     : number, unit price upper bound
            """;

        var chat = _client.GetChatClient(_o.ChatDeployment);
        var res = await chat.CompleteChatAsync(
            [new SystemChatMessage(system), new UserChatMessage(prompt)],
            new ChatCompletionOptions { ResponseFormat = ChatResponseFormat.CreateJsonObjectFormat() });

        var json = res.Value.Content[0].Text;
        return JsonSerializer.Deserialize<SearchFilters>(json, new JsonSerializerOptions { PropertyNameCaseInsensitive = true })
               ?? new SearchFilters();
    }
}

Explanation

AzureOpenAIOptions mirrors the configuration section. SearchFilters is the structure the chat model fills in: product text, region, country code, customer, date range, which date to filter on, and the price range.

OpenAIService creates one AzureOpenAIClient from the endpoint and key. It has three methods:

  • EmbedAsync turns one piece of text into a float[] using the embedding deployment. It is used for the search prompt.
  • EmbedBatchAsync does the same for a list of texts in one call and returns the vectors in input order. It is used by the setup step.
  • ParsePromptAsync sends the user's prompt to the chat deployment with a system message that lists the filter keys and asks for a JSON object only. ChatResponseFormat.CreateJsonObjectFormat() forces valid JSON, which is then deserialized into SearchFilters. The system message tells the model that the data spans 2011 to 2014 and that "delivery" means the due date and "shipped" means the ship date.

Step 6: The search service (EF Core 11 vector search)

Services/ShipmentSearchService.cs

using Microsoft.Data.SqlTypes;
using Microsoft.EntityFrameworkCore;
using VectorShipmentApp.Data;
using VectorShipmentApp.Models;

namespace VectorShipmentApp.Services;

public record MatchedProduct(int ProductID, string Name, double Distance);

public record ShipmentRow(
    int SalesOrderID, string? SalesOrderNumber, string ProductName, short Qty, decimal UnitPrice, decimal LineTotal,
    DateTime OrderDate, DateTime? ShipDate, DateTime DueDate, string Customer, string ShipMethod,
    string City, string State, string Country, string? Territory);

public class SearchResult
{
    public SearchFilters Filters { get; set; } = new();
    public List<MatchedProduct> MatchedProducts { get; set; } = new();
    public List<ShipmentRow> Rows { get; set; } = new();
}

public class ShipmentSearchService
{
    private readonly AdventureWorksContext _db;
    private readonly OpenAIService _ai;

    public ShipmentSearchService(AdventureWorksContext db, OpenAIService ai) { _db = db; _ai = ai; }

    public async Task<SearchResult> SearchAsync(string prompt)
    {
        var f = await _ai.ParsePromptAsync(prompt);
        var result = new SearchResult { Filters = f };

        IQueryable<SalesOrderDetail> q = _db.SalesOrderDetails.AsNoTracking();

        // 1. Vector search: find products semantically close to the product text in the prompt
        if (!string.IsNullOrWhiteSpace(f.ProductName))
        {
            var queryVector = new SqlVector<float>(await _ai.EmbedAsync(f.ProductName));

            var top = await _db.Products
                .Where(p => p.Embedding != null)
                .OrderBy(p => EF.Functions.VectorDistance("cosine", p.Embedding!.Value, queryVector))
                .Select(p => new
                {
                    p.ProductID,
                    p.Name,
                    Distance = EF.Functions.VectorDistance("cosine", p.Embedding!.Value, queryVector)
                })
                .Take(10)
                .ToListAsync();

            var ids = top.Select(t => t.ProductID).ToList();
            result.MatchedProducts = top.Select(t => new MatchedProduct(t.ProductID, t.Name, t.Distance)).ToList();

            q = q.Where(d => ids.Contains(d.ProductID));
        }

        // 2. Relational filters
        if (!string.IsNullOrWhiteSpace(f.Region) || !string.IsNullOrWhiteSpace(f.CountryCode))
        {
            var region = f.Region ?? "\u0001";
            var cc = f.CountryCode ?? "\u0001";
            q = q.Where(d =>
                d.SalesOrder.ShipToAddress.StateProvince.CountryRegionCode == cc ||
                d.SalesOrder.Territory!.Name.Contains(region) ||
                d.SalesOrder.Territory.TerritoryGroup.Contains(region) ||
                d.SalesOrder.ShipToAddress.StateProvince.Name.Contains(region) ||
                d.SalesOrder.ShipToAddress.City.Contains(region));
        }

        if (!string.IsNullOrWhiteSpace(f.Customer))
        {
            var c = f.Customer;
            q = q.Where(d => d.SalesOrder.Customer.Person != null &&
                (d.SalesOrder.Customer.Person.FirstName + " " + d.SalesOrder.Customer.Person.LastName).Contains(c));
        }

        if (f.MinPrice is decimal min) q = q.Where(d => d.UnitPrice >= min);
        if (f.MaxPrice is decimal max) q = q.Where(d => d.UnitPrice <= max);

        if (f.DateFrom is DateTime from || f.DateTo is DateTime)
        {
            var to = f.DateTo?.Date.AddDays(1);
            var fromD = f.DateFrom?.Date;
            switch ((f.DateField ?? "ShipDate").ToLowerInvariant())
            {
                case "orderdate":
                    if (fromD != null) q = q.Where(d => d.SalesOrder.OrderDate >= fromD);
                    if (to != null) q = q.Where(d => d.SalesOrder.OrderDate < to);
                    break;
                case "duedate":
                    if (fromD != null) q = q.Where(d => d.SalesOrder.DueDate >= fromD);
                    if (to != null) q = q.Where(d => d.SalesOrder.DueDate < to);
                    break;
                default:
                    if (fromD != null) q = q.Where(d => d.SalesOrder.ShipDate >= fromD);
                    if (to != null) q = q.Where(d => d.SalesOrder.ShipDate < to);
                    break;
            }
        }

        // 3. Project shipment details
        result.Rows = await q
            .OrderByDescending(d => d.SalesOrder.ShipDate)
            .Take(200)
            .Select(d => new ShipmentRow(
                d.SalesOrderID,
                d.SalesOrder.SalesOrderNumber,
                d.Product.Name,
                d.OrderQty,
                d.UnitPrice,
                d.LineTotal,
                d.SalesOrder.OrderDate,
                d.SalesOrder.ShipDate,
                d.SalesOrder.DueDate,
                d.SalesOrder.Customer.Person != null
                    ? d.SalesOrder.Customer.Person.FirstName + " " + d.SalesOrder.Customer.Person.LastName
                    : "(Store customer)",
                d.SalesOrder.ShipMethod.Name,
                d.SalesOrder.ShipToAddress.City,
                d.SalesOrder.ShipToAddress.StateProvince.Name,
                d.SalesOrder.ShipToAddress.StateProvince.CountryRegionCode,
                d.SalesOrder.Territory != null ? d.SalesOrder.Territory.Name : null))
            .ToListAsync();

        return result;
    }

    // Adds Production.Product.Embedding vector(1536) if needed and embeds every product that has none yet.
    public async Task<int> BuildEmbeddingsAsync()
    {
        await _db.Database.ExecuteSqlRawAsync(
            "IF COL_LENGTH('Production.Product','Embedding') IS NULL ALTER TABLE Production.Product ADD Embedding vector(1536) NULL;");

        var todo = await _db.Products
            .Include(p => p.ProductSubcategory!).ThenInclude(s => s.ProductCategory)
            .Where(p => p.Embedding == null)
            .ToListAsync();

        int added = 0;
        foreach (var batch in todo.Chunk(50))
        {
            var texts = batch.Select(p => p.Name
                + (p.ProductSubcategory != null ? ". Subcategory: " + p.ProductSubcategory.Name + ". Category: " + p.ProductSubcategory.ProductCategory.Name : "")
                + (p.Color != null ? ". Color: " + p.Color : "")).ToList();
            var vectors = await _ai.EmbedBatchAsync(texts);
            for (int i = 0; i < batch.Length; i++)
                batch[i].Embedding = new SqlVector<float>(vectors[i]);
            added += await _db.SaveChangesAsync();
        }
        return added;
    }
}

Explanation

This is the core of the application. It has two public methods.

SearchAsync runs the search in three stages.

  1. Parse. It asks OpenAIService for the structured filters.
  2. Vector search. If a product was mentioned, it embeds that text and wraps the result in a SqlVector<float>. It then queries Products, keeps rows that have an embedding, orders them by EF.Functions.VectorDistance("cosine", p.Embedding!.Value, queryVector) and takes the top 10. The distance is also selected so the page can show how close each match is. The 10 product IDs become a Where(d => ids.Contains(d.ProductID)) condition on the order lines.
  3. Relational filters. The remaining filters are added to the same IQueryable. Region and country match the sales territory name or group, the state name, the city, or the country code of the ship-to address. Customer matches first name plus last name. Price compares the unit price. The date filter is applied to ship date, order date or due date depending on what the chat model chose, and the end date is made inclusive by comparing with the next day. Finally the query is projected into ShipmentRow objects, ordered by ship date and limited to 200 rows.

Because everything is built on one IQueryable, the filters that were not mentioned are simply not added, and EF Core turns the final result into a single SQL statement for the order data.

BuildEmbeddingsAsync is the one-time setup. It runs ALTER TABLE Production.Product ADD Embedding vector(1536) NULL if the column is missing. It loads the products that have no embedding, including their subcategory and category, and builds a text such as "Mountain-200 Black, 42. Subcategory: Mountain Bikes. Category: Bikes. Color: Black". It sends the texts to Azure OpenAI 50 at a time, assigns each returned array to product.Embedding = new SqlVector<float>(vectors[i]), and calls SaveChangesAsync. Running it again only processes products that still have no embedding.

Step 7: The controller

Controllers/ShipmentSearchController.cs

using System.Diagnostics;
using Microsoft.AspNetCore.Mvc;
using VectorShipmentApp.Models;
using VectorShipmentApp.Services;

namespace VectorShipmentApp.Controllers;

public class ShipmentSearchController : Controller
{
    private readonly ShipmentSearchService _search;

    public ShipmentSearchController(ShipmentSearchService search) => _search = search;

    public IActionResult Index() => View(new SearchViewModel());

    [HttpPost]
    public async Task<IActionResult> Index(string prompt)
    {
        var vm = new SearchViewModel { Prompt = prompt };
        if (string.IsNullOrWhiteSpace(prompt)) return View(vm);
        try
        {
            vm.Result = await _search.SearchAsync(prompt);
        }
        catch (Exception ex)
        {
            vm.Error = ex.GetBaseException().Message;
        }
        return View(vm);
    }

    [HttpPost]
    public async Task<IActionResult> BuildEmbeddings()
    {
        var vm = new SearchViewModel();
        try
        {
            var n = await _search.BuildEmbeddingsAsync();
            vm.Info = $"Embeddings created for {n} product(s).";
        }
        catch (Exception ex)
        {
            vm.Error = ex.GetBaseException().Message;
        }
        return View("Index", vm);
    }

    [ResponseCache(Duration = 0, Location = ResponseCacheLocation.None, NoStore = true)]
    public IActionResult Error() =>
        View(new ErrorViewModel { RequestId = Activity.Current?.Id ?? HttpContext.TraceIdentifier });
}

public class SearchViewModel
{
    public string? Prompt { get; set; }
    public string? Error { get; set; }
    public string? Info { get; set; }
    public SearchResult? Result { get; set; }
}

Explanation

The controller is thin. Index (GET) shows the empty page. Index (POST) receives the prompt, calls ShipmentSearchService.SearchAsync and puts the result in a SearchViewModel. Any exception, such as a wrong Azure OpenAI key or a missing embedding column, is caught and its message is shown on the page. BuildEmbeddings (POST) calls the setup method and reports how many products were embedded. Error is the standard error action moved over from the old HomeController. SearchViewModel carries the prompt, the result, and info and error messages to the view.

Step 8: The view

Views/ShipmentSearch/Index.cshtml

@model VectorShipmentApp.Controllers.SearchViewModel
@{
    ViewData["Title"] = "Shipment Search";
}

<h2>Product Shipment / Delivery Search</h2>
<p class="text-muted">
    Ask in plain English, e.g. <em>"Mountain bikes shipped to Canada in 2013 over $500"</em>,
    <em>"helmets delivered to customer Smith in the Northwest between June and August 2012"</em>.
</p>

<form method="post" asp-action="Index" class="mb-2">
    <div class="input-group">
        <input type="text" name="prompt" value="@Model.Prompt" class="form-control form-control-lg" placeholder="Describe the shipments you want..." />
        <button class="btn btn-primary" type="submit">Search</button>
    </div>
</form>

<div class="d-flex gap-2 mb-3">
    <a asp-action="Index" class="btn btn-outline-secondary btn-sm">Clear</a>
    <form method="post" asp-action="BuildEmbeddings" class="m-0">
        <button class="btn btn-outline-secondary btn-sm" type="submit">One-time setup: create / refresh product embeddings</button>
    </form>
</div>

@if (Model.Info != null) { <div class="alert alert-success">@Model.Info</div> }
@if (Model.Error != null) { <div class="alert alert-danger">@Model.Error</div> }

@if (Model.Result != null)
{
    <div class="card mb-3">
        <div class="card-body" style="max-height: 220px; overflow-y: auto;">
            <h6>Tables used</h6>
            <ul class="mb-0">
                <li>Production.Product (including the Embedding vector(1536) column)</li>
                <li>Production.ProductSubcategory</li>
                <li>Production.ProductCategory</li>
                <li>Sales.SalesOrderHeader</li>
                <li>Sales.SalesOrderDetail</li>
                <li>Sales.Customer</li>
                <li>Person.Person</li>
                <li>Person.Address</li>
                <li>Person.StateProvince</li>
                <li>Sales.SalesTerritory</li>
                <li>Purchasing.ShipMethod</li>
            </ul>
            @if (Model.Result.MatchedProducts.Any())
            {
                <h6 class="mt-3">Vector-matched products (cosine distance)</h6>
                <ul class="mb-0">
                    @foreach (var p in Model.Result.MatchedProducts)
                    {
                        <li>@p.Name <span class="text-muted">(@p.Distance.ToString("0.000"))</span></li>
                    }
                </ul>
            }
        </div>
    </div>

    <p><strong>@Model.Result.Rows.Count</strong> row(s)</p>
    <div style="max-height: 500px; overflow: auto;">
        <table class="table table-sm table-striped">
            <thead class="table-light" style="position: sticky; top: 0;">
                <tr>
                    <th>Order</th><th>Product</th><th>Qty</th><th>Unit price</th><th>Line total</th>
                    <th>Ordered</th><th>Shipped</th><th>Due</th><th>Customer</th><th>Ship method</th><th>Ship to</th>
                </tr>
            </thead>
            <tbody>
                @foreach (var r in Model.Result.Rows)
                {
                    <tr>
                        <td>@r.SalesOrderNumber</td>
                        <td>@r.ProductName</td>
                        <td>@r.Qty</td>
                        <td>@r.UnitPrice.ToString("C")</td>
                        <td>@r.LineTotal.ToString("C")</td>
                        <td>@r.OrderDate.ToString("yyyy-MM-dd")</td>
                        <td>@r.ShipDate?.ToString("yyyy-MM-dd")</td>
                        <td>@r.DueDate.ToString("yyyy-MM-dd")</td>
                        <td>@r.Customer</td>
                        <td>@r.ShipMethod</td>
                        <td>@r.City, @r.State, @r.Country (@r.Territory)</td>
                    </tr>
                }
            </tbody>
        </table>
    </div>
}

Explanation

The page has a prompt box with a Search button. Below it are a Clear button, which reloads the page empty, and the one-time setup button, which posts to BuildEmbeddings. After a search, a "Tables used" card lists the tables the query touched and, when a product was mentioned, the vector-matched products with their cosine distance. The card scrolls vertically if it is long. The results are shown in a table inside a container that scrolls both ways, and the header row stays visible while scrolling down.

Step 9: Run it

  1. Fill in the placeholders in appsettings.json.
  2. Run dotnet run and open the site.
  3. Click One-time setup: create / refresh product embeddings and wait until the success message appears.
  4. Try prompts such as:
    • Mountain bikes shipped to Canada in 2013 over $500
    • Helmets delivered to customer Smith in the Northwest between June and August 2012
    • Something to keep my head safe while cycling
The following Video shows the execution


Code for this article can be downloaded from this link.

Conclusion

The vector support in SQL Server 2025 and EF Core means semantic search no longer needs a separate vector database. The application here adds one vector(1536) column to an existing table, fills it with embeddings from Azure OpenAI, and queries it with EF.Functions.VectorDistance in the same LINQ query that applies ordinary filters on dates, prices, regions and customers. Azure OpenAI does two jobs: it turns the user's sentence into filters, and it turns product text into vectors.

Popular posts from this blog

Azure AI Building RAG Application Solution: Using Azure AI Search Service and Creating Data Source, Index, and Indexer

ASP.NET Core 8 API: How to implement REPR Pattern Endpoints in ASP.NET Core 8 API

Azure AI Document Intelligence: Processing an Invoice and Saving it in Azure SQL Server Database using Azure Functions