Entity Framework Monitoring ?????????????????????
Entity Framework (EF) Core ???????????? ORM (Object-Relational Mapper) ????????????????????? .NET ????????????????????? developers ???????????????????????? database ???????????? C# objects ????????????????????????????????????????????? SQL ?????????????????? ????????? EF Core ???????????????????????? queries ????????????????????????????????????????????????????????????????????????????????????????????? ???????????? N+1 queries, missing indexes, large result sets
Monitoring EF Core ?????????????????????????????? ????????????????????? slow queries ??????????????????????????? users, ?????? N+1 query problems, ???????????? connection pool exhaustion, ????????? query execution time, ?????????????????? database load ????????? application, Alert ??????????????? performance degradation
???????????????????????????????????????????????? EF Core built-in diagnostics (DiagnosticListener, LoggerFactory), Application Insights / OpenTelemetry, Prometheus + Grafana, Custom middleware ?????????????????? query tracking
????????????????????? EF Core Diagnostics
Configure EF Core ?????????????????? monitoring
// === EF Core Diagnostics Setup ===
// 1. Program.cs ??? Configure DbContext with logging
// Install packages:
// dotnet add package Microsoft.EntityFrameworkCore.SqlServer
// dotnet add package OpenTelemetry.Exporter.Prometheus
// dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Diagnostics;
using System.Diagnostics;
var builder = WebApplication.CreateBuilder(args);
// Configure DbContext with diagnostics
builder.Services.AddDbContext<AppDbContext>(options =>
{
options.UseNpgsql(builder.Configuration.GetConnectionString("Default"))
.EnableSensitiveDataLogging(builder.Environment.IsDevelopment())
.EnableDetailedErrors(builder.Environment.IsDevelopment())
.LogTo(Console.WriteLine, new[] { DbLoggerCategory.Database.Command.Name },
LogLevel.Information)
.AddInterceptors(new SlowQueryInterceptor());
});
// 2. SlowQueryInterceptor.cs
public class SlowQueryInterceptor : DbCommandInterceptor
{
private readonly TimeSpan _threshold = TimeSpan.FromMilliseconds(500);
public override DbDataReader ReaderExecuted(
DbCommand command,
CommandExecutedEventData eventData,
DbDataReader result)
{
if (eventData.Duration > _threshold)
{
var logger = LoggerFactory.Create(b => b.AddConsole())
.CreateLogger("SlowQuery");
logger.LogWarning(
"Slow query detected ({Duration}ms): {CommandText}",
eventData.Duration.TotalMilliseconds,
command.CommandText);
}
return result;
}
public override ValueTask<DbDataReader> ReaderExecutedAsync(
DbCommand command,
CommandExecutedEventData eventData,
DbDataReader result,
CancellationToken cancellationToken = default)
{
if (eventData.Duration > _threshold)
{
Console.WriteLine($"[SLOW QUERY] {eventData.Duration.TotalMilliseconds}ms: " +
$"{command.CommandText[..Math.Min(200, command.CommandText.Length)]}");
}
return ValueTask.FromResult(result);
}
}
// 3. AppDbContext.cs
public class AppDbContext : DbContext
{
public DbSet<Product> Products { get; set; }
public DbSet<Order> Orders { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder options)
{
// Add query tags for tracking
options.AddInterceptors(new TaggedQueryInterceptor());
}
}
// 4. Health Check
builder.Services.AddHealthChecks()
.AddDbContextCheck<AppDbContext>("database");
Console.WriteLine("EF Core diagnostics configured");??????????????? Performance Monitoring System
C# monitoring system ?????????????????? EF Core
// === EF Core Performance Monitor ===
using System.Collections.Concurrent;
using System.Diagnostics;
using System.Diagnostics.Metrics;
// 1. Query Metrics Collector
public class EfCoreMetricsCollector
{
private static readonly Meter Meter = new("EfCore.Monitoring", "1.0");
private static readonly Counter<long> QueryCount =
Meter.CreateCounter<long>("efcore.queries.total", "queries");
private static readonly Histogram<double> QueryDuration =
Meter.CreateHistogram<double>("efcore.query.duration", "ms");
private static readonly Counter<long> SlowQueryCount =
Meter.CreateCounter<long>("efcore.queries.slow", "queries");
private static readonly Counter<long> ErrorCount =
Meter.CreateCounter<long>("efcore.queries.errors", "queries");
private readonly ConcurrentDictionary<string, QueryStats> _queryStats = new();
public void RecordQuery(string queryHash, double durationMs, bool isError = false)
{
QueryCount.Add(1, new KeyValuePair<string, object?>("type", "select"));
QueryDuration.Record(durationMs);
if (durationMs > 500)
SlowQueryCount.Add(1);
if (isError)
ErrorCount.Add(1);
_queryStats.AddOrUpdate(queryHash,
new QueryStats { Count = 1, TotalMs = durationMs, MaxMs = durationMs },
(_, stats) =>
{
stats.Count++;
stats.TotalMs += durationMs;
if (durationMs > stats.MaxMs) stats.MaxMs = durationMs;
return stats;
});
}
public IEnumerable<QueryReport> GetTopQueries(int top = 10)
{
return _queryStats
.OrderByDescending(q => q.Value.TotalMs)
.Take(top)
.Select(q => new QueryReport
{
QueryHash = q.Key,
ExecutionCount = q.Value.Count,
TotalMs = q.Value.TotalMs,
AvgMs = q.Value.TotalMs / q.Value.Count,
MaxMs = q.Value.MaxMs,
});
}
}
public class QueryStats
{
public long Count { get; set; }
public double TotalMs { get; set; }
public double MaxMs { get; set; }
}
public class QueryReport
{
public string QueryHash { get; set; } = "";
public long ExecutionCount { get; set; }
public double TotalMs { get; set; }
public double AvgMs { get; set; }
public double MaxMs { get; set; }
}
// 2. N+1 Query Detector
public class NPlus1Detector
{
private readonly ConcurrentDictionary<string, int> _requestQueries = new();
private readonly int _threshold;
public NPlus1Detector(int threshold = 10)
{
_threshold = threshold;
}
public void TrackQuery(string requestId, string tableName)
{
var key = $"{requestId}:{tableName}";
_requestQueries.AddOrUpdate(key, 1, (_, count) => count + 1);
if (_requestQueries.TryGetValue(key, out int count) && count >= _threshold)
{
Console.WriteLine(
$"[N+1 DETECTED] Request {requestId}: " +
$"{count} queries to {tableName}. Use Include() or projection.");
}
}
}
// 3. Connection Pool Monitor
public class ConnectionPoolMonitor
{
public ConnectionPoolStatus GetStatus()
{
return new ConnectionPoolStatus
{
MaxPoolSize = 100,
ActiveConnections = 45,
IdleConnections = 20,
WaitingRequests = 0,
UtilizationPct = 45.0,
};
}
}
public class ConnectionPoolStatus
{
public int MaxPoolSize { get; set; }
public int ActiveConnections { get; set; }
public int IdleConnections { get; set; }
public int WaitingRequests { get; set; }
public double UtilizationPct { get; set; }
}
Console.WriteLine("EF Core monitoring system ready");Query Performance Analysis
???????????????????????????????????????????????????????????? queries
// === Query Performance Analysis ===
// 1. Query Analyzer Middleware
public class QueryAnalyzerMiddleware
{
private readonly RequestDelegate _next;
private readonly EfCoreMetricsCollector _metrics;
public QueryAnalyzerMiddleware(RequestDelegate next, EfCoreMetricsCollector metrics)
{
_next = next;
_metrics = metrics;
}
public async Task InvokeAsync(HttpContext context)
{
var sw = Stopwatch.StartNew();
var requestId = context.TraceIdentifier;
// Track queries per request
context.Items["QueryCount"] = 0;
context.Items["TotalQueryMs"] = 0.0;
await _next(context);
sw.Stop();
var queryCount = (int)(context.Items["QueryCount"] ?? 0);
var totalQueryMs = (double)(context.Items["TotalQueryMs"] ?? 0.0);
if (queryCount > 20)
{
Console.WriteLine(
$"[HIGH QUERY COUNT] {context.Request.Path}: " +
$"{queryCount} queries in {sw.ElapsedMilliseconds}ms " +
$"(DB time: {totalQueryMs:F0}ms)");
}
}
}
// 2. Common EF Core Performance Issues
/*
Issue 1: N+1 Queries
BAD:
var orders = context.Orders.ToList();
foreach (var order in orders)
{
var items = order.Items; // Lazy load = 1 query per order!
}
GOOD:
var orders = context.Orders
.Include(o => o.Items)
.ToList();
Issue 2: Loading Too Much Data
BAD:
var products = context.Products.ToList(); // Loads ALL columns
GOOD:
var products = context.Products
.Select(p => new { p.Id, p.Name, p.Price })
.ToList();
Issue 3: Missing AsNoTracking
BAD:
var products = context.Products.ToList(); // Tracked
GOOD:
var products = context.Products
.AsNoTracking()
.ToList(); // 30-50% faster for read-only
Issue 4: Client-Side Evaluation
BAD:
var results = context.Products
.Where(p => MyCustomMethod(p.Name)) // Runs on client!
.ToList();
GOOD:
var results = context.Products
.Where(p => p.Name.Contains("search"))
.ToList();
*/
// 3. Automated Query Review
public class QueryReviewer
{
public List<QueryIssue> AnalyzeQueries(List<QueryLog> logs)
{
var issues = new List<QueryIssue>();
// Detect N+1
var grouped = logs.GroupBy(l => l.RequestId);
foreach (var group in grouped)
{
var tableQueries = group.GroupBy(q => q.TableName);
foreach (var tq in tableQueries)
{
if (tq.Count() > 10)
{
issues.Add(new QueryIssue
{
Type = "N+1",
Severity = "HIGH",
Message = $"N+1 detected: {tq.Count()} queries to {tq.Key}",
Fix = "Use .Include() or projection with .Select()",
});
}
}
}
// Detect slow queries
foreach (var log in logs.Where(l => l.DurationMs > 1000))
{
issues.Add(new QueryIssue
{
Type = "SlowQuery",
Severity = "MEDIUM",
Message = $"Slow query: {log.DurationMs}ms",
Fix = "Add index or optimize query",
});
}
return issues;
}
}
public class QueryLog
{
public string RequestId { get; set; } = "";
public string TableName { get; set; } = "";
public double DurationMs { get; set; }
}
public class QueryIssue
{
public string Type { get; set; } = "";
public string Severity { get; set; } = "";
public string Message { get; set; } = "";
public string Fix { get; set; } = "";
}
Console.WriteLine("Query analysis configured");Alerting ????????? Notification
????????????????????? alerts ?????????????????? database issues
# === Alerting Configuration ===
# 1. Prometheus Alert Rules
cat > prometheus/ef_core_alerts.yml << 'EOF'
groups:
- name: ef_core_alerts
rules:
- alert: HighQueryLatency
expr: histogram_quantile(0.99, rate(efcore_query_duration_bucket[5m])) > 2000
for: 5m
labels:
severity: warning
annotations:
summary: "EF Core P99 query latency > 2s"
description: "P99 query latency is {{ $value }}ms"
- alert: SlowQuerySpike
expr: rate(efcore_queries_slow[5m]) > 0.1
for: 2m
labels:
severity: warning
annotations:
summary: "Slow query rate increasing"
- alert: DatabaseErrors
expr: rate(efcore_queries_errors[5m]) > 0.01
for: 1m
labels:
severity: critical
annotations:
summary: "Database errors detected"
- alert: ConnectionPoolExhaustion
expr: efcore_connection_pool_utilization > 85
for: 5m
labels:
severity: critical
annotations:
summary: "Connection pool usage > 85%"
description: "Active: {{ $value }}%. Risk of connection timeout."
- alert: HighQueryCount
expr: rate(efcore_queries_total[1m]) > 1000
for: 5m
labels:
severity: warning
annotations:
summary: "Query rate > 1000/min"
EOF
# 2. Grafana Dashboard
cat > grafana/ef_core_dashboard.json << 'EOF'
{
"dashboard": {
"title": "EF Core Performance",
"panels": [
{
"title": "Query Rate",
"type": "graph",
"targets": [{"expr": "rate(efcore_queries_total[5m])"}]
},
{
"title": "Query Latency (P50/P95/P99)",
"type": "graph",
"targets": [
{"expr": "histogram_quantile(0.50, rate(efcore_query_duration_bucket[5m]))"},
{"expr": "histogram_quantile(0.95, rate(efcore_query_duration_bucket[5m]))"},
{"expr": "histogram_quantile(0.99, rate(efcore_query_duration_bucket[5m]))"}
]
},
{
"title": "Connection Pool",
"type": "gauge",
"targets": [{"expr": "efcore_connection_pool_utilization"}]
},
{
"title": "Slow Queries",
"type": "stat",
"targets": [{"expr": "rate(efcore_queries_slow[5m])"}]
}
]
}
}
EOF
echo "Alerting and dashboard configured"Dashboard ????????? Observability
??????????????? observability ?????????????????? EF Core
// === OpenTelemetry Integration ===
// Program.cs ??? Add OpenTelemetry
// dotnet add package OpenTelemetry.Extensions.Hosting
// dotnet add package OpenTelemetry.Instrumentation.EntityFrameworkCore
// dotnet add package OpenTelemetry.Exporter.Prometheus.AspNetCore
/*
builder.Services.AddOpenTelemetry()
.WithMetrics(metrics =>
{
metrics
.AddAspNetCoreInstrumentation()
.AddRuntimeInstrumentation()
.AddMeter("EfCore.Monitoring")
.AddPrometheusExporter();
})
.WithTracing(tracing =>
{
tracing
.AddAspNetCoreInstrumentation()
.AddEntityFrameworkCoreInstrumentation(options =>
{
options.SetDbStatementForText = true;
options.SetDbStatementForStoredProcedure = true;
})
.AddOtlpExporter();
});
*/
// Dashboard Data Provider
public class EfCoreDashboard
{
public DashboardData GetDashboard()
{
return new DashboardData
{
QueryMetrics = new QueryMetrics
{
TotalQueries1h = 45000,
AvgLatencyMs = 12.5,
P95LatencyMs = 45.2,
P99LatencyMs = 125.8,
SlowQueries1h = 23,
ErrorRate = 0.02,
},
ConnectionPool = new ConnectionPoolData
{
MaxSize = 100,
Active = 35,
Idle = 25,
Utilization = 35.0,
},
TopSlowQueries = new List<SlowQueryData>
{
new() { Query = "SELECT * FROM Orders WHERE...", AvgMs = 850, Count = 15 },
new() { Query = "SELECT * FROM Products JOIN...", AvgMs = 520, Count = 45 },
},
Alerts = new List<AlertData>
{
new() { Level = "WARNING", Message = "N+1 detected on /api/orders", Time = "5m ago" },
new() { Level = "INFO", Message = "Connection pool usage peaked at 72%", Time = "1h ago" },
},
};
}
}
public class DashboardData
{
public QueryMetrics QueryMetrics { get; set; } = new();
public ConnectionPoolData ConnectionPool { get; set; } = new();
public List<SlowQueryData> TopSlowQueries { get; set; } = new();
public List<AlertData> Alerts { get; set; } = new();
}
public class QueryMetrics
{
public long TotalQueries1h { get; set; }
public double AvgLatencyMs { get; set; }
public double P95LatencyMs { get; set; }
public double P99LatencyMs { get; set; }
public int SlowQueries1h { get; set; }
public double ErrorRate { get; set; }
}
public class ConnectionPoolData
{
public int MaxSize { get; set; }
public int Active { get; set; }
public int Idle { get; set; }
public double Utilization { get; set; }
}
public class SlowQueryData
{
public string Query { get; set; } = "";
public double AvgMs { get; set; }
public int Count { get; set; }
}
public class AlertData
{
public string Level { get; set; } = "";
public string Message { get; set; } = "";
public string Time { get; set; } = "";
}
Console.WriteLine("Observability configured");FAQ ??????????????????????????????????????????
Q: N+1 Query ?????? EF Core ???????????????????????????????????????????
A: N+1 ??????????????????????????? query 1 ???????????????????????? list ???????????? loop query ????????????????????? row (1 + N queries) ????????????????????????????????? ???????????? EF Core logging ?????? SQL ????????? generate ????????????????????? query ???????????????????????????????????? = N+1, ????????? DiagnosticListener ????????? queries per request ?????????????????????????????? 10-20 = suspect, ????????? MiniProfiler ???????????? queries per page, ??????????????? custom interceptor log query count per request ????????????????????? ????????? Include() ?????????????????? eager loading (.Include(o => o.Items)), ????????? Select() projection ???????????????????????? fields ?????????????????????, ????????? AsSplitQuery() ?????????????????? complex includes, ????????? batch loading patterns
Q: Connection Pool Exhaustion ???????????????????????????????
A: Connection Pool Exhaustion ???????????????????????????????????? connections ??????????????????????????? requests ???????????????????????? wait ?????????????????????????????? DbContext ?????????????????? dispose (memory leak), Long-running transactions lock connections, Connection string ??????????????? timeout/pool size, Concurrent requests ????????????????????? pool size ????????????????????? ????????? using statement ???????????? DI lifetime (AddDbContext = scoped by default), ???????????? Max Pool Size ?????? connection string (default 100), ???????????? Connection Timeout, ???????????? DbContext lifetime ?????????????????? singleton, Monitor pool usage ???????????? metrics alert ????????? 80% ???????????????????????? ?????? active connections ?????? database (pg_stat_activity ?????????????????? PostgreSQL)
Q: EF Core ????????? Dapper ??????????????????????????????????
A: EF Core ???????????? full ORM ?????? change tracking, migrations, LINQ, relationships management ?????????????????????????????? CRUD operations development speed ???????????? ????????? overhead ????????????????????? (tracking, query translation) ??????????????? business applications ????????? CRUD ???????????????????????? Dapper ???????????? micro-ORM ??????????????? SQL ???????????? ???????????????????????? EF Core 2-5x ?????????????????? read operations ??????????????? change tracking ??????????????????????????? SQL ????????? ??????????????? read-heavy applications, reporting, complex queries ??????????????? ?????????????????????????????? EF Core ?????????????????? write operations (CRUD, transactions), Dapper ?????????????????? complex read queries ????????????????????? performance ?????????
Q: OpenTelemetry ????????? Application Insights ??????????????????????????????????
A: Application Insights ???????????? Azure-native APM setup ??????????????????????????????????????? .NET (1 NuGet package), auto-instrumentation ?????????????????? EF Core, SQL, HTTP, built-in dashboards ????????? alerting ????????? lock-in ????????? Azure ????????????????????? data volume OpenTelemetry ???????????? open standard vendor-neutral ????????? data ?????? backend ???????????????????????? (Jaeger, Grafana, Datadog), community-driven, ????????? lock-in ????????? setup ????????????????????????????????? ???????????? maintain backend ????????? ?????????????????? .NET on Azure ??????????????? Application Insights (?????????????????????) ?????????????????? multi-cloud ???????????? vendor independence ??????????????? OpenTelemetry ????????????????????? compatible ????????? Application Insights ?????????????????? OpenTelemetry protocol
