在多对多Linq查询中返回(空格)

Question

这个问题的后续:更改linq查询以筛选多对多

我有以下Linq查询

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;

我如何修改查询，使它也返回没有位置设置的人?

Answer 1

可以在数据库端进行过滤:

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();

或lambda语法:

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

在这种情况下，

EF将生成两个EXISTS子查询。比如:

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