QueryException: List has no rows for assignment in test class

Question

I have included the following code and dependencies (fields) in my change set and I'm encountering the following error:

LeadCreateOppTrigger.LeadCreateOppTrigger(), Details: System.QueryException: List has no rows for assignment to SObject Class.LeadCreateOppTrigger.LeadCreateOppTrigger: line 45, column 1

It's important to mention this test class works and causes 95% code coverage for the Trigger it is meant to address in testing.

Please advise on how this would be remedied:

(Line 45 is indicated below)

@isTest
private class LeadCreateOppTrigger {

 public TestMethod static void LeadCreateOppTrigger() {

    Lead myLead = new Lead ( //Address = '123 Main St. Anywhere USA',
                            LastName = 'Doe',
                            FirstName = 'John',
                            Company = 'AnyCompany',
                            Desired_City1__c = 'AnyCity1',
                            Desired_City2__c = 'AnyCity2',
                            Desired_City3__c = 'AnyCity3',
                            Desired_County1__c = 'AnyCounty1',
                            Desired_County2__c = 'AnyCounty2',
                            Desired_Property_Description__c = 'Any Description',
                            Desired_State1__c = 'AnyState1',
                            Desired_State2__c = 'AnyState2',
                            Desired_State3__c = 'AnyState3',
                            Pre_Approval_Amount__c = 12345.00,
                            Pre_Approval_Granted__c = true,
                            Salutation__c = 'Mr.');

    insert myLead;

    myLead.Real_Estate_Agent_Assigned__c = true;
    update myLead;

    //Opportunity[] myOpp = new Opportunity[1];
    /*Line 45*/ Opportunity myOpp = [SELECT
                            Desired_City1__c,
                            Desired_City2__c,
                            Desired_City3__c,
                            Desired_County1__c,
                            Desired_County2__c,
                            Desired_Property_Description__c,
                            Desired_State1__c,
                            Desired_State2__c,
                            Desired_State3__c,
                            Lead_Id__c,
                            Pre_Approval_Amount__c,
                            Pre_Approval_Granted__c,
                            Real_Estate_Agent_Assigned__c,
                            Salutation__c
                        FROM Opportunity WHERE Lead_Id__c =: myLead.Id LIMIT 1];

        System.assertEquals(myOpp.Desired_City1__c, 'AnyCity1');
        System.assertEquals(myOpp.Desired_City2__c, 'AnyCity2');
        System.assertEquals(myOpp.Desired_City3__c, 'AnyCity3');
        System.assertEquals(myOpp.Desired_County1__c, 'AnyCounty1');
        System.assertEquals(myOpp.Desired_County2__c, 'AnyCounty2');
        System.assertEquals(myOpp.Desired_Property_Description__c, 'Any Description');
        System.assertEquals(myOpp.Desired_State1__c, 'AnyState1');
        System.assertEquals(myOpp.Desired_State2__c, 'AnyState2');
        System.assertEquals(myOpp.Desired_State3__c, 'AnyState3');
        System.assertEquals(myOpp.Lead_Id__c, String.valueOf(myLead.Id));
        System.assertEquals(myOpp.Pre_Approval_Amount__c, 12345.00);
        System.assertEquals(myOpp.Pre_Approval_Granted__c, true);
        System.assertEquals(myOpp.Real_Estate_Agent_Assigned__c, true);     
        System.assertEquals(myOpp.Salutation__c, 'Mr.');
    } // End TestTriggerCreatemyOpp()
} // End Lead_CreatemyOpp_Trigger_Test

Answer 1

From the perspective of your test, your trigger is not working because the opportunity is not being created (expected behavior). The query to retrieve it is not returning any result. Independently from that, if you want to avoid that System.QueryException: List has no rows for assignment exception, you can simply change your myOpp variable for a list.

List<Opportunity> myOpp = [SELECT
                        Desired_City1__c,
                        Desired_City2__c,
                        Desired_City3__c,
                        Desired_County1__c,
                        Desired_County2__c,
                        Desired_Property_Description__c,
                        Desired_State1__c,
                        Desired_State2__c,
                        Desired_State3__c,
                        Lead_Id__c,
                        Pre_Approval_Amount__c,
                        Pre_Approval_Granted__c,
                        Real_Estate_Agent_Assigned__c,
                        Salutation__c
                    FROM Opportunity WHERE Lead_Id__c =: myLead.Id LIMIT 1];

The exception should not be thrown, however you'll have to double check the trigger's code if the test does not pass.