AddThis Feed Button

Recent Items

Tags: Apex, SOQL

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;

While a 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 null.

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[0].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 SELECT statement is very handy
  • It’s also dangerous if no rows are found!

7 Responses to “List has no rows for assignment to SObject”

  1. Tom Davies Says:

    IMHO it is nocer to get an immediate runtime exception than a null reference which might blow up later.

    Is the last example really correct? That is, does SELECT return null for no rows rather than an empty list?

  2. The Enforcer Says:

    Oops! Thanks for finding the error, Tom!

    I was checking players != null, but should be checking players.size() > 0. Now corrected. <blush>

  3. Scott Hemmeter Says:

    An approach I often use is to always use a FOR loop for the query. It will only go into the loop if something is returned. Only downside is that you need to declare variables outside the loop you want to use them outside the loop. If you declare them in th loop, the system can’t see them after the loop is complete.

  4. Wes Says:

    I’ve debated with myself how to handle this sort of thing before, I blogged about it some time ago but I prefer to use a single var and surround it with a try-catch (http://developinthecloud.wordpress.com/2009/07/09/handling-system-queryexception/). Of course your method and a try-catch are simply the same thing implemented differently:) Good article.

  5. Richard Vanhook Says:

    My safeguard around this issue is to just wrap a try/catch – so line 2 above would become:

    Contact c1 = null;
    try{
    c1=[SELECT Id FROM Contact LIMIT 1];
    }catch(QueryException e){}
    if(c1 != null){
    //do something
    } else {
    //or gracefully handle no contact found
    }

  6. Jorma Soucey Says:

    Whenever possible, it’s best to try to fit a query that could return nothing inside a try–>catch pair:

    Player__c[] players = new Player__c[]();
    try{
    players = [SELECT Id from Player__c where Name = :username];}
    catch {system.queryexception players){
    return;
    }

    This avoids both the runtime error and the null reference.

  7. Kim Bailey Says:

    HELP! I need to fix this code so I can deploy a change set in our production instance but have NO idea what to change – can anyone suggest code for this:

    static testMethod void testSave() {
    // Modify the test to query for a portal user that exists in your org
    User existingPortalUser = [SELECT id, profileId, userRoleId FROM User WHERE UserRoleId null AND UserType='CustomerSuccess' LIMIT 1];
    System.assert(existingPortalUser != null, ‘This test depends on an existing test portal user to run’);