The Bulkified Query-Map Pattern: Avoiding N+1 SOQL in Triggers

The most common governor limit violation in Apex triggers is the SOQL query limit: 100 queries per synchronous transaction. When developers place a SOQL query inside a for loop that iterates over Trigger.new, each record fires a separate query. With 200 records in a single trigger batch, the code attempts 200 queries and fails at record 101. This anti-pattern is sometimes called "N+1 SOQL" and is a core PD1 exam topic.

The Anti-Pattern: SOQL Inside the Loop

// BAD: One query per record -- will hit governor limit at 101 records
trigger LineItemTrigger on Line_Item__c (before insert) {
    for (Line_Item__c item : Trigger.new) {
        Product2 product = [
            SELECT Id, Name, Family, Unit_Weight__c
            FROM Product2
            WHERE Id = :item.Product2Id
        ];  // Query runs EVERY iteration

        item.Product_Family__c = product.Family;
        item.Weight__c = item.Quantity__c * product.Unit_Weight__c;
    }
}

If a data load inserts 500 line items, this trigger fires across three chunks (200 + 200 + 100). The first chunk alone attempts 200 SOQL queries, far exceeding the 100-query limit.

The Correct Pattern: Query Once, Map, Loop

The solution is three steps:

1. Collect all unique lookup IDs from the trigger records.
2. Query all related records in a single SOQL statement.
3. Map the results by ID and use Map.get() inside the loop.

trigger LineItemTrigger on Line_Item__c (before insert) {
    // Step 1: Collect all unique Product IDs
    Set<Id> productIds = new Set<Id>();
    for (Line_Item__c item : Trigger.new) {
        if (item.Product2Id != null) {
            productIds.add(item.Product2Id);
        }
    }

    // Step 2: Single bulk query -- 1 query regardless of record count
    Map<Id, Product2> productMap = new Map<Id, Product2>([
        SELECT Id, Name, Family, Unit_Weight__c
        FROM Product2
        WHERE Id IN :productIds
    ]);

    // Step 3: Loop and use Map.get() -- zero additional queries
    for (Line_Item__c item : Trigger.new) {
        Product2 product = productMap.get(item.Product2Id);
        if (product != null) {
            item.Product_Family__c = product.Family;
            item.Weight__c = item.Quantity__c * product.Unit_Weight__c;
        }
    }
}

This pattern uses exactly one SOQL query regardless of whether the trigger processes 1 record or 200.

The Map Constructor Shortcut

The Map<Id, SObject> constructor accepts a List<SObject> directly from a SOQL query:

Map<Id, Product2> productMap = new Map<Id, Product2>([
    SELECT Id, Name, Family FROM Product2 WHERE Id IN :productIds
]);

This single line executes the query and populates the map with each record's Id as the key. It is equivalent to querying into a list, then iterating to build the map manually -- but in one step.

Safe Navigation for Cross-Object Fields

When accessing fields through a lookup relationship, use the safe navigation operator (?.) to handle null references gracefully:

for (Line_Item__c item : Trigger.new) {
    Product2 product = productMap.get(item.Product2Id);
    item.Product_Family__c = product?.Family;
    item.Weight__c = (item.Quantity__c != null && product?.Unit_Weight__c != null)
        ? item.Quantity__c * product.Unit_Weight__c
        : null;
}

Without ?., a null product reference throws a NullPointerException. The safe navigation operator returns null instead, which is typically the desired behavior for optional lookups.

Multiple Related Objects

The pattern scales to multiple related objects. Each object gets its own collect-query-map sequence:

// Collect IDs for both lookups
Set<Id> productIds = new Set<Id>();
Set<Id> supplierIds = new Set<Id>();

for (Line_Item__c item : Trigger.new) {
    productIds.add(item.Product2Id);
    supplierIds.add(item.Supplier__c);
}

// Two queries total, regardless of record count
Map<Id, Product2> products = new Map<Id, Product2>(
    [SELECT Id, Name, Family FROM Product2 WHERE Id IN :productIds]
);
Map<Id, Account> suppliers = new Map<Id, Account>(
    [SELECT Id, Name, Supplier_Code__c FROM Account WHERE Id IN :supplierIds]
);

Governor Limit Comparison

Approach	SOQL Queries for 200 Records	SOQL Queries for 1000 Records
Query inside loop	200 (fails at 101)	1000 (fails at 101)
Query-Map pattern	1	1

The Query-Map pattern is not optional -- it is the minimum standard for production Apex code. Any trigger that queries inside a loop is a latent production bug waiting for sufficient data volume to manifest.