When should I use navigation properties to query the database?

Question

In my Entity Framework 6 application, I have a table of people's email addresses:

public class EmailAddress
{
    public int Id { get; set; }

    public int PersonId { get; set; }

    public string EmailAddress { get; set; }

    public virtual Person Person { get; set; }
}

And the Person object references these email addresses also:

public class Person
{
    public int Id { get; set; }
    {...}

    public virtual ICollection<EmailAddress> EmailAddresses { get; set; }
}

If I want to get all the email addresses for a single person and check whether the person actually exists, would it be more efficient to either:

Run an Any() query on the Persons table and then another query on the EmailAddresses table, using PersonId as a parameter:

public IEnumerable<EmailAddress> GetAddressesByPerson(int personId)
{
    if (!Context.Persons.Any(x => x.Id == personId))
    {
        throw new Exception("Person not found");
    }

    return Context.EmailAddresses.Where(x => x.PersonId == personId).ToList();
}

Get the Person object and return the EmailAddresses navigation property:

public IEnumerable<EmailAddress> GetAddressesByPerson(int personId)
{
    var person = Context.Persons.Find(personId);

    if (person == null)
    {
        throw new Exception("Person not found")
    }

    return person.EmailAddress;
}

Farhad Jabiyev · Accepted Answer · 2019-11-20T09:10:19.793

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 · Answer 2 · answered Nov 20 '19 at 00:01

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.

When should I use navigation properties to query the database?

2 Answers2