ASP.NET Core Data Access

Introduction to Data Access in ASP.NET Core

ASP.NET Core provides flexible and powerful ways to access data. Whether you're working with relational databases, NoSQL stores, or external APIs, there are patterns and tools to help you build robust data-driven applications.

This section covers:

Choosing the right data access technology.
Implementing common data access patterns.
Best practices for performance and security.

Entity Framework Core

Entity Framework Core (EF Core) is a modern object-relational mapper (ORM) for .NET. It allows developers to work with a database using .NET objects, eliminating the need for most of the data-access code they'd typically need to write.

Key Features:

LINQ to Entities for querying data.
Migrations for database schema management.
Support for various database providers (SQL Server, PostgreSQL, MySQL, SQLite, etc.).
Change tracking and concurrency control.

Getting Started with EF Core:

To use EF Core, you'll typically follow these steps:

Install EF Core NuGet Packages:


PM> Install-Package Microsoft.EntityFrameworkCore
PM> Install-Package Microsoft.EntityFrameworkCore.SqlServer // Or your chosen provider
PM> Install-Package Microsoft.EntityFrameworkCore.Tools

Define your Models: Create C# classes that represent your database tables.


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

Create a DbContext: This class represents your database session and allows you to query and save data.


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

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

Configure Services: Register your DbContext in Startup.cs or Program.cs.


// In Program.cs (ASP.NET Core 6+)
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

Perform CRUD Operations: Use your DbContext to interact with the database.


// Example: Get all products
var products = await _context.Products.ToListAsync();

// Example: Add a new product
var newProduct = new Product { Name = "Gadget", Price = 19.99M };
_context.Products.Add(newProduct);
await _context.SaveChangesAsync();

Raw SQL Queries

While ORMs like EF Core are powerful, sometimes you need to write raw SQL queries for performance tuning or complex operations. EF Core allows you to execute raw SQL queries directly.

Executing Raw SQL:

You can use FromSqlRaw or FromSqlInterpolated to execute SQL.


// Using FromSqlInterpolated for safety and readability
var productName = "Laptop";
var products = await _context.Products
    .FromSqlInterpolated($"SELECT * FROM Products WHERE Name = {productName}")
    .ToListAsync();

Dapper (Micro-ORM)

Dapper is a popular, lightweight micro-ORM that extends IDbConnection with methods to execute SQL commands and return dynamic or strongly-typed objects. It's known for its speed and simplicity.

Getting Started with Dapper:

Install Dapper NuGet Package:


PM> Install-Package Dapper
PM> Install-Package Dapper.Contrib // For basic CRUD operations

Execute Queries:


using SqlConnection connection = new ("YourConnectionString");
var products = await connection.QueryAsync<Product>("SELECT * FROM Products");

var product = await connection.QueryFirstOrDefaultAsync<Product>("SELECT * FROM Products WHERE Id = @Id", new { Id = 1 });

Data Access Best Practices

Dependency Injection: Inject your DbContext or data access repositories into your services and controllers.
Asynchronous Operations: Always use asynchronous methods (e.g., ToListAsync, SaveChangesAsync) to avoid blocking threads.
Connection Pooling: Ensure your database provider is configured for connection pooling, which is typically handled by default.
Error Handling: Implement robust error handling and logging for database operations.
Security: Sanitize inputs to prevent SQL injection attacks, especially when building dynamic queries. Use parameterized queries or ORM features that handle this automatically.
Lazy Loading vs. Eager Loading: Understand the implications of lazy and eager loading for performance. Use eager loading (e.g., Include in EF Core) when you know you'll need related data.

MSDN Documentation