Object–Relational Mapping (ORMs) in .NET

In .NET development, Object-Relational Mapping (ORM) plays a crucial role in bridging object-oriented programming with relational databases. ORMs allow developers to interact with a database using .NET objects, simplifying data manipulation and retrieval. Two prominent ORMs in the .NET ecosystem are Dapper and Entity Framework Core (EF Core).

a) Dapper

Dapper is a lightweight, open-source ORM developed by the Stack Exchange team. Known for its performance and simplicity, Dapper provides a high-performance data access layer while maintaining flexibility. It directly maps .NET objects to database tables, executing SQL queries without the overhead of a full ORM.

Key Features of Dapper

Performance: Dapper is often praised for its speed, using a straightforward approach to object mapping, resulting in minimal overhead.
Flexibility: Dapper does not abstract SQL queries, allowing developers to have complete control over their SQL and leverage database-specific features.
Ease of Use: With a minimal API, Dapper is easy to learn and integrate into existing projects.

Using Dapper

To use Dapper, you need to install the Dapper NuGet package. Here is a basic example demonstrating how to retrieve data from a database using Dapper:

using System.Data.SqlClient;
using Dapper;

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

public class DapperExample
{
    private string _connectionString = "YourConnectionString";

    public IEnumerable<Product> GetProducts()
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            return connection.Query<Product>("SELECT * FROM Products");
        }
    }
}

Additional Examples in Dapper

To demonstrate a more comprehensive use of Dapper, here is an example of how to execute SQL commands for inserts or updates:

using (var connection = new SqlConnection(_connectionString))
{
    var affectedRows = connection.Execute("INSERT INTO Products (Name, Price) VALUES (@Name, @Price)",
                                          new { Name = "New Product", Price = 11.39 });
}

Benefits and Limitations of Dapper

Benefits:

High performance for queries.
Complete control over SQL.

Limitations:

Lack of advanced mapping features.
No automatic change tracking.

b) Entity Framework Core

Entity Framework Core (EF Core) is a robust ORM developed by Microsoft. As the successor to Entity Framework, EF Core offers significant improvements, including better performance, cross-platform support, and more flexible configurations. EF Core aims to simplify data access by allowing developers to work with .NET objects and minimising the need to write SQL queries.

1. Learning the Basics of EF Core

EF Core is based on three key concepts: DbContext, the model, and LINQ queries.

DbContext: The primary class responsible for interacting with the database. It manages database connections and change tracking.
Model: The representation of the database schema in the form of .NET classes.
LINQ Queries: EF Core allows querying the database using Language-Integrated Query (LINQ), making data access more intuitive and type-safe.

2. Code First & Migrations

Code First is an approach where the database schema is generated from the model classes defined in code. This allows developers to focus on the domain model and evolve the database schema through migrations.

Migrations are a way to incrementally update the database schema to keep it in sync with the application's data model. Here is how to use Code First and migrations in EF Core:

Define your model classes:

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

public class AppDbContext : DbContext
{
    public DbSet<Product> Products { get; set; }
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("YourConnectionString");
    }
}

Add a migration and update the database:

dotnet ef migrations add InitialCreate
dotnet ef database update

3. Change Tracker API

The Change Tracker API in EF Core tracks changes made to your entity instances. This is essential for maintaining entity states and applying updates to the database.

Usage example:

using (var context = new AppDbContext())
{
    var product = context.Products.First(p => p.Id == 4);
    product.Price = 41.10M;
    context.SaveChanges();
}

The Change Tracker automatically detects changes to the product entity and generates the corresponding update SQL statement.

4. Lazy Loading, Eager Loading, Explicit Loading

EF Core provides three different strategies for loading related data:

Lazy Loading: Related data is loaded on demand when the navigation property is accessed. It requires installing the Microsoft.EntityFrameworkCore.Proxies package and configuring the proxy in OnConfiguring.
Eager Loading: Related data is loaded as part of the initial query using the Include method.
Explicit Loading: Related data is explicitly loaded using the Load method.

Example of each:

Lazy Loading:

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

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseSqlServer("YourConnectionStringHere")
                  .UseLazyLoadingProxies();
}

Eager Loading:

var products = context.Products.Include(p => p.Category).ToList();

Explicit Loading:

var product = context.Products.First();
context.Entry(product).Reference(p => p.Category).Load();

Conclusion

Both Dapper and Entity Framework Core offer unique advantages for .NET developers. Dapper excels in performance and simplicity, making it ideal for applications where control over SQL is paramount. Entity Framework Core, on the other hand, provides a comprehensive set of features, including LINQ queries, change tracking, and advanced loading strategies, making it suitable for complex data access scenarios. Understanding the strengths of each ORM allows developers to choose the right tool for their specific needs. In some cases, it might be beneficial to combine both, using Dapper for performance-critical queries and EF Core for complex operations and state management.