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 insideORDER 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-SQLVECTOR_DISTANCEfunction. This is what the application uses. - Approximate vector search (experimental). EF Core 11 adds translation for the SQL Server
VECTOR_SEARCHfunction, 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
FullJoinoperator. - JSON path check.
EF.Functions.JsonPathExists()maps to SQL Server'sJSON_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:
- 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.
- If the prompt mentions a product, that text is embedded with the same embedding deployment.
- EF Core runs
VectorDistancewith the cosine metric againstProduct.Embeddingand returns the 10 closest products. - 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 plainvarbinarycolumn 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
| Package | Version used | Purpose |
|---|---|---|
| Microsoft.EntityFrameworkCore.SqlServer | 11.0.0 | EF Core provider for SQL Server, including vector mapping and EF.Functions.VectorDistance |
| Microsoft.Data.SqlClient | 7.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.OpenAI | 2.1.0 | Azure 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 afloat[]directly because arrays convert implicitly toReadOnlyMemory<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:
EmbedAsyncturns one piece of text into afloat[]using the embedding deployment. It is used for the search prompt.EmbedBatchAsyncdoes the same for a list of texts in one call and returns the vectors in input order. It is used by the setup step.ParsePromptAsyncsends 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 intoSearchFilters. 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.
- Parse. It asks
OpenAIServicefor the structured filters. - Vector search. If a product was mentioned, it embeds that text and wraps the result in a
SqlVector<float>. It then queriesProducts, keeps rows that have an embedding, orders them byEF.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 aWhere(d => ids.Contains(d.ProductID))condition on the order lines. - 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 intoShipmentRowobjects, 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
- Fill in the placeholders in
appsettings.json. - Run
dotnet runand open the site. - Click One-time setup: create / refresh product embeddings and wait until the success message appears.
- 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
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.

