The Problem
Developers often need to populate dropdown lists dynamically from a database in ASP.NET Core applications. The challenge is to do this securely and efficiently, avoiding hardcoded connection strings and ensuring the solution is production-ready with Azure best practices.
Prerequisites
- .NET 8 SDK installed
- Azure CLI configured
- Azure SQL Database with Managed Identity enabled
- Visual Studio Code or IDE of choice
The Solution (Step-by-Step)
Step 1: Configure Azure Managed Identity and RBAC
Assign your App Service or Azure Function a System-Assigned Managed Identity. Then, grant the identity the Azure SQL Database Contributor role or a custom role with least privilege to access the database.
Step 2: Configure EF Core with DefaultAzureCredential
using Azure.Identity; // For DefaultAzureCredential
using Microsoft.EntityFrameworkCore;
var builder = WebApplication.CreateBuilder(args);
// Use DefaultAzureCredential for Managed Identity authentication
var credential = new DefaultAzureCredential();
var connectionString = builder.Configuration["AZURE_SQL_CONNECTION_STRING"];
builder.Services.AddDbContext<AppDbContext>(options =>
{
options.UseSqlServer(connectionString, sqlOptions =>
{
sqlOptions.EnableRetryOnFailure(); // Resiliency for transient faults
});
});
var app = builder.Build();
app.Run();
Explanation: We use DefaultAzureCredential to authenticate via Managed Identity, avoiding hardcoded secrets. The connection string should reference the server and database but omit credentials.
Step 3: Create the Model and DbContext
public class Category
{
public int Id { get; set; }
public string Name { get; set; } = string.Empty;
}
public class AppDbContext(DbContextOptions<AppDbContext> options) : DbContext(options)
{
public DbSet<Category> Categories => Set<Category>();
}
Explanation: The Category entity represents the dropdown data source.
Step 4: Implement the Controller Action
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.Rendering;
[ApiController]
[Route("api/[controller]")]
public class CategoriesController(AppDbContext context) : ControllerBase
{
[HttpGet("dropdown")]
public IActionResult GetDropdown()
{
// Fetch categories without tracking for performance
var categories = context.Categories.AsNoTracking()
.Select(c => new SelectListItem
{
Value = c.Id.ToString(),
Text = c.Name
}).ToList();
return Ok(categories);
}
}
Explanation: The action retrieves categories and maps them to SelectListItem for dropdown binding. AsNoTracking() improves performance for read-only queries.
Step 5: Bind in Razor View
@model YourViewModel
<select asp-for="SelectedCategoryId" asp-items="Model.Categories"></select>
Explanation: The Razor view uses asp-items to bind the dropdown list.
Best Practices & Security
- Use Managed Identity with
DefaultAzureCredentialinstead of storing credentials in code or configuration files. - Apply the Principle of Least Privilege by assigning only necessary RBAC roles like Azure SQL Database Contributor.
- Validate user input in controllers to prevent injection attacks.
- Use
AsNoTracking()for read-only queries to improve performance.
Summary
- Bind dropdown lists using EF Core and strongly typed models.
- Secure database access with Azure Managed Identity and RBAC roles.
- Follow best practices for performance and security in production.
