Adding RAG to an Existing ASP.NET Core App: SQL Server Chunks, Embeddings and Hybrid Ranking

When I add AI to an existing business application, my first concern is not the chat interface. It is whether the assistant can access something the current user cannot.

That is the starting point for my RAG ASP.NET Core implementation in SupportAgent.NET, an ASP.NET Core and React customer-support copilot.

The internal assistant uses native tool calling to look up permitted customer and order records through normal .NET services. It also retrieves company documents and drafts replies, which a human agent reviews before sending. Identity roles and tenant filters remain application rules, not instructions I expect the model to obey.

Here, I focus on the document-retrieval path: SQL Server chunks and embeddings, hybrid ranking, and the checks that must happen before context reaches the model.

Why I use RAG instead of fine-tuning for business documents

RAG supplies retrieved information at request time. Fine-tuning changes model weights. For changing policies and procedures, I want to update searchable documents without retraining the answering model. Fine-tuning can help with specialized behavior, but it is not my document-refresh mechanism.

I also keep transactional data separate. An order's current status belongs behind GetOrderStatus, not in an embedding generated from yesterday's database export. Documents provide policy context; application services provide current facts.

Neither approach replaces authorization. A model knowing a fact does not establish that the current user may read it.

Chunking documents and storing them in SQL Server

SupportAgent.NET stores chunk embeddings as JSON in SQL Server. That keeps the initial retrieval path within the existing data layer, without requiring a separate vector store.

These are reduced .NET 8 examples, not verbatim repository files. The methods belong in the existing services, and they rely on these namespaces:

using System.Net.Http.Json;
using System.Security.Claims;
using System.Text.Json;
using System.Text.RegularExpressions;
using Microsoft.AspNetCore.Authorization;
using Microsoft.EntityFrameworkCore;

public sealed class KnowledgeChunk
{
    public long Id { get; set; }
    public Guid OrganizationId { get; set; }
    public Guid DocumentId { get; set; }
    public int Ordinal { get; set; }
    public required string Content { get; set; }
    public required string Source { get; set; }
    public string? EmbeddingJson { get; set; }
    public string? EmbeddingSpace { get; set; }
}

I merge this mapping into the existing context, preserving its Identity configuration and other mappings:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder); // Preserve Identity mappings.

    var chunks = modelBuilder.Entity<KnowledgeChunk>();
    chunks.Property(c => c.EmbeddingJson).HasColumnType("nvarchar(max)");
    chunks.HasIndex(c => new
        { c.OrganizationId, c.DocumentId, c.Ordinal }).IsUnique();
}

You'll need a migration for this. EmbeddingSpace is a schema-hardening suggestion in this example rather than a description of the repository's current metadata.

The following method belongs inside the authorized ingestion service. trustedOrganizationId comes from server-side identity, never an upload field.

public static async Task StoreAsync(
    DbContext db, IEmbeddingProvider embeddings,
    Guid trustedOrganizationId, Guid documentId,
    string source, string text, int size, int overlap,
    CancellationToken ct)
{
    ArgumentException.ThrowIfNullOrWhiteSpace(text);
    ArgumentException.ThrowIfNullOrWhiteSpace(source);

    if (trustedOrganizationId == Guid.Empty || documentId == Guid.Empty)
        throw new ArgumentException("Missing document scope.");

    if (size <= 0 || overlap < 0 || overlap >= size)
        throw new ArgumentOutOfRangeException(nameof(overlap));

    int ordinal = 0;
    for (int start = 0; start < text.Length; start += size - overlap)
    {
        int length = Math.Min(size, text.Length - start);
        string content = text.Substring(start, length);
        if (string.IsNullOrWhiteSpace(content)) continue;

        var vector = await embeddings.EmbedAsync(
            content, EmbeddingPurpose.Document, ct);

        db.Set<KnowledgeChunk>().Add(new KnowledgeChunk
        {
            OrganizationId = trustedOrganizationId,
            DocumentId = documentId,
            Ordinal = ordinal++,
            Source = source,
            Content = content,
            EmbeddingJson = JsonSerializer.Serialize(vector),
            EmbeddingSpace = embeddings.SpaceKey
        });

        if (start + length == text.Length) break;
    }

    await db.SaveChangesAsync(ct);
}

This intentionally simple character-window splitter makes overlap visible. It can cut sentences, and a character count is not a token budget. For varied documents, I would adapt splitting to headings, paragraphs, tables, and the embedding model's tokenizer.

This method inserts a new document's chunks. In production, I handle replacements transactionally so obsolete chunks are removed, authorize the parent document before writing, and generate replacement vectors before opening the database transaction so it is never held across network requests.

Generating embeddings behind an interface

The answering model and embedding model are separate choices. Switching the chat provider does not inherently require rebuilding document vectors; switching embedding spaces does.

public enum EmbeddingPurpose { Document, Query }

public interface IEmbeddingProvider
{
    string SpaceKey { get; }

    Task<float[]> EmbedAsync(string text,
        EmbeddingPurpose purpose, CancellationToken ct);
}

public sealed class HttpEmbeddingProvider(
    HttpClient http, string model, string spaceKey,
    bool ollama, string documentPrefix, string queryPrefix)
    : IEmbeddingProvider
{
    public string SpaceKey => spaceKey;

    public async Task<float[]> EmbedAsync(string text,
        EmbeddingPurpose purpose, CancellationToken ct)
    {
        ArgumentException.ThrowIfNullOrWhiteSpace(text);

        string input = (purpose == EmbeddingPurpose.Query
            ? queryPrefix : documentPrefix) + text;

        object body = ollama
            ? new { model, input, truncate = false }
            : new { model, input, encoding_format = "float" };

        using var response = await http.PostAsJsonAsync(
            ollama ? "api/embed" : "v1/embeddings", body, ct);
        response.EnsureSuccessStatusCode();

        using var json = JsonDocument.Parse(
            await response.Content.ReadAsStringAsync(ct));

        var values = ollama
            ? json.RootElement.GetProperty("embeddings")[0]
            : json.RootElement.GetProperty("data")[0]
                .GetProperty("embedding");

        var vector = values.EnumerateArray()
            .Select(x => x.GetSingle()).ToArray();

        if (vector.Length == 0 || vector.All(x => x == 0) ||
            vector.Any(x => !float.IsFinite(x)))
            throw new InvalidOperationException("Invalid embedding.");

        return vector;
    }
}

The adapter handles OpenAI's /v1/embeddings and Ollama's /api/embed response shapes. Ollama's truncate = false makes oversized input fail instead of silently discarding text.

Register a configured HttpClient with the provider's origin as its base address, a timeout, and server-side authentication where required. Supply the model and prefixes through configuration, and use empty prefixes when the selected model does not require them. Nomic's nomic-embed-text-v1.5, for example, distinguishes search_document: from search_query:.

SpaceKey should identify the pinned model/version, dimensions, and preprocessing. Matching array lengths alone does not make vectors compatible. On an embedding-model change, re-embed the corpus; do not compare old and new spaces.

Hybrid ranking in a RAG ASP.NET Core application

I want semantic matches without losing literal terms such as product names or error codes. This example combines cosine similarity with distinct query-token coverage. It is deliberately not BM25 or SQL Server full-text search.

public static class HybridRank
{
    public static HashSet<string> Tokens(string text) =>
        Regex.Matches(text.ToLowerInvariant(), @"[\p{L}\p{N}]+")
            .Select(m => m.Value).ToHashSet();

    public static double Score(KnowledgeChunk chunk,
        HashSet<string> terms, float[] queryVector, string spaceKey)
    {
        var words = Tokens(chunk.Content);
        double lexical = terms.Count == 0 ? 0
            : terms.Count(words.Contains) / (double)terms.Count;

        var vector = chunk.EmbeddingSpace == spaceKey &&
                     chunk.EmbeddingJson is not null
            ? JsonSerializer.Deserialize<float[]>(chunk.EmbeddingJson)
            : null;

        // Missing or incompatible vectors use lexical matching.
        if (vector is null) return lexical;

        double semantic = Math.Max(0, Cosine(queryVector, vector));
        return 0.75 * semantic + 0.25 * lexical;
    }

    private static double Cosine(float[] a, float[] b)
    {
        if (a.Length == 0 || a.Length != b.Length)
            throw new InvalidOperationException("Embedding mismatch.");

        double dot = 0, aa = 0, bb = 0;
        for (int i = 0; i < a.Length; i++)
        {
            dot += (double)a[i] * b[i];
            aa += (double)a[i] * a[i];
            bb += (double)b[i] * b[i];
        }

        return aa == 0 || bb == 0 ? 0
            : Math.Clamp(dot / Math.Sqrt(aa * bb), -1, 1);
    }
}

Cosine measures vector alignment. I clamp negative similarity to zero and combine it with lexical coverage, which already lies between zero and one. The weights mirror the project's configurable semantic/lexical starting point, not a measured optimum, so thresholds and weights need evaluation against representative questions, including questions that should return nothing.

For a larger keyword-search workload, SQL Server's FREETEXTTABLE or CONTAINSTABLE can supply ranked candidates after a full-text index is configured. Their ranks are not directly interchangeable with cosine scores, so I would normalize deliberately or combine rank positions instead.

Enforcing tenant and role permissions before retrieval

I authorize the operation first, then constrain the database query, then rank the permitted chunks. Filtering after retrieval risks exposing text through prompts, logs, or intermediate results.

In Program.cs, with Identity already configured:

builder.Services.AddAuthorization(options =>
    options.AddPolicy("Knowledge.Read", policy => policy
        .RequireAuthenticatedUser()
        .RequireRole("Admin", "SupportAgent")));

This policy allows either named role. It does not make an administrator a cross-tenant administrator.

Inside the scoped retrieval service:

public sealed record RankedChunk(KnowledgeChunk Chunk, double Score);

public static async Task<RankedChunk[]> SearchAsync(
    DbContext db, IAuthorizationService authorization,
    ClaimsPrincipal user, Guid trustedOrganizationId,
    IEmbeddingProvider embeddings, string query,
    int limit, double minimumScore, CancellationToken ct)
{
    ArgumentException.ThrowIfNullOrWhiteSpace(query);

    if (trustedOrganizationId == Guid.Empty || limit <= 0 ||
        !double.IsFinite(minimumScore) ||
        minimumScore < 0 || minimumScore > 1)
        throw new ArgumentException("Invalid retrieval settings.");

    var allowed = await authorization.AuthorizeAsync(
        user, null, "Knowledge.Read");
    if (!allowed.Succeeded) throw new UnauthorizedAccessException();

    // Scope in SQL before loading any chunk text.
    var chunks = await db.Set<KnowledgeChunk>().AsNoTracking()
        .Where(c => c.OrganizationId == trustedOrganizationId)
        .ToListAsync(ct);

    if (chunks.Count == 0) return [];

    var vector = await embeddings.EmbedAsync(
        query, EmbeddingPurpose.Query, ct);
    var terms = HybridRank.Tokens(query);

    return chunks.Select(c => new RankedChunk(c,
            HybridRank.Score(c, terms, vector, embeddings.SpaceKey)))
        .Where(x => x.Score > 0 && x.Score >= minimumScore)
        .OrderByDescending(x => x.Score).ThenBy(x => x.Chunk.Id)
        .Take(limit).ToArray();
}

user and trustedOrganizationId must come from the same authenticated server context, including when a model invokes SearchKnowledgeBase. Neither belongs in the model's tool arguments, and authorization failures should map to appropriate HTTP responses.

I retain the application's tenant query filters as defense in depth. EF Core global filters can apply tenant conditions automatically, but they can be bypassed deliberately, so custom SQL paths need their own tenant predicates. Any future full-text or vector candidate query needs the same scope before results leave the database.

This example grants knowledge access at tenant-and-role level. When an existing application has document-specific permissions, I add those grants to the SQL predicate before materialization, not after ranking. I've written more about AI integration for existing .NET applications.

Passing retrieved chunks with source references

I pass selected passages as the SearchKnowledgeBase tool result, preserving document identity, chunk identity, source, and text. Request-local labels such as S1 map to those server-selected records; the model must not invent source URLs.

public static string BuildEvidence(IReadOnlyList<RankedChunk> hits) =>
    JsonSerializer.Serialize(new
    {
        sources = hits.Select((hit, index) => new
        {
            sourceId = $"S{index + 1}",
            documentId = hit.Chunk.DocumentId,
            chunkId = hit.Chunk.Id,
            source = hit.Chunk.Source,
            text = hit.Chunk.Content
        })
    });

The tool executor returns that JSON using the provider's native tool-result format before requesting the draft. I would not concatenate document content into the system instructions.

The model instructions require policy claims to cite those labels, treat document text as evidence rather than commands, and acknowledge missing evidence. An empty result should produce an honest limitation, not a policy assembled from general knowledge. In production, I also validate returned labels against the retrieved set and enforce a token budget that leaves room for instructions, conversation history, and the answer.

Retrieved documents can contain prompt-injection instructions. Delimiters help organize context, but they are not an authorization boundary. Tool permissions stay enforced in .NET regardless of what retrieved text says.

Limits and lessons learned

JSON storage is simple; scanning has a ceiling

The sample loads every authorized chunk and calculates similarity in application memory. Its comparison work grows with candidate count and vector dimensions, and JSON adds transfer and deserialization overhead. This is not indexed vector search. I would measure retrieval separately from generation before choosing a different search backend, rather than hiding scaling problems by ranking an arbitrary subset.

Retrieval quality and operational reliability are separate

Overlap can return near-duplicates, simple token matching lacks stemming and stopword handling, and a relevant passage can still be summarized incorrectly. Human review remains necessary.

For production, I add bounded retries, ingestion batching, cancellation checks during ranking, and explicit provider-outage behavior. The sample fails on provider errors rather than pretending a semantic search succeeded. Local Ollama avoids a hosted embedding request but still needs hardware and operational care, and a hosted provider requires an explicit decision about which document text may leave the application environment.

My evaluation set includes expected-source questions, unknown-answer questions, cross-tenant access attempts, and denied-role requests.

Conclusion

My approach to RAG ASP.NET Core work is to keep retrieval inside the application's existing rules: authorized services for live records, permission-scoped document chunks, compatible embeddings, and inspectable hybrid ranking.

The model receives limited evidence and produces a draft. It does not become the database, the permission system, or the person responsible for sending the reply.

I'm Rahmat Afridi, a senior .NET developer who adds AI to existing business applications. The full working example is in SupportAgent.NET and on GitHub.