Data Access in ASP.NET Core

Overview

ASP.NET Core provides a flexible set of APIs for working with data. Whether you need an object‑relational mapper, a lightweight NoSQL client, or direct access to a relational database, the framework supports it.

Entity Framework Core

Entity Framework Core (EF Core) is the recommended ORM for most relational database scenarios. It supports LINQ queries, change tracking, migrations, and more.

dotnet add package Microsoft.EntityFrameworkCore.SqlServer

public class Blog
{
    public int Id { get; set; }
    public string Title { get; set; } = string.Empty;
    public ICollection<Post> Posts { get; set; } = new List<Post>();
}

public class Post
{
    public int Id { get; set; }
    public string Content { get; set; } = string.Empty;
    public int BlogId { get; set; }
    public Blog? Blog { get; set; }
}

using Microsoft.EntityFrameworkCore;

public class BloggingContext : DbContext
{
    public BloggingContext(DbContextOptions<BloggingContext> options)
        : base(options) { }

    public DbSet<Blog> Blogs => Set<Blog>();
    public DbSet<Post> Posts => Set<Post>();
}

builder.Services.AddDbContext<BloggingContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("BloggingDatabase")));

// Example of injecting and using the context
builder.Services.AddScoped<IBlogRepository, BlogRepository>();

Using Dapper

Dapper is a lightweight micro‑ORM that works directly with raw SQL. It offers excellent performance for scenarios where full EF Core features aren't required.

dotnet add package Dapper

using System.Data;
using Dapper;
using Microsoft.Data.SqlClient;

public class BlogRepository
{
    private readonly string _connectionString;
    public BlogRepository(IConfiguration config) => 
        _connectionString = config.GetConnectionString("BloggingDatabase");

    public async Task<IEnumerable<Blog>> GetAllAsync()
    {
        await using IDbConnection db = new SqlConnection(_connectionString);
        return await db.QueryAsync<Blog>("SELECT * FROM Blogs");
    }
}

Configuration and Secrets

Never store connection strings in source code. Use appsettings.json for defaults and User Secrets or environment variables for production values.

{
  "ConnectionStrings": {
    "BloggingDatabase": "Server=.;Database=Blogging;Trusted_Connection=True;"
  }
}

In production, override with an environment variable:

ConnectionStrings__BloggingDatabase="Server=prod;Database=Blogging;User Id=app;Password=*****;"

Best Practices

• Prefer async APIs to avoid blocking threads.
• Use a scoped lifetime for DbContext to ensure a single instance per request.
• Apply migrations during deployment, not at runtime.
• Validate user input before constructing raw SQL.