ORM (Object-Relational Mapping) in ASP.NET Core

What is ORM?

ORM (Object-Relational Mapping) is a technique that allows developers to work with a database using C# objects instead of writing raw SQL queries.

It maps:

1. Database Table → C# Class
2. Database Row → Object
3. Database Column → Property

Instead of writing:

SELECT * FROM Products WHERE Id = 1;

We write:

var product = await _context.Products.FindAsync(1);

Why do we use ORM?

Without ORM:

• Write SQL manually.
• Map each column to object properties.
• Handle database connections manually.

With ORM:

• Less code.
• Faster development.
• Strongly typed.
• Easier maintenance.

How ORM works ORM maps it to:

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

Query:

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

Generated SQL:

SELECT * FROM Products

We write C# code then ORM generates SQL.

ORM in ASP.NET Core

The most popular ORM is Entity Framework Core (EF Core). Other ORMs include:

1. Dapper (Micro ORM)
2. Linq2Db

Core Components of EF Core

Entity

Represents a database table.

public class Customer
{
    public int Id { get; set; }

    public string Name { get; set; }
}

Maps to -> Customers Table

The main advantage of EF Core ORM is given below

1. Change Tracking Example:

var product = await _context.Products.FindAsync(1);

product.Price = 300;

await _context.SaveChangesAsync();

We never call Update(). EF already knows that Before a product price was 200 and now the price has changed into 300

It automatically generates SQL like following:

UPDATE Products
SET Price = 300
WHERE Id = 1

2. LINK Support Instead of write raw SQL queries like following

SELECT * FROM Products
WHERE Price > 100

We are writing like below because EF auto translates LINQ into SQL

var products = _context.Products
    .Where(x => x.Price > 100)
    .ToList();

Some other advantages like

3. Relationships: One to Many
4. Loading Related Data: Eager loading, Lazy loading, and Explicit Loading
5. Migrations: Migrations keep our database schema synchronized with our model classes.
6. Query Execution: EF Core builds an expression tree from our LINQ query and translates it into SQL.

##Performance Optimization: Use AsNoTracking()

For read-only queries:

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

Benefits: Faster, Less memory, No change tracking overhead.

Use Pagination: Avoid loading thousands of records.

var products = await _context.Products
    .Skip(20)
    .Take(10)
    .ToListAsync();

Avoid N+1 Query Problem: May execute one query for customers plus one query per customer.

Bad:

foreach(var customer in customers)
{
    customer.Orders.Count();
}

Better:

_context.Customers
    .Include(c => c.Orders)

Advantages

• Faster development.
• Strongly typed.
• Automatic SQL generation.
• Change tracking.
• LINQ support.
• Migrations.
• Cross-database support.
• Reduced boilerplate code.
• Easier maintenance.

Disadvantages

• Slight performance overhead compared to raw SQL.
• Complex queries may generate inefficient SQL.
• Learning curve.
• Less control over generated SQL.
• Not ideal for every high-performance scenario.

When to Choose EF Core over Dapper

Choose EF Core when building CRUD-heavy business applications where developer productivity, maintainability, and rapid development are more important than maximum performance. EF Core is ideal when you need change tracking, LINQ querying, relationship management, and database migrations. For most enterprise applications, EF Core provides the best balance of features and performance, while Dapper is better suited for performance-critical, read-heavy, or complex SQL scenarios where manual query optimization is required.