score:1

Accepted answer

In case of 1st solution, EF will generate sql query which includes EXISTS statement. Then, if it exits you will execute completely different 2nd query against to the database.

In case of 2nd solution, you will send just a select ... from Persons where .. statement. And as you set EmailAddress as navigational property, if Lazy Loading is enabled then EF will generate and execute query against EmailAdress table based on personId. If Lazy Loading is not enabled, then EmailAddress will be null or empty.

As a 3rd option you use Eager Loading feature, which will let EF to generate join query and will fecth person and related EmailAddresses in one go.

So, if mostly you expect to have correct personId, then you can switch to Eager Loading mode. Lazy Loading is mostly helpful in scnearios, when you need to fetch related entities only in some cases.

By the way, I suggest you to turn on logging in EF, to see generated queries.

As a result, here is the code sample for loading related entities eagerly:

var person = Context.Persons
            .Include(s ⇒ s.EmailAddresses)
            .FirstOrDefault(x => x.Id == personId);

The key point is to add a call to Include method and pass the navigational property. Passed entity will be loaded eagerly. And at the end of query you can use any of the methods which will do immediate execution, like First, FirstOrDefault, Single, SingleOrDefault, ToList and so on. You can't use Include with Find, because the latter one is the method of DbSet. In your case the most relevant one is Single, which will automatically throw exception if there is no person in the table with the specified id.

score:1

An option:

public IEnumerable<EmailAddress> GetAddressesByPerson(int personId)
{
    var queryResults = Context.Persons
        .Where(x => x.Id == personId)
        .Select(x => new { EmailAddresses = x.EmailAddresses })
        .Single(); 

    return queryResults.EmailAddresses;
}

The above query asserts that a single Person's e-mail addresses should be returned. You could do a SingleOrDefault and then check the result for #null to customize the error message, though I tend to keep exception messages pure. We then return the selected collection. So if a person exists, but has no e-mail addresses, you'll receive an empty list. If the person doesn't exist you'll get an Expected 1, found 0 exception. If more than one person exists for the Id (shouldn't, but...) you'll get an Expected 1, found more than one exception. Don't use FirstOrDefault unless you expect more than one is possible and provide an OrderBy to ensure the data order is predictable.


Related Articles