Building a 100% Local Text-to-SQL Agent with .NET 10, EF Core and LLamaSharp

Leveraging EF Core metadata and idle Azure P3v3 App Service RAM for zero-cost, privacy-first SQL generation 07/17/2026

During my recent mission for a corporate client, I was tasked with building a Proof of Concept (POC) for a Text-to-SQL Agent: allowing business analysts to query internal financial and operational databases using natural language. Think of questions like: "Give me the top 5 clients by total invoiced amount this year" or "How many invoices are currently in 'Paid' status?"

While building Text-to-SQL with public commercial LLM APIs (like standard OpenAI or Anthropic endpoints) is straightforward, enterprise realities quickly kicked in: calling external public endpoints with internal financial schemas was an immediate compliance and GDPR veto.

Now, you might ask: why not use enterprise private AI platforms like Microsoft Azure AI Foundry (formerly Azure OpenAI Service) or Amazon Bedrock? These managed platforms do guarantee data privacy, private networking, and tenant isolation within your cloud perimeter. However, in our client's current landscape, no dedicated enterprise AI platform or governance landing zone had been set up yet. Going through security accreditations, subscription governance, private endpoint setup, and procurement for Azure AI Foundry or AWS Bedrock can take months in a large corporate organization.

Since the business wanted a rapid, tangible Proof of Concept without waiting for an organization-wide AI cloud onboarding process, we opted for a 100% local, self-hosted approach: running the model directly in-process within our existing application stack, with zero external infrastructure dependencies and zero additional cloud licensing costs.

In this article, I will explain how we achieved this by combining Entity Framework Core metadata with in-process local inference using LLamaSharp in .NET 10, and how taking advantage of existing Azure App Service P3v3 infrastructure allowed us to run this with zero extra cloud cost.

The Infrastructure Opportunity: Azure P3v3 Idle RAM

When developers consider running LLMs locally, the immediate objection is usually: "We don't have expensive GPU instances in our cloud budget."

However, looking closely at our production environment, our backend APIs were hosted on Azure App Service Plans on the P3v3 tier. A P3v3 instance provides:

  • 8 vCPUs (Intel Xeon / AMD EPYC high-frequency cores)
  • 32 GB of physical RAM

While our web APIs experienced occasional CPU spikes during business hours, over 20 GB of RAM was sitting completely idle! Quantization has become a mature, battle-tested standard in the ecosystem: with widely adopted 4-bit formats (such as standard GGUF Q4_K_M), specialized coding models like Qwen2.5-Coder-7B or lightweight models like Gemma-2-2B retain near-lossless accuracy on SQL tasks while fitting comfortably into just 1.5 GB to 5 GB of RAM.

Instead of provisioning dedicated GPU virtual machines or paying per-token API bills, we realized we could embed the model directly in-process within our ASP.NET Core application using CPU inference. It runs on hardware already paid for, guarantees that not a single byte of data leaves the server, and eliminates external network latency.

The Secret Weapon: EF Core as an AI Metamodel

Most Text-to-SQL tutorials instruct you to dump raw CREATE TABLE DDL scripts or query SQL Server system catalogs (sys.tables, sys.columns). This produces massive, noisy prompts full of database internals, while completely lacking high-level business context.

If your application already uses Entity Framework Core, you are sitting on a goldmine. Your DbContext already defines:

  • Clear business entity names and property types
  • Explicit table and column descriptions configured via .HasComment(...)
  • Primary and Foreign Key relationships navigated through strongly-typed models
  • C# Enums and their exact business values

Extracting Rich Metadata via IDesignTimeModel

In EF Core 8, 9, and 10, the runtime model is stripped of certain relational annotations for maximum performance. To retrieve comments and detailed relational metadata, we request the IDesignTimeModel service from the context: 


public class EfCoreSchemaGenerator : IEfCoreSchemaGenerator
{
    private readonly AppDbContext _dbContext;

    public EfCoreSchemaGenerator(AppDbContext dbContext)
    {
        _dbContext = dbContext;
    }

    public string GetSchemaForLlm()
    {
        var sb = new StringBuilder();
        // Access full metadata including relational comments
        var model = _dbContext.GetService<IDesignTimeModel>().Model;

        foreach (var entityType in model.GetEntityTypes())
        {
            var tableName = entityType.GetTableName();
            if (string.IsNullOrEmpty(tableName)) continue;

            sb.AppendLine($"Table: {tableName}");
            var comment = entityType.GetComment();
            if (!string.IsNullOrWhiteSpace(comment))
            {
                sb.AppendLine($"Description: {comment}");
            }

            sb.AppendLine("Columns:");
            foreach (var property in entityType.GetProperties())
            {
                var colName = property.GetColumnName();
                var colType = property.GetColumnType() ?? property.ClrType.Name;
                
                var flags = new List<string>();
                if (property.IsPrimaryKey()) flags.Add("PK");
                if (property.IsForeignKey()) flags.Add("FK");
                if (!property.IsNullable) flags.Add("NOT NULL");

                var desc = property.GetComment();
                var descStr = !string.IsNullOrWhiteSpace(desc) ? $" - {desc}" : "";
                var flagStr = flags.Count > 0 ? $" [{string.Join("] [", flags)}]" : "";

                sb.AppendLine($"  - {colName} ({colType}){flagStr}{descStr}");
            }
        }
        return sb.ToString();
    }
}

Solving the Enum Dilemma

One of the biggest failure points of Text-to-SQL agents is handling numeric status columns. If the user asks: "Find all paid invoices", how does the model know whether Status should be 1, 2, or 'PAID'?

By inspecting the CLR type of properties during schema generation, we automatically extract all enum integer values and format them into the prompt: 


var isEnum = property.ClrType.IsEnum || 
             (Nullable.GetUnderlyingType(property.ClrType)?.IsEnum == true);

if (isEnum)
{
    var enumType = property.ClrType.IsEnum 
        ? property.ClrType 
        : Nullable.GetUnderlyingType(property.ClrType)!;

    var values = Enum.GetValues(enumType)
        .Cast<object>()
        .Select(v => $"{Convert.ToInt32(v)} = {v}");

    sb.AppendLine($"Enum Values: {property.GetColumnName()}: {string.Join(", ", values)}");
}

The resulting prompt provided to the LLM is concise and unambiguous: 


Table: Invoices
Description: Invoices issued to corporate clients
Columns:
  - Id (INTEGER) [PK]
  - CustomerId (INTEGER) [FK] [NOT NULL] - Foreign key pointing to Customers.Id
  - TotalAmountTTC (TEXT) [NOT NULL] - Total amount including taxes in EUR
  - Status (INTEGER) [NOT NULL] - Payment status
Enum Values:
  - Status: 0 = Draft, 1 = Pending, 2 = Paid, 3 = Overdue, 4 = Cancelled
Relationships:
  - CustomerId -> Customers.Id

In-Process Local Inference with LLamaSharp

LLamaSharp is an exceptional open-source library providing high-performance C# P/Invoke bindings directly over llama.cpp. No Python runtime, no Docker sidecar, and no external daemon like Ollama is required. Everything runs inside your .NET host process.

Dependency Injection Architecture

Loading a GGUF model into memory takes a few seconds and consumes memory. We want to do this once at application startup: 


// Program.cs (.NET 10)
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlite("Data Source=app.db"));

// Singleton: Holds model weights and parameters in RAM
builder.Services.AddSingleton<ILlmModelManager, LlmModelManager>();

// Scoped services for per-request work
builder.Services.AddScoped<IEfCoreSchemaGenerator, EfCoreSchemaGenerator>();
builder.Services.AddScoped<ISqlExecutor, SqlExecutor>();
builder.Services.AddScoped<ILlmChatService, LlmChatService>();

Stateless Query Generation

Unlike a conversational chatbot where context accumulates over turns, SQL generation is an isolated task. We use StatelessExecutor to avoid keeping old tokens in the KV cache, ensuring fast inference: 


public async Task<string> GenerateSqlAsync(string question)
{
    var schema = _schemaGenerator.GetSchemaForLlm();
    var executor = new StatelessExecutor(_modelManager.Weights!, _modelManager.ModelParameters!);

    var inferenceParams = new InferenceParams
    {
        MaxTokens = 400,
        AntiPrompts = new List<string> { "<|im_end|>", "<end_of_turn>", "user:" }
    };

    var prompt = $@"<|im_start|>system
You are a SQL expert. Based strictly on the schema below, write a valid SQLite query to answer the question.
Output ONLY the raw SQL query. Do not wrap with markdown code fences (no ```sql).
Schema:
{schema}
<|im_end|>
<|im_start|>user
{question}
<|im_end|>
<|im_start|>assistant
";

    var sql = "";
    await foreach (var token in executor.InferAsync(prompt, inferenceParams))
    {
        sql += token;
    }

    return CleanSql(sql);
}

Safe Dynamic Execution with Dapper

Executing code or queries generated by an AI model requires strict guardrails. Even on a read-only database replica, defensive programming is mandatory.

Our execution pipeline enforces three safety layers:

  • Read-only statement enforcement: Queries must start with SELECT or WITH.
  • Destructive keyword blocker: Regex filtering blocks any query containing INSERT, UPDATE, DELETE, DROP, ALTER, TRUNCATE, or EXEC.
  • Dynamic Dictionary Projection: Dapper executes the query and returns IEnumerable<IDictionary<string, object>>, allowing any dynamic table structure to be serialized to JSON without creating static C# DTOs beforehand.

public async Task<SqlExecutionResult> ExecuteSelectQueryAsync(string sqlQuery)
{
    var trimmed = sqlQuery.Trim();

    if (!trimmed.StartsWith("SELECT", StringComparison.OrdinalIgnoreCase) &&
        !trimmed.StartsWith("WITH", StringComparison.OrdinalIgnoreCase))
    {
        throw new InvalidOperationException("Only SELECT queries are authorized.");
    }

    if (DangerousRegex.IsMatch(trimmed))
    {
        throw new InvalidOperationException("Destructive SQL keywords detected.");
    }

    var connection = _dbContext.Database.GetDbConnection();
    var results = await connection.QueryAsync(trimmed);

    return new SqlExecutionResult(results.Cast<IDictionary<string, object>>());
}

Open-Source Showcase: Try It Locally

To showcase this architecture, I created a clean, standalone repository that anyone can clone and run in minutes without having to configure an external database or cloud account:

  • Backend: .NET 10 Web API with SQLite and Bogus (pre-seeding 60 customers and 350 invoices).
  • Frontend: A lightweight React 19 + TypeScript interface with query chips, live tabular data rendering, and SQL syntax inspection.
  • Scripted Model Setup: A download-model.ps1 script to fetch Qwen2.5-Coder (1.5B or 7B) directly from Hugging Face.

👉 View Source Code on GitHub (yfrouin/TextToSql-EfCore-LocalLlm)

Key Takeaways & Future Evolutions

This project proved to our client that private, in-house AI agents are not only possible but practical on existing production hardware:

  • Zero extra cloud costs: By tapping into the unused RAM of our Azure P3v3 App Service instances, we avoided provisioning dedicated AI clusters.
  • Complete data confidentiality: Database schemas and queries remain entirely in-process and never leave the corporate boundary.
  • EF Core as an AI catalyst: Instead of treating ORMs and AI as separate worlds, leveraging the EF Core metadata graph gives local LLMs the exact semantic context they need to produce accurate SQL on the first attempt.

What's next? A V2 leveraging the Microsoft Agent Framework (and Semantic Kernel) is already in preparation! In an upcoming article, we will look into transitioning from this procedural pipeline to an autonomous agent workflow: using granular tool-calling (AIFunction) to inspect table schemas on demand, and introducing self-healing SQL queries where the agent captures database execution errors and self-corrects its syntax automatically. Stay tuned!

Share On