User & Account - System.LimitException: Too many SOQL queries:101

Question

I have this code:

List<User> lUsers = [select federationIdentifier, email, firstName, lastName, ContactId, isActive from User where isActive=True and FederationIdentifier != ''];
List<Account> lAccounts = new List<Account>();


for (User U:lUsers)
{
    Account uAccount = [select Id, PersonContactId, net_id__pc, personemail, firstName, LastName from account where PersonContactId = :U.ContactId].get(0);
    uAccount.net_id__pc = U.federationIdentifier;
    lAccounts.add(uAccount);  
}

system.debug(lAccounts);

But I am getting an error:

System.LimitException: Too many SOQL queries:101.

Is there any other way to write this code and avoid this problem?

Answer 1

You need to NEVER have a query inside of a loop.

//Get set of Ids so that you can query in bulk
Set<Id> contactIds = new Set<Id>();
for (User usr: lUsers)
{
  contactIds.add(usr.ContactId);
}

//Place info into a map
Map<Id, Account> personToAccMap = new Map<Id, Account>();
for (Account acc: [SELECT PersonContactId, net_Id__pc, PersonEmail, FIrstName, LastName FROM Account WHERE PersonContactId IN :contactIds])
{
  personToAccMap.put(acc.PersonContactId, acc);
}

for (User U:lUsers)
{
    //You can now grab without a query
    Account uAccount = personToAccMap.get(U.ContactId);
    uAccount.net_id__pc = U.federationIdentifier;
    lAccounts.add(uAccount);  
}

system.debug(lAccounts);

Answer 2

You can use a Map. A typical example would look something like:

Set<Id> contactIds = new Set<Id>();
for (User lUser : lUsers) contactIds.add(lUser.ContactId);
contactIds.remove(null);

Map<Id, Account> contactIdToAccount = new Map<Id, Account>();
for (Account account : [SELECT PersonContactId FROM Account])
    if (!contactIdToAccount.containsKey(account.PersonContactId))
        contactIdToAccount.put(account.PersonContactId, account);

for (User lUser : lUsers)
{
    Account firstPersonAccount = contactIdToAccount.get(lUser.ContactId);
    // do stuff
}

However, note that there can be multiple matches.* It probably makes more sense to update all of them, and also use a separate Map to de-duplicate.

Set<Id> contactIds = new Set<Id>();
for (User lUser : lUsers) contactIds.add(lUser.ContactId);
contactIds.remove(null);

Map<Id, List<Account>> contactIdToAccounts = new Map<Id, List<Account>>();
for (Account account : [SELECT PersonContactId FROM Account])
{
    if (!contactIdToAccount.containsKey(account.PersonContactId))
        contactIdToAccount.put(account.PersonContactId, new List<Account>());
    contactIdToAccount.get(account.PersonContactId).add(account);
}

Map<Id, Account> uniqueAccounts = new Map<Id, Account>();
for (User lUser : lUsers)
{
    List<Account> personAccounts = contactIdToAccount.get(lUser.ContactId);
    if (!personAccounts == null)
    {
        for (Account personAccount : personAccounts)
        {
            // do stuff
            uniqueAccounts.put(personAccount.Id, personAccount);
        }
    }
}

* The relationship is set up as many to one. I am not certain if you can actually associate multiple Person Accounts to the same Contact.