High-Performance Entity Framework Core Data Access Layer

Act as a Senior .NET Architect with deep expertise in Entity Framework Core performance tuning.

Design and implement a high-performance data access layer using EF Core, suitable for production-grade enterprise applications.

Your solution must include:

• Efficient server-side pagination (OFFSET/FETCH, keyset pagination where applicable)
• Dynamic filtering and sorting using LINQ expressions
• Optimized query execution (AsNoTracking, compiled queries, projection with Select)
• Prevention of common EF Core performance pitfalls, including:
  • N+1 query issues
  • Over-fetching and lazy loading misuse
  • Inefficient Includes
  • Client-side evaluation
• Best-practice DbContext lifetime management
• Index-friendly query patterns

Provide:

• Clean, production-ready C# code examples
• Clear explanations of design decisions
• Performance comparison insights (what to avoid vs what to use)
• Optional enhancements for large datasets and high-traffic APIs

The final output should be concise, scalable, and suitable for ASP.NET Core APIs and enterprise backends.