ORM Features

An ORM maps objects to tables so you can persist a graph without hand-writing every INSERT. EF Core, Hibernate, Django ORM all do this. They are not a reason to skip SQL, indexes, or transactions. When the query is the product, write SQL (or a compiled view) and map the result.

What it should give you

• Unit of work: one EF Core SaveChanges starts a transaction when none is open. A Hibernate flush uses the transaction you already started; it does not open one by itself.
• Identity map: the same row is the same object in a context — until you dispose the context.
• Change tracking: conveniences and surprises (N+1, extra UPDATEs).
• Migrations: schema as code. Still review the generated SQL.

N+1 and lazy load

Looping order.Lines with lazy load fires a query per order. Fix with explicit Include / fetch-join, a projection (Select into a DTO), or a second query you control. Lazy load in a web request is a footgun; many teams disable it.

Tracking vs no-tracking

Read-only list endpoints: no-tracking (or a micro-ORM). Tracking is for graphs you intend to update. Mixing a long-lived tracked context with a web request is how you get stale entities and accidental updates.

Example

One query per order, then a projection that lets the database add the lines.

var orders = await db.Orders.ToListAsync(ct);
foreach (var order in orders)
{
    var lines = await db.Lines.Where(line => line.OrderId == order.Id).ToListAsync(ct);
    _ = lines.Count;
}

var rows = await db.Orders
    .Select(order => new OrderTotal(order.Id, order.Lines.Sum(line => line.Amount)))
    .ToListAsync(ct);

What breaks it

Returning IQueryable from the repository means the controller decides the SQL. Return the DTO from the projection above instead.

static IQueryable<Order> All(AppDb db) => db.Orders;

Pitfalls

• Treating entities as API contracts (over-posting, leaking columns).
• Repository-on-top-of-repository that returns IQueryable to the controller — the ORM is now your API.
• Ignoring the SQL in logs. If you cannot explain the query, you do not own the cost.

See SQL for business logic and normalization.