Modify Linq query to support optional relationships

Question

I have a problem with a linq query where a joining record is null.

I have 3 models:

Location, Company, and Person.

• A Person must be at a single company
• A Person may have a single Location
• A Company may have multiple People

I'm using the following LINQ query to retrieve a distinct list of all the locations for all of the people at a specific company:

locations =
            db.Companies.Where(c => c.Name == company.Name)
            .SelectMany(c => c.People)
            .Select(p => p.Location)
            .Distinct()
            .ToList();

The problem arises where a Peron that is retrieved from this query does not have a Location (a Location is optional for a Person).

In this situation, the following query, which gets the name of each location, fails due to Object reference not set to an object:

locations.Select(g => g.Name).ToList()

How can I change the above line to ignore records where a retreived Person record does not have a location?

Answer 1

Try this:

locations =
            db.Companies.Where(c => c.Name == company.Name)
            .SelectMany(c => c.People)
            .Where(p => p.Location !=null)
            .Select(p => p.Location)
            .Distinct()
            .ToList();

Cheers

Answer 2

Commonly, you can use the null coalescing or conditional operators to check for the null, and then substitute it with a suitable default, e.g.:

locations =
        db.Companies.Where(c => c.Name == company.Name)
        .SelectMany(c => c.People)
        .Select(p => p.Location ?? SomeDefaultLocation)
        .Distinct()
        .ToList();

e.g. SomeDefaultLocation could be a readonly static instance of Location.

However, in the context you provided, this doesn't really make sense (e.g. all persons with no location will just return SomeDefaultLocation). This would be a more typical usage:

personAndLocations =
        db.Companies.Where(c => c.Name == company.Name)
        .SelectMany(c => c.People)
        .Select(p => new 
          {Person = p, 
           Location = p.Location ?? SomeDefaultLocation})
        .ToList();