101 SOQL Queries Limit Exception

Question

Even when I think that I have bulkified my code, it is still throwing a SOQL 101 Limit Exception on the code below on Line 5 (For loop One). What I am doing Wrong?

UserIds is just a list of Oppportunties.OwnerIds

global static map<Id, Contact> getEmployeeInfos(list<Id> userIds)
{
       map<Id, Contact> employeeContacts = new map<Id, Contact>();

       for (Contact c : [SELECT Name, User__c, User__r.IsActive, LOB__c, Email, Phone,LOB__r.Short_Name__c, LOB__r.Name FROM Contact WHERE User__c IN :userIds])
       {
            employeeContacts.put(c.User__c, c);
       }

       return employeeContacts;

Complete scenario:

Failed Test Class: OpportunityFeeCalculationTest

Failed Class Methods: 1. test 2. testOverFirstAUM

Class: OpportunityFeeCalculationTest

    @isTest
    public static void test()
    {        
        CoreDataTest.getRelationships();

        Test.startTest();


        Opportunity_Fee_Schedule__c f1 = new Opportunity_Fee_Schedule__c(
            Opportunity__c=CoreDataTest.Opportunities[0].id,
            Tier_Description__c='First',
            Tier_AUM_Level__c=100000,
            Tier_Fee_Bps__c=33
        );

        Opportunity_Fee_Schedule__c f2 = new Opportunity_Fee_Schedule__c(
            Opportunity__c=CoreDataTest.Opportunities[0].id,
            Tier_Description__c='Second',
            Tier_AUM_Level__c=300000,
            Tier_Fee_Bps__c=43
        );

        Opportunity_Fee_Schedule__c f3 = new Opportunity_Fee_Schedule__c(Opportunity__c=CoreDataTest.Opportunities[0].id,Tier_Description__c='Thereafter',Tier_Fee_Bps__c=33);
        insert f1;
        insert f2;
        insert f3;

        list<Opportunity> lista=new list<Opportunity>();
        lista.add(CoreDataTest.Opportunities[0]);

        OpportunityFeeCalculation_Core.calculateManagementFees(lista);       
    }  


    @isTest
    public static void testOverFirstAUM()
    {        
        CoreDataTest.getRelationships();

        list<Opportunity_Fee_Schedule__c> scheds = new list<Opportunity_Fee_Schedule__c> {
            new Opportunity_Fee_Schedule__c(
                Opportunity__c=CoreDataTest.Opportunities[0].id,
                Tier_Description__c='First',
                Tier_AUM_Level__c=300000,
                Tier_Fee_Bps__c=33
            )
            ,
            new Opportunity_Fee_Schedule__c(
                Opportunity__c=CoreDataTest.Opportunities[0].id,
                Tier_Description__c='Second',
                Tier_AUM_Level__c=100000,
                Tier_Fee_Bps__c=43
            )
            ,
            new Opportunity_Fee_Schedule__c(
                Opportunity__c=CoreDataTest.Opportunities[0].id,
                Tier_Description__c='Fourth',
                Tier_AUM_Level__c=900000,
                Tier_Fee_Bps__c=43
            )
            ,
            new Opportunity_Fee_Schedule__c(
                Opportunity__c=CoreDataTest.Opportunities[0].id,
                Tier_Description__c='Third',
                Tier_AUM_Level__c=100000,
                Tier_Fee_Bps__c=43
            )
            ,
            new Opportunity_Fee_Schedule__c(
                Opportunity__c=CoreDataTest.Opportunities[0].id,
                Tier_Description__c='Thereafter',
                //Tier_AUM_Level__c=100,
                Tier_Fee_Bps__c=3
            )

        };        

        Test.startTest();        
        insert scheds;        

        CoreDataTest.Opportunities[0].Assets__c = 2500000;
        update CoreDataTest.Opportunities[0];

        list<Opportunity> lista=new list<Opportunity>{
            CoreDataTest.Opportunities[0]
        };        

        OpportunityFeeCalculation_Core.calculateManagementFees(lista);   
    }
}

---

error: System.LimitException: Too many SOQL queries: 101

Stack Trace: Class.UtilitiesContact.getEmployeeInfos: line 96, column 1 Class.OpportunityTriggerHandler_Core.beforeUpdate: line 232, column 1 Trigger.OpportunityBeforeUpdate_Core: line 5, column 1

---

Trigger.OpportunityBeforeUpdate_Core:

trigger OpportunityBeforeUpdate_Core on Opportunity (before update) {

    if (UtilitiesSObject.RunTrigger('OpportunityBeforeUpdate_Core'))
    {
Line 5: new OpportunityTriggerHandler_Core().beforeUpdate(trigger.new, trigger.newMap, trigger.oldMap);
    }
}

---

Class.OpportunityTriggerHandler_Core.beforeUpdate

public void beforeUpdate(list<Opportunity> triggerNew,map<Id, Opportunity> triggerNewMap,map<Id, Opportunity> triggerOld)
{
    if (UtilitiesUser.UserContactInfo.Service_Account__c == false)
    {
        calculateManagementFees(triggerNew,triggerold);
        calculateProbability(triggerNew, triggerOld);

        map<string,boolean> keys = new map<string,boolean>();//lob+account
        set<id> lobs = new set<id>();
        set<id> companies = new set<id>();
        set<id> oppsAffected = new set<id>();
        list<id> ownerids = new list<id>();
        set<id> oppids = new set<id>();

        for (Opportunity o : triggerNewMap.values())
        {
            ownerids.add(o.ownerid);

            o.Restricted__c=o.Restricted_Formula__c;
            o.Platinum_Client_Group__c=o.Platinum_Client_Group_Formula__c;

            keys.put(o.LOB_Owner__c+'-'+o.Accountid,false);
            keys.put(o.LOB_Owner__c+'-'+o.Record_Keeper__c,false);
            keys.put(o.LOB_Owner__c+'-'+o.Consultant__c,false);
            lobs.add(o.LOB_Owner__c);
            companies.add(o.Accountid);
            companies.add(o.Record_Keeper__c);
            companies.add(o.Consultant__c);
            oppids.add(o.id);
        }   

        for(Company_LOB_Relationship__c clr:[
            select id,Company__c,LOB__c,Opt_Out__c 
            from Company_LOB_Relationship__c 
            where  LOB__c in :lobs
            AND Company__c IN :companies
        ])
        {
            string key = clr.LOB__c+'-'+clr.Company__c;
            if(keys.get(key)!=null)
            {
                keys.put(key,clr.Opt_Out__c);
            }        
        }

//Set Owner LOB
Line 232: map<Id, Contact> employeesByUser = UtilitiesContact.getEmployeeInfos(ownerids);

---

Class.UtilitiesContact.getEmployeeInfos: line 96, column 1

global static map<Id, Contact> getEmployeeInfos(list<Id> userIds)
    {
        map<Id, Contact> employeeContacts = new map<Id, Contact>();

Line 96: for (Contact c : [SELECT Name, User__c, User__r.IsActive, LOB__c, Email, Phone,LOB__r.Short_Name__c, LOB__r.Name FROM Contact WHERE User__c IN :userIds])
        {
            employeeContacts.put(c.User__c, c);
        }

        return employeeContacts;
    }

Answer 1

Your code snippet does not contain enough information to figure out your issue. The SOQL 101 error is thrown when 101 SOQL statements are executed in a single request. It does not mean that the statement the error was thrown on was executed 101 times, it only means that it was the 101st SOQL statement. There could be another piece of code that ran before the code you have presented and that code executed 100 SOQL statements.

To debug this issue you should have a look in the log files. If you search for SOQL_EXECUTE_BEGIN you will find all the SOQL statements that have been called, there should be a repeating SOQL statement in there somewhere which is where the probably actually is.

Answer 2

This snippet of code is not hitting the governor limit by itself.

At a guess, I would say that you're calling getEmployeeInfos() in a loop.

Please post the rest of your code to confirm.

Answer 3

Guess too late for answer but best guess here is your trigger Trigger.OpportunityBeforeUpdate_Core do not have recursion handling. Somewhere in your trigger you are updating opportunity which leads to fire this trigger again and again leading to 101 SOQL. Handle the recursion in your trigger.