linq嵌套在其中()子句

Question

我有建筑物实体，在该实体内部，我有另一个实体公寓。我想过滤那些至少有一个带有"批准的"状态和带有"可销售"的公寓的建筑物。

这是我的建筑课。

public class Building 
{
 public ICollection<Apartment> Apartments {get;set;}
}

这是我的公寓班。

public class Apartment 
{
  public Enum State {get;set;}   
}

到目前为止，我已经尝试了此代码，但似乎实际上不起作用。

 var buildings = _context.Buildings.Where(apartments.Apartments
              .Any(isActive =>isActive.State == ApartmentState.Approved && isActive.Accessibility == AccessibilityState.Saleable));

此代码只是返回所有公寓的清单，这些公寓至少有一套满足此条件的公寓。我不需要其余的公寓。

我也尝试了另一种方法来获得理想的结果

var buildings = _context.BuildingsWhere(apartments => apartments.Apartments.Where(apart=> aapart.State == ApartmentState.Approved && apart.Accessibility == AccessibilityState.Saleable));

在这种特殊情况下，我有这个错误

操作员不能应用于bool类型的操作数

任何帮助将不胜感激。

Answer 1

您正在寻找任何子句：

var buildings = _context
    .Buildings // For all my buildings
    .Where(b => b.Apartments // I want the ones where
        .Any(a => a.State == ApartmentState.Approved // There is at least 1 approved
            && a.Accessibility == AccessibilityState.Saleable)); // AND one is Saleable.

当您执行Where时，语句中的最终值必须是Bool，并且将返回IEnumerable(IQueryable(。Any将返回true或false供您过滤。

update

新要求，只能获取具有过滤的公寓。拥有建筑物后，如果您只想获得批准和可销售的公寓，请再次过滤。

foreach(var building in buildings) {
    var saleableApartments = building.Apartments
        .Where(a => a.State == ApartmentState.Approved
                    && a.Accessibility == AccessibilityState.Saleable));
    // Do whatever.
}

或者您只是在寻找公寓

var saleableApartments = _context
    .Buildings // For all my buildings
    .Select(b => b.Apartments // Select the apartments
        .Where(a => a.State == ApartmentState.Approved // where x
            && a.Accessibility == AccessibilityState.Saleable)); 

Answer 2

从@blueeyedbehemoth下的评论中答案，我建议：

var filteredBuildings = _context
    .Buildings
    .Select(b => new { apparments = b.Appartments
        .Where(a => a.State == ApartmentState.Approved
                          && a.Accessibility == AccessibilityState.Saleable) } )
    .Where(b => b.apparments.Any());

Answer 3

您的场景需要在LINQ查询中加入。您可以写类似：

var apartments = from building in _context.Buildings
                join apartment in _context.Apartments on building.Id equals apartment.BuildingID
                where apartment.State == ApartmentState.Approved 
                && apartment.Accessibility == AccessibilityState.Saleable
                select apartment ;

或者您还可以编写一个子查询：

var buildings = _context.Buildings
                  .Select(building => new 
                          { 
                             Building = building ,
                             Apartments = _context.Apartments
                                       .Where(apartment=>
                                        building.Id == apartment.BuildingId &&
                                        apartment.State == ApartmentState.Approved &&
                                        apartment.Accessibility == AccessibilityState.Saleable)
                           });