在多对多Linq查询中返回(空白) [英] Returning (blanks) in many to many Linq query

问题描述

此问题的后续处理:更改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;

如何修改查询，使其也返回根本没有设置位置的人?

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

推荐答案

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

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

或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子查询.像这样:

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

这篇关于在多对多Linq查询中返回(空白)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！