ASP.NET Core Data Access

Introduction to Data Access in ASP.NET Core

Accessing and persisting data is a fundamental part of most web applications. ASP.NET Core provides a robust and flexible set of tools and patterns to handle data access efficiently and securely. This document covers the primary approaches to data interaction within your ASP.NET Core applications, focusing on modern best practices.

Key considerations for data access include:

• Choosing the right data storage technology (SQL, NoSQL, etc.).
• Selecting an appropriate Object-Relational Mapper (ORM) or data access library.
• Implementing secure data operations, including protection against SQL injection.
• Managing database schemas and migrations.
• Optimizing performance for data retrieval and storage.

Using Entity Framework Core

Entity Framework Core (EF Core) is Microsoft's recommended Object-Relational Mapper (ORM) for .NET. It allows developers to work with a database using .NET objects, abstracting away much of the underlying SQL code. EF Core supports various database providers, including SQL Server, PostgreSQL, MySQL, SQLite, and more.

Key Features:

• LINQ to Entities: Query your database using Language Integrated Query (LINQ).
• Change Tracking: EF Core automatically tracks changes to your entities.
• Migrations: Manage database schema changes over time.
• Cross-Platform: Works on Windows, macOS, and Linux.

Getting Started with EF Core:

To use EF Core, you typically define C# classes (entities) that map to your database tables and a DbContext class that represents a session with the database.

using Microsoft.EntityFrameworkCore;

public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }
    public List<Post> Posts { get; set; }
}

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }
    public int BlogId { get; set; }
    public Blog Blog { get; set; }
}

public class BloggingContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        // Example: Using SQLite
        optionsBuilder.UseSqlite("Data Source=blogging.db");
    }
}
                

You can then interact with the database:

using (var context = new BloggingContext())
{
    var blog = new Blog { Url = "https://example.com" };
    context.Blogs.Add(blog);
    context.SaveChanges();

    var blogs = context.Blogs.OrderBy(b => b.Url).ToList();
}
                

Executing Raw SQL Queries

While ORMs like EF Core offer high-level abstractions, there are scenarios where direct SQL execution is necessary or more performant. EF Core provides methods to execute raw SQL commands.

Executing Raw SQL with EF Core:

You can execute raw SQL queries to retrieve data or perform updates.

using (var context = new BloggingContext())
{
    // Querying raw SQL
    var blogs = context.Blogs.FromSqlRaw("SELECT * FROM Blogs");

    // Executing raw SQL for updates/inserts/deletes
    context.Database.ExecuteSqlRaw("UPDATE Blogs SET Url = {0} WHERE BlogId = {1}", "new.url.com", 1);
}
                

Caution: Always sanitize user input when constructing raw SQL queries to prevent SQL injection vulnerabilities. Using parameterized queries as shown above is a crucial step.

Database Migrations with EF Core

Database schema evolves over time as your application requirements change. EF Core Migrations is a powerful feature that helps you manage these schema changes incrementally. Migrations are code-based, meaning you define your schema changes in C# code, making them version-controllable and repeatable.

Common Migration Commands:

• dotnet ef migrations add MigrationName: Creates a new migration based on your current model.
• dotnet ef database update: Applies pending migrations to the database.
• dotnet ef migrations script: Generates a SQL script for your migrations.

Migrations are stored in a Migrations folder within your project.

Advanced Data Access Topics

• Asynchronous Data Operations: Leverage async and await for non-blocking data access, improving application responsiveness.
• Connection Pooling: Efficiently manage database connections to reduce overhead.
• Data Caching: Implement caching strategies to reduce database load and improve read performance.
• Data Validation: Integrate data validation rules to ensure data integrity before persistence.
• Dependency Injection: Properly register your DbContext and repositories for use throughout your application.
• NoSQL Databases: Explore integration with NoSQL databases like MongoDB, Cosmos DB, etc., using their respective SDKs.