liveBook · Manning

Object-relational mapper

As explained in Grokking Software Architecture

An object-relational mapper is a technique or software layer that represents relational database data as programming-language objects and translates operations between the two models.

An object-relational mapper (ORM) is a translator between object-oriented code and a relational database. It maps classes and objects to tables and rows, and translates object queries into SQL. This helps bridge the mismatch between interconnected objects and relational tables—but it cannot make their differences disappear. section 6.8.1 section 8.3

Instead of assembling SQL strings and mapping rows by hand, you can declare what data you want in your programming language. ORM queries can improve productivity and type safety, while parameterization treats user input as data rather than executable SQL. section 8.3.1 section 8.10

Listing 8.4 C# (Entity Framework / LINQ - https://learn.microsoft.com/en-us/ef):
var users = context.Users
    .Where(u => u.Age > 21 && u.IsActive)
    .OrderBy(u => u.LastName)
    .ToList();

This Entity Framework query asks for active users over 21, ordered by last name, without writing a SQL string. section 8.3.1

The translator is useful, but the database is still doing real work. An innocent-looking property access can trigger extra queries, and fetching whole objects can pull far more data than a screen needs. section 6.8.1 section 8.3.2

The N+1 trap

Fetch 50 orders, then access each order’s user inside a loop. If the ORM lazily loads each user separately, one query for the orders plus 50 for the users makes 51 database calls. Eager loading, such as Entity Framework’s Include(o => o.User), fetches the related data up front instead. section 6.8.1

Figure 6.13 The N+1 Performance Trap: Fetching related child records inside a loop cripples your database with excessive network chatter. The architectural fix is to batch those requests into a single trip using Eager Loading or a DataLoader.

The diagram contrasts fetching related records in a loop with batching the requests. section 6.8.1

Watch for other leaks in the abstraction:

  • Overfetching: Loading a user with a 5 MB profile picture also loads the image when only a name and email are needed. Use a projection to select just those fields. section 8.3.2
  • Untranslatable logic: A C# helper function in a query may not translate to SQL. Older Entity Framework versions could download and filter a large dataset; EF Core 3+ throws a runtime exception. Rewrite the logic for SQL, or explicitly fetch only necessary data before filtering in memory. section 8.3.2

An ORM can save data, but it cannot design the schema or remove the database’s CPU, memory, and I/O constraints. Design queries and data structures with the database in mind. section 8.4 section 8.10

How Grokking Software Architecture covers it

How deep it goes
Explains declarative querying and common performance leaks, including eager loading, projections, and untranslatable query logic.
Examples
Uses an active-user query, an orders-and-users N+1 scenario, and oversized profile images to illustrate ORM behavior.
What you can do after
Diagnoses common ORM query problems and selects eager loading, projections, or SQL-compatible logic as appropriate.
How it connects
Connects ORMs to object-relational impedance mismatch, SQL injection, N+1 queries, and schema design.

Also explained in

Compare how 9 books explain object-relational mapper →

Related concepts

This explainer was generated by Manning's knowledge graph from the text of Grokking Software Architecture. The section links point to the parts of the book it draws on.