1# SQL Database — C# (.NET) — REFERENCE ONLY
2 
3## Entity Framework Core Setup
4 
5Add EF Core with Azure SQL and managed identity support to an ASP.NET Core app.
6 
7### NuGet Packages
8 
9```xml
10<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="8.0.*" />
11<PackageReference Include="Azure.Identity" Version="1.13.*" />
12<PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="8.0.*" />
13```
14 
15### DbContext
16 
17Create `Data/AppDbContext.cs`:
18 
19```csharp
20using Microsoft.EntityFrameworkCore;
21 
22namespace MyApp.Data;
23 
24public class AppDbContext : DbContext
25{
26    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }
27 
28    public DbSet<TodoItem> TodoItems => Set<TodoItem>();
29}
30 
31public class TodoItem
32{
33    public int Id { get; set; }
34    public string Title { get; set; } = string.Empty;
35    public bool IsComplete { get; set; }
36}
37```
38 
39### Program.cs (additions)
40 
41Add these lines to `Program.cs` — do NOT replace the file:
42 
43```csharp
44using Microsoft.EntityFrameworkCore;
45using MyApp.Data;
46 
47// Add after builder creation
48var connectionString = builder.Configuration.GetConnectionString("AZURE_SQL")
49    ?? builder.Configuration["AZURE_SQL_CONNECTION_STRING"];
50 
51builder.Services.AddDbContext<AppDbContext>(options =>
52    options.UseSqlServer(connectionString));
53 
54// Add health check for SQL
55builder.Services.AddHealthChecks()
56    .AddDbContextCheck<AppDbContext>();
57 
58// After app build — map health checks so App Service probes reflect SQL connectivity
59app.MapHealthChecks("/health");
60```
61 
62### API Endpoints
63 
64Add to `Program.cs` after `app` is built:
65 
66```csharp
67app.MapGet("/api/todos", async (AppDbContext db) =>
68    await db.TodoItems.ToListAsync());
69 
70app.MapGet("/api/todos/{id}", async (int id, AppDbContext db) =>
71    await db.TodoItems.FindAsync(id) is TodoItem todo
72        ? Results.Ok(todo)
73        : Results.NotFound());
74 
75app.MapPost("/api/todos", async (TodoItem todo, AppDbContext db) =>
76{
77    db.TodoItems.Add(todo);
78    await db.SaveChangesAsync();
79    return Results.Created($"/api/todos/{todo.Id}", todo);
80});
81```
82 
83### appsettings.json
84 
85```json
86{
87  "ConnectionStrings": {
88    "AZURE_SQL": "Server=localhost;Database=myapp;Trusted_Connection=true;"
89  }
90}
91```
92 
93> In production, the `AZURE_SQL_CONNECTION_STRING` app setting from Azure overrides this with the managed identity connection string.
94 
95### EF Migrations (postprovision hook)
96 
97Create `infra/scripts/setup-db.sh`:
98 
99```bash
100#!/bin/bash
101dotnet ef database update --project src/api
102```
103 
104## Files to Add
105 
106| File | Action |
107|------|--------|
108| `Data/AppDbContext.cs` | Create — DbContext + entity models |
109| `Program.cs` | Modify — add DbContext registration + endpoints |
110| `appsettings.json` | Modify — add ConnectionStrings section |
111 
112## Common Patterns
113 
114- Always use `AddHealthChecks().AddDbContextCheck<>()` for SQL health monitoring
115- Use `AsNoTracking()` for read-only queries to improve performance
116- Apply migrations via postprovision hook, not at app startup
117