Deep Thought

Object Relational Impedance Mismatch

11 min read

Object–Relational Impedance Mismatch (ORIM)

Learning goals

After this lesson you should be able to explain:

  • why object-oriented (OO) data and
    relational database data don’t “fit” perfectly
  • the main types of mismatches
    (structure, identity, relationships, inheritance, types, queries, transactions)
  • what an ORM mapper must do internally
    (query translation, materialization, change tracking, unit of work)
  • pros and cons of using an ORM

The big idea

Object-oriented programs and relational databases solve different problems:

  • OO world: rich objects, behavior, graphs of references, inheritance, encapsulation
  • Relational world: tables, rows, columns, set-based operations, joins, normalization

Object–Relational Impedance Mismatch:
the friction caused by representing an object graph in a relational model.

Example domain: School

OO view (classes and references)

classDiagram
direction LR

class Student {
  +string FirstName
  +string LastName
  +Address Address
  +List~Enrollment~ Enrollments
  +decimal GetAverageGrade()
}

class Address {
  +string Street
  +string City
  +string Zip
}

class Course {
  +string Title
  +int Credits
}

class Enrollment {
  +Student Student
  +Course Course
  +DateTime EnrolledAt
  +string? Grade
}

Student "1" --> "1" Address : has
Student "1" --> "*" Enrollment : has
Course "1" --> "*" Enrollment : has
Enrollment "*" --> "1" Student : belongs to
Enrollment "*" --> "1" Course : belongs to

Relational view (tables and foreign keys)

erDiagram
  STUDENT ||--o{ ENROLLMENT : enrolls
  COURSE  ||--o{ ENROLLMENT : is_taken_in
  STUDENT ||--|| ADDRESS : has

  STUDENT {
    int StudentId PK
    string FirstName
    string LastName
  }

  ADDRESS {
    int StudentId "PK, FK"
    string Street
    string City
    string Zip
  }

  COURSE {
    int CourseId PK
    string Title
    int Credits
  }

  ENROLLMENT {
    int StudentId "PK, FK"
    int CourseId "PK, FK"
    datetime EnrolledAt
    string Grade

  }

Mismatch types

Let’s explore different scenarios, that cause friction and see what the mapper must do to deal with it.

1) Structural mismatch: objects are graphs, tables are grids

Objects contain references to other objects (graphs).
Tables store values in rows and use keys to connect data.

Example

In C#, Student.Address is a reference to an Address object.

In SQL, Address is typically stored in:

  • its own table (normalized) or
  • columns in the Student table (denormalized)

OO code

public sealed class Student
{
    public int Id { get; init; }
    public string FirstName { get; set; } = "";
    public string LastName  { get; set; } = "";
    public Address Address  { get; set; } = new();
}
 
public sealed class Address
{
    public string Street { get; set; } = "";
    public string City   { get; set; } = "";
    public string Zip    { get; set; } = "";
}

Relational schema (normalized)

CREATE TABLE Student (
  StudentId   INT PRIMARY KEY,
  FirstName   NVARCHAR(50) NOT NULL,
  LastName    NVARCHAR(50) NOT NULL
);
 
CREATE TABLE Address (
  StudentId   INT PRIMARY KEY,
  Street      NVARCHAR(80) NOT NULL,
  City        NVARCHAR(60) NOT NULL,
  Zip         NVARCHAR(12) NOT NULL,
  CONSTRAINT FK_Address_Student FOREIGN KEY (StudentId) 
    REFERENCES Student(StudentId)
);

What the mapper must do

  • decide how to split/merge objects ↔ rows
  • perform joins to rebuild the object graph
  • avoid duplicating the same objects when multiple rows represent one object (see Identity mismatch)

2) Identity mismatch: object identity vs primary keys

In OO:

  • two references can point to the same instance (ReferenceEquals(a, b) == true)
  • identity is in memory, not a database key

In relational databases:

  • identity is represented by primary keys
  • equality is based on values / keys

Example problem

If your query joins Student with Enrollment, the same student row may appear many times.

SQL (one student repeated across rows)

SELECT s.StudentId, s.FirstName, e.CourseId, e.EnrolledAt
FROM Student s
JOIN Enrollment e ON e.StudentId = s.StudentId
WHERE s.StudentId = 1;

Result rows (conceptually):

StudentIdFirstNameCourseIdEnrolledAt
1Ada102026-01-10
1Ada122026-01-15

What the mapper must do

When “materializing” objects from rows:

  • detect that StudentId = 1 is the same student
  • create one Student object and attach multiple enrollments
  • often uses an identity map (a cache keyed by PK)
// Conceptual identity map
var studentCache = new Dictionary<int, Student>();
 
Student GetOrCreateStudent(int studentId, Func<Student> factory)
{
    if (!studentCache.TryGetValue(studentId, out var s))
    {
        s = factory();
        studentCache[studentId] = s;
    }
    return s;
}

3) Relationship mismatch: references vs foreign keys + join tables

OO relationships are easy: student.Enrollments.Add(...)
Relational relationships need:

  • foreign keys
  • sometimes a join table (many-to-many)

Many-to-many example

A student takes many courses, and a course has many students.

OO: Student.Courses could be List<Course>
Relational: you usually need Enrollment(StudentId, CourseId, ...)

What the mapper must do

  • represent relationships as collections/references in code
  • map them to foreign keys / join tables in SQL
  • load related data:
    • eager (join now)
    • lazy (load on access)
    • explicit (load when asked)

Even if you never use “lazy loading”, your mapper still needs a strategy for loading graphs.

4) Inheritance mismatch: class hierarchies vs tables

OO uses inheritance naturally. SQL tables do not.

OO example

public abstract class Person
{
    public int Id { get; init; }
    public string Name { get; set; } = "";
}
 
public sealed class Student : Person
{
    public int Semester { get; set; }
}
 
public sealed class Teacher : Person
{
    public string Subject { get; set; } = "";
}

Relational strategies (common approaches)

A) Single Table Inheritance (STI)

One table with a discriminator column.

CREATE TABLE Person (
  PersonId INT PRIMARY KEY,
  Name NVARCHAR(80) NOT NULL,
  Kind NVARCHAR(20) NOT NULL,        -- "Student" or "Teacher"
  Semester INT NULL,
  Subject NVARCHAR(50) NULL
);

✅ fewer joins
❌ many nullable columns, constraints become tricky

B) Class Table Inheritance (CTI)

Base table + one table per subclass.

CREATE TABLE Person (
  PersonId INT PRIMARY KEY,
  Name NVARCHAR(80) NOT NULL
);
 
CREATE TABLE Student (
  PersonId INT PRIMARY KEY,
  Semester INT NOT NULL,
  FOREIGN KEY (PersonId) REFERENCES Person(PersonId)
);
 
CREATE TABLE Teacher (
  PersonId INT PRIMARY KEY,
  Subject NVARCHAR(50) NOT NULL,
  FOREIGN KEY (PersonId) REFERENCES Person(PersonId)
);

✅ normalized, strong constraints
❌ queries need joins

What the mapper must do

  • decide which strategy the schema uses
  • map rows back to the correct runtime type (Student vs Teacher)
  • generate correct INSERT/UPDATE for multi-table cases

5) Type mismatch: CLR types vs SQL types

Some types map nicely:

  • int ↔ INT
  • string ↔ NVARCHAR

Some types need decisions:

  • DateTime vs datetime2 / timezone handling
  • decimal precision/scale
  • bool vs bit
  • enums stored as int or string
  • collections (e.g., List<string>) — not a single column in a normalized schema

Example: decimal precision

public decimal GradePointAverage { get; set; }  // e.g., 3.75

If SQL uses too little precision:

-- Bad: might round unexpectedly
GradePointAverage DECIMAL(3,2) -- max 9.99, ok, but rounding can happen

What the mapper must do

  • define a mapping table: CLR type → SQL type
  • sometimes apply conversions when reading/writing
  • handle nulls (string?, int?) ↔ NULL

6) Encapsulation mismatch: behavior vs data

Objects often include behavior and invariants:

public sealed class BankAccount
{
    public int Id { get; init; }
    public decimal Balance { get; private set; }
 
    public void Deposit(decimal amount)
    {
        if (amount <= 0) throw new ArgumentOutOfRangeException(nameof(amount));
        Balance += amount;
    }
}

But a table has only values:

CREATE TABLE BankAccount (
  AccountId INT PRIMARY KEY,
  Balance DECIMAL(18,2) NOT NULL
);

What the mapper must do

  • hydrate objects without breaking invariants (sometimes via constructors/factories)
  • decide whether to set private fields / use reflection / use factory methods
    (Different ORMs choose different trade-offs.)

7) Query mismatch: LINQ is composable, SQL is set-based

OO queries often look like:

  • filter a collection
  • project to another type
  • chain operations (composition)

SQL is set-based and has its own rules and functions.

A LINQ-like query (conceptual)

// Think of Students as "queryable", not a List<Student>.
IQueryable<Student> students = repo.Students;
 
var q =
    students
        .Where(s => s.LastName.StartsWith("S"))
        .Select(s => new { s.Id, Full = s.FirstName + " " + s.LastName })
        .OrderBy(x => x.Full)
        .Take(10);

What the mapper must do

  • capture the query as an expression tree
  • translate supported operations into SQL:
    • Where → WHERE
    • Select → SELECT
    • OrderBy → ORDER BY
    • Take → TOP / LIMIT
  • handle parameters safely (avoid SQL injection)
  • execute and map rows to objects / projections

Typical translation (one possible SQL form)

SELECT TOP (10)
  s.StudentId AS Id,
  (s.FirstName + N' ' + s.LastName) AS Full
FROM Student s
WHERE s.LastName LIKE N'S%'
ORDER BY Full;

Sequence: LINQ to SQL

sequenceDiagram
autonumber
actor App as Your CS Code
participant Q as Query Provider (IQueryable)
participant T as Translator
participant DB as Database
participant M as Materializer

App->>Q: build query (Where/Select/OrderBy/Take)
Q->>T: provide expression tree
T->>T: analyze supported operations
T->>DB: send SQL + parameters
DB-->>T: return rows
T->>M: map rows → objects / projections
M-->>App: return IEnumerable results

If the mapper can’t translate something, it might:

  • throw an error (“cannot be translated”), or
  • run part of it in memory (sometimes expensive).

8) Transaction & concurrency mismatch: unit-of-work vs statement execution

In a DB, each statement is independent unless you use a transaction.

In OO apps you typically want:

  • “make several changes, then save once”
  • “either all changes succeed, or none”

What the mapper must do

  • track changes made to objects (change tracking)
  • compute SQL commands (INSERT/UPDATE/DELETE) at save time
  • wrap them in a transaction (if needed)
  • handle concurrency:
    • last-write-wins (risky)
    • optimistic concurrency using a version column

Example: optimistic concurrency column

ALTER TABLE Student ADD RowVersion ROWVERSION; -- SQL Server example

Mapper idea:

  • remember original RowVersion
  • update with WHERE StudentId = @id AND RowVersion = @old
  • if 0 rows updated → somebody else changed it

What an ORM is (conceptually)

An ORM usually provides:

  • Mapping between classes and tables (metadata)
  • Querying via composable APIs (often LINQ-like)
  • Materialization (rows → objects), including identity and relationships
  • Change tracking
  • Unit of Work (SaveChanges() concept)
  • Transaction integration
  • Migrations/schema tools (optional feature)

A minimal, ORM-agnostic mental model

Below are “fake” interfaces — not any real ORM — just to make roles clear.

public interface ITable<T>
{
    IQueryable<T> Query();      // build server-side queries
    void Add(T entity);
    void Remove(T entity);
}
 
public interface IUnitOfWork
{
    void SaveChanges();         // compute SQL & execute
    ITransaction BeginTransaction();
}
 
public interface ITransaction : IDisposable
{
    void Commit();
    void Rollback();
}

Putting it together: what the mapper actually does

Action A: Load a student with courses

Goal: a Student object with populated Enrollments and Course references.

Possible SQL (join)

SELECT
  s.StudentId, s.FirstName, s.LastName,
  e.CourseId, e.EnrolledAt, e.Grade,
  c.CourseId, c.Title, c.Credits
FROM Student s
JOIN Enrollment e ON e.StudentId = s.StudentId
JOIN Course c ON c.CourseId = e.CourseId
WHERE s.StudentId = @id;

Materialization steps

  1. Create Student once (identity map by StudentId)
  2. For each row:
    • create Enrollment
    • create Course (identity map by CourseId)
    • connect references
  3. Return the fully connected object graph
flowchart LR
A[Rows from DB] --> B["Identity Map<br/>(StudentId, CourseId...)"]
B --> C[Materializer]
C --> D[Object graph<br/>Student → Enrollments → Courses]

Action B: Save changes

Goal: you change objects in memory, then persist them.

Example in code

var student = repo.Students.Query().Single(s => s.Id == 1);
 
student.LastName = "Sato";           // changed property
repo.Enrollments.Add(new Enrollment { StudentId = 1, CourseId = 12, EnrolledAt = DateTime.UtcNow });
 
uow.SaveChanges();

What the mapper must do

  • detect that student.LastName changed (change tracking)
  • detect that a new enrollment was added
  • generate SQL in a safe order (respecting FK constraints)

Example SQL it might emit:

UPDATE Student
SET LastName = @p0
WHERE StudentId = @p1;
 
INSERT INTO Enrollment (StudentId, CourseId, EnrolledAt, Grade)
VALUES (@p2, @p3, @p4, @p5);

Common pitfalls ORMs try to protect you from

N+1 query problem

If related data is loaded one object at a time, you can accidentally run:

1 query to get students

  • N queries to get enrollments for each student
    = N+1 total queries (slow)
sequenceDiagram
autonumber
participant App
participant DB

App->>DB: SELECT * FROM Student
loop for each student
  App->>DB: SELECT * FROM Enrollment WHERE StudentId = ...
end

What a mapper might do

  • allow eager loading (join / batching)
  • cache repeated lookups
  • warn or provide profiling tools

Pros and cons of ORMs

Advantages

  • Productivity: less hand-written SQL for common CRUD tasks
  • Consistency: one mapping layer for the whole application
  • Composability: query building with reusable parts (LINQ style)
  • Safety: parameters by default → less SQL injection risk
  • Refactoring support: rename properties/classes and update mappings

Disadvantages / risks

  • Hidden complexity: SQL still happens — just not visible unless you inspect it
  • Performance traps: N+1, accidental big joins, client-side evaluation
  • Abstraction leaks: you must still understand relational concepts (keys, joins, indexing)
  • Schema control: some ORMs push you toward certain schema shapes
  • Debugging: harder to reason about “why this SQL?”

Summary checklist: what an ORM must handle

An ORM has to bridge the gaps between:

  • graphs ↔ tables
  • object identity ↔ primary keys
  • references/collections ↔ foreign keys/join tables
  • inheritance ↔ table strategies
  • CLR types ↔ SQL types
  • composable queries ↔ translatable SQL
  • unit-of-work ↔ transactions & concurrency

If you understand these mismatches, learning a specific ORM later becomes much easier — you’ll know what problem each feature is solving.

Graph View

Backlinks