How would you design a high-performance ASP.NET Core + SQL Server data-access layer?

Start with clear transactional boundaries and database-enforced integrity. Use appropriate primary/foreign keys, constraints, data types, and indexes based on real query patterns.

In EF Core, keep DbContext scoped appropriately, use async database operations for scalable I/O, project only required columns, use AsNoTracking for read-only entity queries when appropriate, avoid N+1 patterns, paginate large datasets, and inspect generated SQL for important paths.

Use parameterized queries, least-privilege database credentials, optimistic concurrency where business workflows require it, and short transactions. Use stored procedures or raw SQL selectively when they provide a measurable or architectural advantage.

For production operation, enable useful observability: structured application logging, request tracing, Query Store, database metrics, blocking/deadlock monitoring, backup verification, and performance baselines.

Most importantly, performance should be measured end-to-end. A well-designed data-access layer balances correctness, security, maintainability, database efficiency, concurrency, and operational reliability rather than optimizing one layer in isolation.