Returning (blanks) in many to many Linq query

Question

A follow up to this question: Changing a linq query to filter on many-many

I have the following Linq query

public static List<string> selectedLocations = new List<string>();

// I then populate selectedLocations with a number of difference strings, each
// corresponding to a valid Location

viewModel.people = (from c in db.People
                select c)
                .OrderBy(x => x.Name)
                .ToList();

// Here I'm basically filtering my dataset to include Locations from
// my array of selectedLocations

viewModel.people = from c in viewModel.people
                where (
                from a in selectedLocations
                where c.Locations.Any(o => o.Name == a)
                select a
                ).Any()
                select c;

How can I modify the query so that it also returns people that have NO location set at all?

Answer 1

You can do filtering on database side:

viewModel.people =
    (from p in db.People
     where !p.Locations.Any() || 
            p.Locations.Any(l => selectedLocations.Contains(l.Name))
     orderby p.Name
     select p).ToList();

Or lambda syntax:

viewModel.people = 
  db.People.Where(p => !p.Locations.Any() ||
                        p.Locations.Any(l => selectedLocations.Contains(l.Name)))
           .OrderBy(p => p.Name)
           .ToList();

EF will generate two EXISTS subqueries in this case. Something like:

SELECT [Extent1].[Name]
       [Extent1].[Id]
       -- other fields from People table
FROM [dbo].[People] AS [Extent1]
WHERE (NOT EXISTS (SELECT 1 AS [C1]
                   FROM [dbo].[PeopleLocations] AS [Extent2]
                   WHERE [Extent2].[PersonId] = [Extent1].[Id])
       OR EXISTS (SELECT 1 AS [C1]
                  FROM [dbo].[PeopleLocations] AS [Extent3]
                  WHERE [Extent3].[PersonId] = [Extent1].[Id])
                        AND [Extent3].[Name] IN ('location1', 'location2')))
ORDER BY [Extent1].[Name] ASC