Here’s a lesson I learned while making our Cloud Developer Challenge entry.
Take a look at these lines of code and tell me which ones are OK and which ones are dangerous:
integer count = [SELECT count() FROM Contact]; Contact c1 = [SELECT Id FROM Contact LIMIT 1]; Contact c2 = [SELECT Id FROM Contact LIMIT 1]; Id first = [SELECT Id FROM Contact LIMIT 1].Id; Contact contacts = [SELECT Id FROM Contact LIMIT 1]; Contact allContacts = [SELECT Id FROM Contact];
If you executed the above code on most systems, it will run just fine. However, there is a situation in which you’ll get the dreaded
List has no rows for assignment to SObject error. Can you figure out which one?
It’s when there’s no objects to return!
For example, if you have a system with no Contracts, try running the above code with
Contract in place of
Contact and you’ll get:
ERROR - Evaluation error: System.QueryException: List has no rows for assignment to SObject
ERROR - Evaluation error: AnonymousBlock: line 2, column 15
What happened? Well, these 3 lines will all give an error if there’s no row found:
Contact c1 = [SELECT Id FROM Contact LIMIT 1]; Contact c2 = [SELECT Id FROM Contact LIMIT 1]; Id first = [SELECT Id FROM Contact LIMIT 1].Id;
SELECT normally returns an array/list, these statements are using the shorthand syntax that assumes only one row is returned. What’s not obvious is that it also assumes that exactly one row is returned!
While this is unlikely to occur for
Contact, it is highly likely to occur for any custom objects you create, especially when a
WHERE statement is used that might return zero rows, such as:
Player__c player = [SELECT Id from Player__c where Name = :username]; if (player != null) p = player.Id;
The above code will fail if there is no
Player__c record with the matching username. It doesn’t actually return a
It would be safer to do the following:
Player__c players = [SELECT Id from Player__c where Name = :username]; if (players.size() > 0) p = players.Id;
It’s one of those situations for which you wouldn’t normally think of creating a test, so it’s safer to just avoid the possibility rather than making an assumption about your data.
The Bottom Line
- The shorthand syntax that expects one row to be returned from a
SELECTstatement is very handy
- It’s also dangerous if no rows are found!