getting an error : System.LimitException: Too many SOQL queries: 101 when deploying

Question

All the test classes pass when I run them but I get validation error when I try to deploy:

test class name: RELVSRenewalControllerTest

method name: testaddServicedCommunityObjectNoFile

error :

System.LimitException: Too many SOQL queries: 101 Stack Trace: Trigger.contactUserRole: line 7, column 1

 Test class:

   @IsTest static void testaddServicedCommunityObjectNoFile() {
      Integer assertCountServicedCommunityCurrent = 0;
      try {
        System.debug('inside testclass - testaddServicedCommunityObjectNoFile');
        Id p = [select id from profile where name = 'REL VSA Community User'].id;
        //System.debug('P into test routine - ' + p);
        UserRole userrole = [Select id, name from userrole where portalType = 'None' limit 1];
        System.debug('***userrole ***'+ userrole);
        List<Contact> aCommunityContactList = [select ID, lastName from contact where lastName like '%Community%'];
        System.debug('# of contacts aCommunityContactList - ' + aCommunityContactList.size());
       // System.debug('$$$Contact first Name$$-'+ aCommunityContactList.firstName);

        List<User> aUserList = new List<User> ();
        For (Contact aCommunityUser : aCommunityContactList) {
            //system.debug('Account Id - ' + aCommunityUser.AccountId);
            aUserList.add(new User(alias = aCommunityUser.LastName.right(8), email = aCommunityUser.LastName + '@noemail.com',
                                   emailencodingkey = 'UTF-8', lastname = aCommunityUser.LastName, languagelocalekey = 'en_US',
                                   localesidkey = 'en_US', profileid = p, country = 'United States', IsActive = true,
                                   ContactId = aCommunityUser.Id, //userroleid=userrole.id,
                                   timezonesidkey = 'America/Los_Angeles', username = aCommunityUser.LastName.right(8) + '@noemail.com'));
         System.debug('$$$Contact Last Name$$-'+ aCommunityUser.lastName);
         System.debug('$$$Contact ContactId $$-'+ aCommunityUser.Id);
        }
        insert(aUserList);

        List<User> myTestUsers = [SELECT id, Name From user where email like '%@noemail.com%'];
        system.debug('# of users = ' + myTestUsers.size());


        //Check When Lookup value null
        For (User myTestUser : myTestUsers) {
            //System.debug('myTester: ' + myTestUser + myTestUser.Name);
            system.runAs(myTestUser) {
                //Remove - todo
                List<Contact> tempContactList = [select id from contact];
                system.debug('Plain contact query count - ' + tempContactList.size());
                //Remove - todo - end
                ID contactId = [Select contactid from User where id = :Userinfo.getUserid()].contactId;
                ID aAccountID = [Select AccountID from Contact where id = :contactid].AccountId;
                system.debug('Account ID Processed for ' + myTestUser.Name + ' is ' + aAccountID);
                List<REL_VS_Submission__c> myTestSubmissions = [SELECT id From REL_VS_Submission__c where REL_VS_Account__c = :aAccountID];
                PageReference pageRef = Page.RELVSRenewal1;
                Test.setCurrentPage(pageRef);
                ApexPAges.StandardController sc = new ApexPages.StandardController(myTestSubmissions[0]);
                RELVSRenewalController atestcontroller = new RELVSRenewalController(sc);
                atestcontroller.addServicedCommunityObject();
                system.debug('Contact size returned by CC addServicedCommunityObject - ' + atestcontroller.anewServicedCommunity.size());
                assertCountServicedCommunityCurrent = assertCountServicedCommunityCurrent + atestcontroller.anewServicedCommunity.size();

            }
        }
        System.assertEquals(4, assertCountServicedCommunityCurrent);
        assertCountServicedCommunityCurrent=0;
        For (User myTestUser : myTestUsers) {
            //System.debug('myTester: ' + myTestUser + myTestUser.Name);
            system.runAs(myTestUser) {
                //Remove - todo
                List<Contact> tempContactList = [select id from contact];
                system.debug('Plain contact query count - ' + tempContactList.size());
                //Remove - todo - end
                ID contactId = [Select contactid from User where id = :Userinfo.getUserid()].contactId;
                ID aAccountID = [Select AccountID from Contact where id = :contactid].AccountId;
                system.debug('Account ID Processed for ' + myTestUser.Name + ' is ' + aAccountID);
                List<REL_VS_Submission__c> myTestSubmissions = [SELECT id From REL_VS_Submission__c where REL_VS_Account__c = :aAccountID];
                PageReference pageRef = Page.RELVSRenewal1;
                Test.setCurrentPage(pageRef);
                ApexPAges.StandardController sc = new ApexPages.StandardController(myTestSubmissions[0]);
                RELVSRenewalController atestcontroller = new RELVSRenewalController(sc);

                atestcontroller.anewCommunity.REL_VS_County__c = [select id from REL_VS_County__c limit 1].id;
                atestcontroller.anewCommunity.REL_VS_Community__c = [select id from REL_VS_Community__c limit 1].id;
                atestcontroller.communityAddedReason='Expanding into new service area' ;
                // atestcontroller.acommunityDocumentType='Expired Franchise Agreement';
                //atestcontroller.principalofficerfilebody = Blob.valueOf('testfile');
                //atestcontroller.principalofficerfileName = 'testfile.pdf';

                atestcontroller.addServicedCommunityObject();
                system.debug('ServicedCommunity size returned by CC addServicedCommunityObject - ' + atestcontroller.anewServicedCommunity.size());
                assertCountServicedCommunityCurrent = assertCountServicedCommunityCurrent + atestcontroller.anewServicedCommunity.size();

            }
        }
        System.assertEquals(6, assertCountServicedCommunityCurrent);
    }

    catch(Exception e) {

        throw e;
    }

}

There is trigger which was written by another developer.

   Trigger contactUserRole on Contact (after insert, after update) {
    Id profileAccountManagerId, profileHRManagerId;
  Profile profileAccountManager = [SELECT Id FROM Profile WHERE name = 
 'Account Manager Community Profile'];
   profileAccountManagerId = profileAccountManager.Id;
  Profile profileHRManager = [SELECT Id FROM Profile WHERE name = 'HR 
  Manager Community Profile'];
  profileHRManagerId = profileHRManager.Id;
  List<User> users = [SELECT Id, ContactId FROM User WHERE ContactId IN 
:Trigger.newMap.keySet()];
for (User u :users) {
  Contact c = Trigger.newMap.get(u.ContactId);
  if (c.Portal_Access_Rights__c == 'Account Manager') {
        updateCommunityUsers.updateUser(u.Id, profileAccountManagerId);
  } else if (c.Portal_Access_Rights__c == 'HR Manager') {
        updateCommunityUsers.updateUser(u.Id, profileHRManagerId);
  } else if (c.Portal_Access_Rights__c == 'No Portal Access') {
    updateCommunityUsers.updateUser(u.Id, null);
  }
  }
}

Is there a way I can change my test class rather than changing the trigger?

Answer 1

Couple of options:

1. Split your test into different methods. Separate your actual test runs (looping over a user list) into two @isTest methods, and move your data creation to a @testSetup method.
2. Separate your test with Test.startTest() and Test.stopTest(). Anything inside these methods has their own set of limits. You can create your data, then wrap your actual test execution in these methods to make sure your limits are being reached.
3. Check the Limits class before executing code, and behave differently at a threshold (ie, dont query related objects, or run excess code)
4. Improve your queries - query all your profiles together, then separate them into variables with a loop.

One or more of these should get you there. This is more of a band-aid than anything else, I'd recommend really digging into it and trying to optimize it.

Answer 2

You are running no fewer than 10 queries per User record in your test set. In your first for loop:

            List<Contact> tempContactList = [select id from contact];
            ID contactId = [Select contactid from User where id = :Userinfo.getUserid()].contactId;
            ID aAccountID = [Select AccountID from Contact where id = :contactid].AccountId;
            List<REL_VS_Submission__c> myTestSubmissions = [SELECT id From REL_VS_Submission__c where REL_VS_Account__c = :aAccountID];

and the second:

            List<Contact> tempContactList = [select id from contact];

            ID contactId = [Select contactid from User where id = :Userinfo.getUserid()].contactId;
            ID aAccountID = [Select AccountID from Contact where id = :contactid].AccountId;
            List<REL_VS_Submission__c> myTestSubmissions = [SELECT id From REL_VS_Submission__c where REL_VS_Account__c = :aAccountID];
            atestcontroller.anewCommunity.REL_VS_County__c = [select id from REL_VS_County__c limit 1].id;
            atestcontroller.anewCommunity.REL_VS_Community__c = [select id from REL_VS_Community__c limit 1].id;

This means you'll exceed the SOQL transaction limit as soon as you have 10 test users in your batch, and possible sooner, since the limit applies across the transaction and does include queries fired by this trigger and any other (non-managed) triggers in operation.

It's not clear to me whether you're using System.runAs() to test sharing rules or for a different purpose, or why you have two for loops here - I'm just not sure exactly what you're testing. You need to bulkify your queries, reduce the size of the test set of users, or refactor your test to reduce the number of queries that need to be performed in a single transaction.