Data Access in ASP.NET Core

This section covers how to work with data in your ASP.NET Core applications, focusing on common patterns, best practices, and popular libraries.

Choosing a Data Access Strategy

ASP.NET Core offers flexibility in how you interact with data. Key considerations include:

• ORM (Object-Relational Mapper): For mapping application objects to database tables.
• Micro-ORM: For simpler mapping and more control over SQL.
• ADO.NET: For direct database interaction with minimal abstraction.

Entity Framework Core

Entity Framework Core (EF Core) is Microsoft's recommended ORM for .NET. It provides a powerful and lightweight way to work with databases.

Key Features:

• LINQ to Entities for querying data using Language Integrated Query.
• Migrations for managing database schema changes.
• Change tracking and concurrency control.
• Support for various database providers (SQL Server, PostgreSQL, MySQL, SQLite, etc.).

Getting Started with EF Core:

1. Install EF Core packages:

dotnet add package Microsoft.EntityFrameworkCore
dotnet add package Microsoft.EntityFrameworkCore.SqlServer # Or your specific provider
dotnet add package Microsoft.EntityFrameworkCore.Tools
            

2. Define your entity classes:

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
}
            

3. Create a DbContext:

using Microsoft.EntityFrameworkCore;

public class ApplicationDbContext : DbContext
{
    public DbSet<Product> Products { get; set; }

    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options)
    {
    }
}
            

4. Configure in Startup.cs (or Program.cs for .NET 6+):

// For .NET 6+ (Program.cs)
builder.Services.AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

// For older versions (Startup.cs)
public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContext<ApplicationDbContext>(options =>
        options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
}
            

5. Run Migrations:

dotnet ef migrations add InitialCreate
dotnet ef database update
            

Dapper: A Micro-ORM

Dapper is a popular, high-performance micro-ORM that allows you to execute SQL queries and map the results to .NET objects with minimal overhead.

Key Features:

• Excellent performance.
• Simple to use.
• Full control over your SQL.
• Supports asynchronous operations.

Getting Started with Dapper:

1. Install Dapper package:

dotnet add package Dapper
            

2. Execute queries:

using Dapper;
using System.Data.SqlClient; // Or your database provider

public async Task<IEnumerable<Product>> GetProductsAsync(string connectionString)
{
    using (var connection = new SqlConnection(connectionString))
    {
        var products = await connection.QueryAsync<Product>("SELECT * FROM Products");
        return products;
    }
}
            

Best Practices for Data Access

• Dependency Injection: Inject your DbContext or data access services into controllers and other components.
• Connection Management: Use connection pooling provided by the data provider. EF Core and Dapper handle this automatically.
• Asynchronous Operations: Always use asynchronous methods (e.g., ToListAsync, ExecuteAsync) for database operations to avoid blocking threads.
• Error Handling: Implement robust error handling for database exceptions.
• Security: Sanitize input and use parameterized queries to prevent SQL injection. ORMs generally handle this well.
• Performance Optimization:
• Only select the columns you need.
• Use efficient queries and indexing in your database.
• Consider caching strategies for frequently accessed data.