Connecting to a Database Using C# and ASP.NET with Entity Framework Core

Introduction

Most modern ASP.NET applications eventually need to persist and retrieve data from a relational database. Although it is possible to communicate directly with a database using ADO.NET, modern .NET applications commonly use an object-relational mapper (ORM) such as Entity Framework Core (EF Core) or a lightweight mapper such as Dapper.

EF Core allows developers to work with database records using strongly typed C# objects and LINQ rather than writing SQL for every operation. At the same time, a well-designed application should not allow database technology to dictate the structure of the entire application. SOLID principles, dependency injection (DI), separation of concerns and resilience policies are therefore just as important as the database technology itself.

This article demonstrates a typical architecture using ASP.NET Core, EF Core and SQL Server, while also explaining when Dapper may be a better choice.

1. The Database Access Architecture

A useful starting point is to separate the application into logical responsibilities:

• API/controller layer: handles HTTP requests and responses.
• Application/service layer: contains application-specific business workflows.
• Domain layer: contains domain models and business rules.
• Data-access layer: communicates with EF Core and the database.
• Database: persists application data.

The goal is not to create layers simply for the sake of creating layers. The important objective is to ensure that each component has a clear responsibility and that changes in one area do not unnecessarily affect the rest of the system.

2. Installing Entity Framework Core

For a SQL Server-backed ASP.NET Core application, the SQL Server EF Core provider can be added to the project. EF Core also provides command-line tooling for migrations and database development.

dotnet add package Microsoft.EntityFrameworkCore.SqlServer 
dotnet add package Microsoft.EntityFrameworkCore.Design

EF Core supports multiple database providers. The provider used by the application determines how EF Core translates its queries and communicates with the underlying database.

3. Creating the Entity Model

Suppose the application manages customers. A simple entity might look like this:

public class Customer 
{   
    public int Id { get; set; }
    public string FirstName { get; set; } = string.Empty;
    public string LastName { get; set; } = string.Empty;
    public string Email { get; set; } = string.Empty;
    public DateTime CreatedUtc { get; set; }
}
    

This class represents the application's model of a customer. EF Core maps the properties to columns in a database table.

For larger applications, explicit configuration can be placed in OnModelCreating or separate IEntityTypeConfiguration<T> classes:

public class CustomerConfiguration : IEntityTypeConfiguration<Customer> 
{ 
    public void Configure( EntityTypeBuilder<Customer> builder) 
    { 
        builder.HasKey(x => x.Id);

        builder.Property(x => x.FirstName)
            .HasMaxLength(100)
            .IsRequired();

        builder.Property(x => x.LastName)
            .HasMaxLength(100)
            .IsRequired();

        builder.Property(x => x.Email)
            .HasMaxLength(320)
            .IsRequired();

        builder.HasIndex(x => x.Email)
            .IsUnique();
    }
}

4. Creating the DbContext

The DbContext is EF Core's primary entry point for interacting with the database. It represents a unit of work and exposes DbSet<T> properties for entities that the application queries or modifies.

public class ApplicationDbContext : DbContext 
{ 
    public ApplicationDbContext( DbContextOptions<ApplicationDbContext> options) : base(options) { }
    public DbSet<Customer> Customers => Set<Customer>();

    protected override void OnModelCreating(
        ModelBuilder modelBuilder)
    {
        modelBuilder.ApplyConfigurationsFromAssembly(
            typeof(ApplicationDbContext).Assembly);
    }
}

5. Configuring the Database Connection

The connection string should normally be stored in ASP.NET Core configuration rather than hard-coded in application code.

For example, development configuration might contain:

{ "ConnectionStrings": { "DefaultConnection": "Server=localhost;Database=CustomerDb;Trusted_Connection=True;TrustServerCertificate=True" } }

Production applications should use an appropriate secret-management mechanism rather than committing credentials to source control.

Microsoft recommends using the most secure authentication mechanism available for deployed applications, particularly when connecting to managed cloud databases.

6. Dependency Injection and DbContext

ASP.NET Core has dependency injection built into the framework. EF Core integrates directly with this DI system through AddDbContext.

var connectionString = builder.Configuration.GetConnectionString("DefaultConnection") ?? throw new InvalidOperationException( "DefaultConnection was not configured.");

builder.Services.AddDbContext(options =>
options.UseSqlServer(connectionString));

AddDbContext registers the context as a scoped service by default. This fits the common ASP.NET Core model in which one HTTP request represents a unit of work.

The context can then be injected into an application service:

public class CustomerService 
{ 
    private readonly ApplicationDbContext _db;
    public CustomerService(ApplicationDbContext db)
    {
        _db = db;
    }
}

7. Why Dependency Injection Matters

Dependency injection is more than a convenient way of obtaining a DbContext. It is a fundamental architectural tool.

Instead of a service creating its own database implementation:

var db = new ApplicationDbContext(...);

the service receives what it needs from the DI container:

public CustomerService( ICustomerRepository customers) { _customers = customers; }

This reduces coupling and makes the service easier to test. A unit test can provide a fake or mock implementation of ICustomerRepository without requiring a real SQL Server connection.

8. SOLID Principles

SOLID principles provide a useful set of guidelines for structuring database-driven applications.

Single Responsibility Principle

A class should have one primary responsibility. A controller should not contain SQL, database transaction management, business rules and HTTP formatting all in the same class.

Instead:

Controller 
↓ 
Application Service 
↓ 
Repository / Data Access 
↓ 
EF Core 
↓ 
Database

Open/Closed Principle

Components should be open to extension without requiring unnecessary modification. Interfaces such as ICustomerRepository can allow the underlying implementation to evolve without forcing every caller to change.

Liskov Substitution Principle

An implementation of an abstraction should be usable wherever that abstraction is expected. If a repository interface promises predictable customer retrieval semantics, replacing its implementation should not unexpectedly change those semantics.

Interface Segregation Principle

Avoid giant interfaces containing unrelated database operations. Prefer focused abstractions where an application genuinely benefits from them.

Dependency Inversion Principle

Higher-level application logic should depend on abstractions rather than directly on infrastructure details. This is particularly valuable when database access is an infrastructure concern.

public interface ICustomerRepository 
{ 
    Task<Customer?> GetByIdAsync(int id, CancellationToken cancellationToken);
    Task<IReadOnlyList<Customer>> GetAllAsync(
    CancellationToken cancellationToken);
}

The application service depends on ICustomerRepository, while the infrastructure layer provides an EF Core implementation.

9. Querying with EF Core

EF Core allows database queries to be expressed using LINQ.

public async Task<Customer?> GetByIdAsync(int id, CancellationToken cancellationToken) 
{ 
    return await _db.Customers.AsNoTracking().SingleOrDefaultAsync(customer => customer.Id == id, cancellationToken); 
}

AsNoTracking() is useful for read-only queries because EF Core does not need to maintain change-tracking state for the returned entities.

Filtering can also be composed naturally:

var customers = await _db.Customers .AsNoTracking() .Where(x => x.LastName.StartsWith("Smith")) .OrderBy(x => x.LastName) .ThenBy(x => x.FirstName) .ToListAsync(cancellationToken);

10. Saving Data

Adding a new entity is straightforward:

var customer = new Customer { FirstName = "Jane", LastName = "Smith", Email = "[email protected]", CreatedUtc = DateTime.UtcNow };

_db.Customers.Add(customer);

await _db.SaveChangesAsync(cancellationToken);

EF Core tracks the entity and generates the appropriate INSERT statement when SaveChangesAsync() is called.

11. Migrations

EF Core migrations allow the application's database schema to evolve alongside the application's model.

dotnet ef migrations add InitialCreate dotnet ef database update

A migration records schema changes such as creating tables, adding columns or creating indexes.

In production, migrations should be treated as controlled deployment artifacts. Teams should consider their deployment strategy, database permissions, backward compatibility and rollback requirements rather than blindly updating production databases during application startup.

12. Building an ASP.NET Core API

A controller can remain relatively thin when database and application responsibilities have been separated:

[ApiController] [Route("api/customers")] 
public class CustomersController : ControllerBase 
{ 
    private readonly ICustomerService _service;

    public CustomersController(ICustomerService service)
    {
        _service = service;
    }

    [HttpGet("{id:int}")]
    public async Task<ActionResult<CustomerDto>> Get(
        int id,
        CancellationToken cancellationToken)
    {
        var customer = await _service.GetByIdAsync(
            id,
            cancellationToken);

        if (customer is null)
            return NotFound();

        return Ok(customer);
    }
}

The controller is concerned primarily with HTTP semantics. It does not need to know whether the data came from EF Core, Dapper or another persistence mechanism.

13. Resilience and Retry Policies

A database is an external dependency. Even a well-designed database can temporarily become unavailable because of network interruptions, transient infrastructure failures, failovers, throttling or other environmental conditions.

Resilience means designing the application so that appropriate transient failures do not immediately become user-visible application failures.

EF Core provides execution strategies for connection resiliency. For SQL Server, retry-on-failure can be enabled when configuring the context.

builder.Services.AddDbContext<ApplicationDbContext>(options => { options.UseSqlServer(connectionString, sqlOptions => { sqlOptions.EnableRetryOnFailure(maxRetryCount: 5, maxRetryDelay: TimeSpan.FromSeconds(10), errorNumbersToAdd: null); 
    });
});

The exact retry values should be based on the application's operational requirements. Retrying every failure is not automatically correct. Permanent errors, invalid SQL, constraint violations and authentication failures generally should not be treated as transient failures.

14. Resilience Is More Than "Retry Everything"

A retry policy should consider:

• Which failures are genuinely transient.
• How many retry attempts are appropriate.
• The maximum delay between attempts.
• Exponential backoff and, where appropriate, jitter.
• Application request timeouts.
• Database command timeouts.
• Cancellation tokens.
• Idempotency of the operation being retried.

For explicit transactions, EF Core's execution strategy may need to execute the complete transactional operation as a retriable unit. Microsoft documents using CreateExecutionStrategy() for this scenario.

15. Timeouts, Cancellation and Resilience

Retry policies should not be used to hide an unhealthy database. A request should still have a sensible upper time limit.

public async Task<Customer?> GetCustomerAsync(int id, CancellationToken cancellationToken) 
{ 
    return await _db.Customers.AsNoTracking().SingleOrDefaultAsync( x => x.Id == id, cancellationToken); 
}

Passing the ASP.NET Core request cancellation token through the application and data-access layers allows abandoned HTTP requests to stop unnecessary database work.

16. Avoiding the "Generic Repository for Everything" Trap

Repository abstractions can be useful, particularly when they represent meaningful application concepts. However, creating a generic repository that simply wraps every method of DbSet<T> can add an unnecessary abstraction over EF Core.

For example, an interface such as:

Task<T> GetAsync(...); Task AddAsync(...); Task UpdateAsync(...); Task DeleteAsync(...); IQueryable<T> Query();

may simply reproduce functionality that EF Core already provides.

A better question is whether the abstraction protects a meaningful architectural boundary. If the application genuinely benefits from hiding persistence details, a focused repository or query service can be valuable. Otherwise, injecting a carefully scoped DbContext into an application service may be simpler.

17. EF Core Versus Dapper

EF Core and Dapper are both popular approaches to database access, but they solve somewhat different problems.

Dapper describes itself as a simple micro-ORM that simplifies ADO.NET. Developers write SQL and Dapper maps query results to .NET objects.

Dapper's own published benchmarks demonstrate its focus on low overhead, although benchmark results should never be treated as universal application-performance guarantees. Real performance depends on query shape, indexes, network latency, database workload, result size and application architecture.

18. When EF Core Is a Better Choice

EF Core is often a strong choice when an application:

• Has a substantial domain model.
• Performs conventional CRUD operations.
• Benefits from strongly typed LINQ queries.
• Needs change tracking.
• Uses relationships between many entities.
• Benefits from migrations.
• Needs provider-independent database abstractions.
• Values developer productivity and maintainability.

EF Core can also execute raw SQL when the ORM abstraction is not the right tool for a particular query, so choosing EF Core does not necessarily mean giving up all control over SQL.

19. When Dapper Is a Better Choice

Dapper can be particularly attractive when:

• The team is comfortable writing and maintaining SQL.
• Queries are highly specialised or reporting-oriented.
• Precise SQL control is important.
• The application performs mostly read-only operations.
• Very lightweight data access is desired.
• The application already has a mature SQL/stored-procedure layer.

Dapper can also be useful alongside EF Core. For example, EF Core might handle normal transactional application operations while Dapper handles a small number of complex reporting queries.

20. A Hybrid EF Core and Dapper Architecture

EF Core and Dapper do not have to be mutually exclusive.

ASP.NET Core API | +-----------------------+ | | Application Services Reporting Services | | v v EF Core Dapper | | +-----------+-----------+ | SQL Server

The key is to keep the choice behind appropriate application boundaries. A controller should not need to know whether its data-access service uses EF Core or Dapper.

21. Performance Considerations

Database performance is usually more about the database operation than simply choosing one .NET data-access library.

Important considerations include:

• Appropriate indexes.
• Efficient SQL queries.
• Returning only the columns required.
• Pagination for large result sets.
• Avoiding accidental N+1 queries.
• Using projections instead of unnecessarily loading large graphs.
• Using AsNoTracking() for appropriate read-only queries.
• Monitoring database execution plans.
• Measuring before optimising.

A poorly indexed query executed through Dapper can still be slow, while a well-designed EF Core query can perform very well. Performance decisions should therefore be based on profiling and representative workloads rather than ORM reputation alone.

22. A Maintainable Project Structure

A larger application might use a structure such as:

src/ MyApp.Api/ Controllers/ Program.cs

MyApp.Application/
Services/
Interfaces/
DTOs/

MyApp.Domain/
Entities/
ValueObjects/

MyApp.Infrastructure/
Persistence/
ApplicationDbContext.cs
Configurations/
Repositories/

tests/
MyApp.UnitTests/
MyApp.IntegrationTests/

This is only one possible architecture. The correct structure depends on the size and complexity of the application. The important principle is that dependencies should flow deliberately rather than allowing every component to reference every other component.

23. Testing the Database Layer

Database code benefits from integration testing against a real database provider. Unit tests should generally focus on business behaviour and should not attempt to prove that SQL Server itself works correctly.

For integration tests, consider using an isolated SQL Server instance, a containerised database or another environment that closely resembles production.

Avoid assuming that every relational database provider behaves identically. In particular, tests against an in-memory provider may fail to reproduce important relational behaviour present in SQL Server or another production database.

24. Observability

Resilience works best when combined with observability. A production application should make it possible to determine:

• How long database operations take.
• Which queries are failing.
• How frequently transient failures occur.
• How many retries are being performed.
• Whether database latency is increasing.
• Which endpoints are generating the greatest database load.

Logging, metrics and distributed tracing can reveal whether a problem is in the API, application layer, database connection, query or database infrastructure.

25. Security Considerations

Database access should be designed with security in mind from the start.

• Never commit production passwords to source control.
• Use secure secret-management facilities.
• Use least-privilege database accounts.
• Use encrypted connections where appropriate.
• Prefer managed identities or equivalent secure authentication for supported cloud environments.
• Parameterise SQL when using raw SQL or Dapper.
• Validate and authorise operations at the application boundary.
• Avoid exposing internal database exceptions directly to clients.

One advantage of LINQ-based EF Core queries is that parameters are normally generated for values rather than requiring developers to concatenate user input into SQL strings.

26. Recommended Practical Approach

For a conventional ASP.NET Core business application, a sensible default architecture is:

1. Use EF Core for the primary persistence model.
2. Register DbContext through ASP.NET Core dependency injection.
3. Keep the context scoped to the normal unit-of-work boundary.
4. Use application services to coordinate business workflows.
5. Keep controllers thin.
6. Apply SOLID principles where they improve maintainability rather than mechanically creating abstractions.
7. Use asynchronous EF Core APIs and propagate cancellation tokens.
8. Enable provider-appropriate retry strategies for transient failures.
9. Be particularly careful when retrying writes and transactions.
10. Measure database performance before introducing complexity.
11. Introduce Dapper selectively where explicit SQL provides a meaningful advantage.

Conclusion

Connecting an ASP.NET application to a database is technically simple; building database access that remains maintainable under real-world requirements is considerably more challenging.

Entity Framework Core provides a powerful abstraction for relational data, including LINQ querying, change tracking, migrations and provider-specific database functionality. ASP.NET Core's dependency-injection system makes it straightforward to configure and consume DbContext with a scoped lifetime appropriate for many web applications.

The larger architectural lesson is that EF Core should be part of an application's infrastructure rather than becoming the application's architecture. SOLID principles and dependency injection help keep business logic independent from infrastructure details, while resilience policies help applications tolerate appropriate transient database failures.

Dapper remains an excellent alternative when developers need direct SQL control and a lightweight mapping layer. The choice between EF Core and Dapper should therefore be based on the application's domain, query complexity, performance requirements, team expertise and operational constraints rather than on the assumption that one tool is universally better.

In many mature applications, the strongest solution is not necessarily choosing one technology exclusively. EF Core can handle the majority of domain-oriented persistence while Dapper can be introduced selectively for specialised SQL-heavy workloads. The architectural boundary is what allows either implementation to evolve without spreading database concerns throughout the application.

Further Reading

• Microsoft Learn — DbContext Lifetime, Configuration and Initialization
• Microsoft Learn — EF Core Connection Resiliency
• Microsoft Learn — Implement Resilient Entity Framework Core SQL Connections
• Dapper — Official GitHub Repository
• Dapper — Official Documentation