ASP.NET Core Data Access

This section covers strategies and best practices for accessing data in ASP.NET Core applications, including Entity Framework Core, raw SQL, and other data access technologies.

Key Data Access Technologies

Entity Framework Core (EF Core)

Entity Framework Core is a modern object-relational mapper (ORM) for .NET. It enables developers to work with a database using .NET objects, eliminating the need for most of the data-access code they typically need to write. EF Core supports a wide range of databases, including SQL Server, PostgreSQL, MySQL, SQLite, and more.

EF Core Overview
Getting Started with EF Core
Database Migrations
Querying Data with EF Core
Writing Data with EF Core

Raw SQL

For scenarios where an ORM might be overkill or when you need fine-grained control over your SQL queries, you can use raw SQL. ASP.NET Core provides excellent support for executing raw SQL commands using libraries like Dapper or directly via ADO.NET.

Raw SQL Overview
Using Dapper
Using ADO.NET

Other Data Access Patterns

Explore other data access patterns and technologies relevant to modern web development, such as NoSQL databases, microservices data strategies, and caching.

NoSQL Databases
Data Caching

EF Core Overview

EF Core simplifies data access by mapping your .NET classes to database tables and your object properties to table columns. This allows you to query and save data as if you were working with regular .NET objects.

EF Core is the recommended data access technology for most ASP.NET Core applications due to its productivity, performance, and features.

Getting Started with EF Core

To start using EF Core, you need to install the necessary NuGet packages and define your data model and context.

Install the EF Core provider for your database (e.g., Microsoft.EntityFrameworkCore.SqlServer).
Define your entity classes (e.g., Product, Customer).
Create a DbContext class that represents a session with the database and can be used to query and save data.
Register your DbContext in the Startup.cs (or Program.cs in .NET 6+) file.

Example DbContext:

                
using Microsoft.EntityFrameworkCore;

public class MyDbContext : DbContext
{
    public MyDbContext(DbContextOptions options)
        : base(options)
    {
    }

    public DbSet<Product> Products { get; set; }
    public DbSet<Category> Categories { get; set; }
}

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

public class Category
{
    public int CategoryId { get; set; }
    public string Name { get; set; }
}
                
            

In Program.cs (.NET 6+):

                
var builder = WebApplication.CreateBuilder(args);

// Add services to the container.
var connectionString = builder.Configuration.GetConnectionString("DefaultConnection");
builder.Services.AddDbContext<MyDbContext>(options =>
    options.UseSqlServer(connectionString));

// ... other services

var app = builder.Build();

// ... configure middleware

app.Run();

Database Migrations

EF Core Migrations allow you to incrementally update your database schema to keep it synchronized with your entity model. This is crucial for managing database changes throughout the development lifecycle.

Common migration commands:

dotnet ef migrations add InitialCreate: Scaffolds a new migration.
dotnet ef database update: Applies pending migrations to the database.
dotnet ef migrations remove: Removes the last migration.

Always review the generated migration code before applying it to ensure it performs the intended database changes.

Querying Data with EF Core

You can query data using LINQ (Language Integrated Query) directly on your DbSet properties.

                
using (var context = serviceProvider.CreateScope().ServiceProvider.GetRequiredService<MyDbContext>())
{
    // Get all products
    var allProducts = await context.Products.ToListAsync();

    // Get products with price greater than 50
    var expensiveProducts = await context.Products
                                      .Where(p => p.Price > 50)
                                      .ToListAsync();

    // Get product by ID
    var product = await context.Products.FindAsync(1);
}
                
            

Writing Data with EF Core

Adding, updating, and deleting entities are straightforward operations with EF Core.

                
using (var context = serviceProvider.CreateScope().ServiceProvider.GetRequiredService<MyDbContext>())
{
    // Add a new product
    var newProduct = new Product { Name = "New Gadget", Price = 99.99m };
    context.Products.Add(newProduct);
    await context.SaveChangesAsync();

    // Update a product
    var existingProduct = await context.Products.FindAsync(1);
    if (existingProduct != null)
    {
        existingProduct.Price = 55.50m;
        await context.SaveChangesAsync();
    }

    // Delete a product
    var productToDelete = await context.Products.FindAsync(2);
    if (productToDelete != null)
    {
        context.Products.Remove(productToDelete);
        await context.SaveChangesAsync();
    }
}
                
            

Raw SQL Overview

When ORMs don't fit your needs or for performance-critical operations, direct SQL execution is an option. EF Core and ADO.NET provide ways to execute raw SQL.

Using Dapper

Dapper is a popular micro-ORM that extends IDbConnection to execute SQL queries and map results to your objects. It's known for its excellent performance.

Install the Dapper NuGet package.
Inject IDbConnection (usually registered by your EF Core provider or manually).

                
using System.Data;
using Dapper;

// Assuming you have an IDbConnection instance
using (IDbConnection dbConnection = new SqlConnection(connectionString))
{
    var products = await dbConnection.QueryAsync<Product>("SELECT * FROM Products WHERE Price > @MinPrice", new { MinPrice = 50 });
    var product = await dbConnection.QueryFirstOrDefaultAsync<Product>("SELECT * FROM Products WHERE ProductId = @Id", new { Id = 1 });
}
                
            

Using ADO.NET

For maximum control and compatibility with any .NET data source, you can use ADO.NET directly. This involves explicitly managing connections, commands, and data readers.

                
using System.Data.SqlClient;

using (SqlConnection connection = new SqlConnection(connectionString))
{
    await connection.OpenAsync();
    string sql = "SELECT ProductId, Name, Price FROM Products WHERE Price > @MinPrice";
    using (SqlCommand command = new SqlCommand(sql, connection))
    {
        command.Parameters.AddWithValue("@MinPrice", 50);
        using (SqlDataReader reader = await command.ExecuteReaderAsync())
        {
            while (await reader.ReadAsync())
            {
                var product = new Product
                {
                    ProductId = reader.GetInt32(0),
                    Name = reader.GetString(1),
                    Price = reader.GetDecimal(2)
                };
                // Process product
            }
        }
    }
}
                
            

NoSQL Databases

ASP.NET Core applications can integrate with various NoSQL databases like MongoDB, Redis, Cosmos DB, etc. The integration approach depends on the specific database and its .NET SDK.

For example, using the MongoDB.Driver package:

                
// Example (conceptual)
var client = new MongoClient("mongodb://localhost:27017");
var database = client.GetDatabase("myDatabase");
var collection = database.GetCollection<Product>("products");

var product = new Product { Name = "Sample Item", Price = 10.0m };
await collection.InsertOneAsync(product);
                
            

Data Caching

Caching is essential for improving application performance by reducing database load. ASP.NET Core supports various caching strategies, including in-memory caching and distributed caching (e.g., using Redis).

In-Memory Caching

Use the IMemoryCache service to cache data directly within your web server's memory.

                
using Microsoft.Extensions.Caching.Memory;

public class DataService
{
    private readonly MyDbContext _context;
    private readonly IMemoryCache _cache;

    public DataService(MyDbContext context, IMemoryCache cache)
    {
        _context = context;
        _cache = cache;
    }

    public async Task<List<Product>> GetProductsAsync()
    {
        if (_cache.TryGetValue("allProducts", out List<Product> cachedProducts))
        {
            return cachedProducts;
        }

        var products = await _context.Products.ToListAsync();

        var cacheEntryOptions = new MemoryCacheEntryOptions()
            .SetSlidingExpiration(TimeSpan.FromMinutes(10)); // Cache for 10 minutes

        _cache.Set("allProducts", products, cacheEntryOptions);

        return products;
    }
}
                
            

Remember to register IMemoryCache in your Program.cs:

builder.Services.AddMemoryCache();