Modern applications are rapidly evolving toward more intuitive user experiences. One powerful capability is allowing users to interact with systems using natural language instead of complex queries.
In this blog, we’ll build a backend API using .NET 8, Entity Framework Core, and Azure OpenAI that converts user prompts into SQL queries and executes them against a database.
Imagine asking:
“Get all customers who placed orders in the last 30 days.”
…and your system automatically converts that into SQL and returns results.
What is Azure OpenAI?
Azure OpenAI is a cloud service by Microsoft that provides access to powerful AI models like GPT in a secure and scalable environment.
It enables developers to:
Generate text and code
Build chat-based applications
Automate workflows
Create intelligent APIs (like our SQL generator)
Step 1: Create an Azure OpenAI Resource
In the Azure Portal:
- Click Create a resource
- Search for Azure OpenAI
- Fill in:
- Resource group
- Region (e.g., East US)
- Name
- Click Create
Step 2: Deploy a Model
After resource creation:
- Go to Model deployments
- Click Create
- Choose model:
gpt-4o(recommended)
- Set deployment name (e.g.,
gpt-4o) - Deploy
Step 3: Install Required Packages
dotnet add package OpenAI
dotnet add package Microsoft.EntityFrameworkCore
dotnet add package Microsoft.EntityFrameworkCore.SqlServer
Step 4: Configure Azure OpenAI
{
"AzureOpenAI": {
"Endpoint": "https://your-resource.openai.azure.com/",
"ApiKey": "your-api-key",
"DeploymentName": "gpt-4o"
}
}
Step 5: Create OpenAI Service
using OpenAI;
using OpenAI.Chat;
using Azure;
public class OpenAiService
{
private readonly ChatClient _chatClient;
public OpenAiService(IConfiguration config)
{
var client = new AzureOpenAIClient(
new Uri(config["AzureOpenAI:Endpoint"]),
new AzureKeyCredential(config["AzureOpenAI:ApiKey"])
);
_chatClient = client.GetChatClient(config["AzureOpenAI:DeploymentName"]);
}
public async Task<string> GenerateSqlAsync(string prompt, string schema)
{
var systemPrompt = $@"
You are a SQL generator.
Only generate SELECT queries.
Do not modify data.
Schema:
{schema}";
var response = await _chatClient.CompleteChatAsync(
new ChatMessage[]
{
new SystemChatMessage(systemPrompt),
new UserChatMessage(prompt)
});
return response.Value.Content[0].Text;
}
}
Step 6: Provide Database Schema
AI needs context to generate accurate SQL:
var schema = @"
Table: Customers(Id, Name, Email)
Table: Orders(Id, CustomerId, OrderDate, TotalAmount)
";
Step 7: Validate SQL (Critical Step)
Never execute AI-generated SQL directly.
public bool IsSafeQuery(string sql)
{
var forbidden = new[] { "INSERT", "UPDATE", "DELETE", "DROP", "ALTER" };
return !forbidden.Any(f => sql.ToUpper().Contains(f));
}
Step 8: Execute Query
var result = await context
.Database
.SqlQueryRaw<dynamic>(sql)
.ToListAsync();
Step 9: Build API Endpoint
[HttpPost]
public async Task<IActionResult> ExecuteQuery([FromBody] string prompt)
{
// var schema = "..."; // you can provide schema here or
// or
var schema = GetSchemaFromDb(); // Provide all the tables
var sql = await _aiService.GenerateSqlAsync(prompt, schema);
if (!_queryService.IsSafeQuery(sql))
return BadRequest("Unsafe query");
var result = await _queryService.ExecuteQueryAsync(sql);
return Ok(new { sql, result });
}
private Dictionary<string, List<string>> GetSchemaFromDb()
{
var schema = new Dictionary<string, List<string>>();
var entityTypes = _appDbContext.Model.GetEntityTypes();
foreach (var entity in entityTypes)
{
var tableName = entity.GetTableName();
tableName = tableName ?? string.Empty;
var columns = entity
.GetProperties()
.Select(p => p.Name)
.ToList();
schema[tableName] = columns;
}
return schema;
}
Conclusion
By combining .NET 8, Entity Framework Core, and Azure OpenAI, you can build intelligent
APIs that translate human language into actionable database queries.
However, with great power comes responsibility—always validate and
secure AI-generated outputs before execution.
