What is an ORM?

An object-relational mapper is a library that translates between the objects in your program and the rows in your database. You define classes; it generates the SQL to read and write them, materialises result sets into instances, tracks what you changed, and writes the changes back. Entity Framework Core, Hibernate, SQLAlchemy, and ActiveRecord are all the same idea in different languages.

The examples here are EF Core against SQL Server, since that is the stack the rest of this path uses. Everything about N+1, change tracking, and loading strategies transfers directly to the others; only the method names change.

An ORM does not remove SQL from your application. It writes the SQL for you, and leaves you responsible for what it wrote.- the deal you are making

That framing matters, because the two standard positions on ORMs are both wrong. "Never write SQL again" produces the page that issues fifty-one queries. "ORMs are slow, always hand-write SQL" throws away a genuine productivity win over a problem that is usually one method call to fix. The useful position is that an ORM is a code generator you are accountable for, exactly like any other.

The mismatch it solves

Objects and relations are different shapes, and the gap between them has a name: the object-relational impedance mismatch.

An object graph has identity, references, collections, and inheritance. A relational schema has rows, keys, foreign keys, and joins. An Order that holds a Customer reference and a list of OrderLine objects is three tables and two joins on the other side. Writing that translation by hand is mechanical, repetitive, and easy to get subtly wrong - the kind of code that is 15% of a feature and 60% of its bugs.

What you actually get for adopting one:

  • The mapping itself. No hand-written materialisation code, no reader.GetInt32(4) that breaks when a column moves.
  • Compile-time safety over the query. A LINQ query that references a renamed property fails to build. A SQL string in quotes fails at runtime, in production, on the one code path nobody tested.
  • Parameterisation by default. Values become parameters automatically, which removes SQL injection from the common path rather than relying on discipline.
  • A unit of work. Change several objects, call save once, and get one transaction containing exactly the statements needed.
  • Schema migrations that travel with the code, versioned alongside it.

None of that is nothing. It is a real reduction in the amount of code that can be wrong.

How the mapping works

Three concepts carry almost all of the behaviour, and knowing them explains most ORM surprises.

Entities and the model. A class maps to a table, its properties to columns, and its navigation properties to foreign keys. The mapping is inferred by convention and overridden by configuration. The model is built once at startup, not per query.

The identity map. Within one context, a given primary key maps to exactly one object instance. Load order 1001 twice in the same context and you get the same object back, not two copies. This is what makes change tracking coherent, and it is also why a long-lived context slowly accumulates every entity it has ever seen.

Change tracking and the unit of work. When you load an entity, the context keeps a snapshot of its original values. When you call SaveChanges, it compares current values against that snapshot, works out the minimal set of INSERT, UPDATE, and DELETE statements, orders them so foreign keys are satisfied, and sends them inside a single transaction. If any statement fails, the whole batch rolls back.

C#what SaveChanges does for you
var order = await db.Orders.FindAsync(1001); // SELECT, snapshot taken order.Status = "Shipped"; // nothing sent yet order.Lines.Add(new OrderLine { Sku = "C7" }); // still nothing await db.SaveChangesAsync(); // one transaction: UPDATE Orders SET Status = @p0 WHERE Id = @p1; // INSERT INTO OrderLines (...) VALUES (...);

The snapshot has a cost: tracking N entities means holding N copies of their original values and comparing them on every save. For a query whose results you are only going to display, that work is pure waste, which is what AsNoTracking() exists to skip.

The N+1 problem

This is the defect the cover diagram shows, and it is by a wide margin the most common performance problem in ORM-backed applications. One query fetches N parent rows. Then, touching a navigation property inside a loop triggers one more query per parent. Total: N+1 round trips where one or two would do.

What makes it dangerous is that it is invisible in the C# and forgiving in development. Fifty-one queries against a local database at 0.5 ms each is 25 ms, which nobody notices. The same code against a database one network hop away at 8 ms each is 400 ms, and it degrades linearly with page size. The query time is fine in both cases. The problem is not the queries; it is the round trips.

C#three fixes, in increasing order of preference
// 1. eager load: one query with a join var orders = await db.Orders.Include(o => o.Customer).Take(50).ToListAsync(); // 2. eager load a collection without the cartesian blow-up var orders = await db.Orders.Include(o => o.Lines).AsSplitQuery().ToListAsync(); // 3. project: fetch exactly the columns the screen needs, nothing more var rows = await db.Orders .Select(o => new OrderRow(o.Id, o.Customer.Name, o.Total)) .Take(50) .ToListAsync();

Projection is usually the right answer for anything read-only. It produces one query, fetches only the columns you asked for, and skips change tracking entirely because there is no entity to track. It also gives the database a shot at answering the whole thing from a covering index without touching the table.

To catch N+1 before your users do, look at query counts, not query durations. Log the SQL EF Core emits in development, or watch the count per request in your telemetry. A page whose query count grows when the result set grows is an N+1, every time.

Loading strategies

There are four ways to get related data, and choosing deliberately between them is most of the skill.

  • Eager loading (Include) - fetch the related data alongside the parent, in one query. Predictable, and the default choice for a small fixed set of relations. Its failure mode is the cartesian explosion: including two collections at once multiplies the rows, so 50 orders with 10 lines and 5 payments returns 2,500 rows to carry 750 facts. AsSplitQuery() turns that into one query per collection instead.
  • Projection (Select) - do not load entities at all, load a shape. Fastest and leanest, and the right default for read-only screens and APIs.
  • Explicit loading (Entry(...).Collection(...).LoadAsync()) - load the relation later, on purpose, once. Useful when only some code paths need it.
  • Lazy loading - the ORM fetches the relation the moment you touch the property. Convenient, and the direct cause of most N+1 problems, because the query is issued by a property access that looks free. EF Core leaves it off unless you opt in, which was the right call.

A rule that holds up well: lazy loading is a debugging convenience, not a production strategy. If a code path is on a hot request, it should say explicitly what data it needs.

What it hides from you

The abstraction is good enough that you can ship real features without ever seeing the SQL. These are the places where that catches up with you.

  • Parameter types, and the implicit conversion they cause. A .NET string maps to nvarchar by default. Compare it against a varchar column and SQL Server converts the column rather than the parameter, which disables the index seek on exactly the column you indexed. It is the single most common ORM performance bug, it looks like nothing in C#, and the query plan shows it plainly as CONVERT_IMPLICIT. Map the property with the right column type and it disappears.
  • How much you are selecting. Loading an entity loads every mapped column, including the nvarchar(max) description nobody displays. Projection is the fix; the ORM will not infer it.
  • Change-tracking overhead on large read-only result sets. AsNoTracking() on every query whose results you will not modify.
  • Translation boundaries. Some expressions cannot be turned into SQL. Older EF versions silently evaluated them on the client after pulling the rows into memory; EF Core now throws instead, which is much better - the exception is telling you the query you wrote does not exist in SQL.
  • Round trips inside a transaction. A save that writes fifty entities may batch into a handful of statements or may not, depending on configuration. Anything holding a transaction open across many round trips is the long-transaction problem from the transactions article.

The habit that prevents all of these is simple: read the SQL your ORM emits, at least once per feature. In EF Core it is one call - query.ToQueryString() - and enabling sensitive-data logging in development shows the parameter values with it.

When to drop to SQL

An ORM is optimised for the case it was designed for: loading a handful of entities, modifying them, and saving them back. It is not the right tool for everything, and it is not supposed to be.

  • Reporting and analytics. Window functions, pivots, recursive CTEs, and multi-level aggregations are clearer and faster as SQL. Trying to express them in LINQ produces something nobody can read and the provider cannot translate well.
  • Bulk operations. Loading 100,000 entities to change one column is a fifty-thousand-fold waste. EF Core 7 and later have ExecuteUpdate and ExecuteDelete, which issue a single set-based statement; before those, this is what raw SQL was for.
  • Hot read paths. When materialisation overhead genuinely shows up in a profile, a Dapper query returning a flat DTO is a small, honest optimisation.
  • Anything where you must control the plan. A query hint, a forced index, an OPTION (RECOMPILE) for a parameter-sensitive statement.

The productive arrangement in most .NET systems is not one or the other. Use the ORM for the write model, where change tracking and transactional consistency earn their keep, and use SQL or a micro-ORM like Dapper for the read model, where you want exact control over the shape and cost of the query. Both can share the same connection and the same transaction, so this is a per-query decision rather than an architectural commitment.

Using one well

  • Read the generated SQL for every non-trivial query. Once, at development time. It takes a minute and it catches conversions, missing indexes, and accidental client evaluation.
  • Count queries per request in your telemetry. A number that scales with the result set is an N+1.
  • AsNoTracking() on read-only queries, projection on read-only screens. Together these remove most of the ORM's overhead.
  • Scope the context per request, never longer. A DbContext is not thread-safe and is designed to be short-lived. A singleton context is a memory leak and a concurrency bug in one object.
  • Keep the transaction inside the save. Do the slow work first, then save. See the transaction article for what a long one costs everyone else.
  • Index for the SQL the ORM actually writes, not the SQL you imagined it would write. This is only possible if you have read it.
  • Let migrations own the schema, or let the schema own itself - pick one. Mixing EF migrations with hand-applied changes or a DACPAC deployment produces a schema no source of truth describes.

An ORM is worth using. It removes a category of tedious code and a category of injection bugs, and modern ones generate SQL that is perfectly reasonable for the queries they were designed for. What it cannot do is care about your data model on your behalf. The developers who get good results are not the ones who avoid the ORM; they are the ones who know what it just sent.

References