Linq 到实体 分组到集合中的左外部联接

Question

from component in Materials.OfType<Container>().Where(m => m.Active)
join segmentFinanceRating in segmentFinanceRatingView on component.Id equals segmentFinanceRating.MaterialId into segmentFinanceRatingGroup
from segmentFinanceRatingWithDefault in segmentFinanceRatingGroup.DefaultIfEmpty()
select new
{
   id = component.Id,
   name = component.Name,
   subType = component.SubType,
   size = component.Size,
   MaterialIds = component.Materials.Select(x => x.Id),
   BrandNames = component.Brands.Select(x => x.Name),
   SegmentRatings = segmentFinanceRatingWithDefault
}

我有上述 LINQ to Entities 查询，该查询具有获取给定组件的一个或多个段的评级值LEFT JOIN。

segmentFinanceRating实体具有以下属性{ MaterialId, SegmentId, Rating, LowRated }

目前，结果尚未分组到相关组件，即 SegmentRatings 属性不是segmentFinanceRating对象的单个集合，而是我有多个数据行，每个行中有 1 个segmentFinanceRating对象。

我已经看到了一些使用 group x by y into z 的示例，但我无法让它工作，可能是由于我也需要组件上的一些集合，我不确定。

关于

如何做到这一点的任何帮助将不胜感激，谢谢。

Answer 1

列表中的 GroupBy 对您不起作用？

var list = (from component in Materials.OfType<Container>().Where(m => m.Active)
join segmentFinanceRating in segmentFinanceRatingView on component.Id equals segmentFinanceRating.MaterialId into segmentFinanceRatingGroup
from segmentFinanceRatingWithDefault in segmentFinanceRatingGroup.DefaultIfEmpty()
select new
{
   id = component.Id,
   name = component.Name,
   subType = component.SubType,
   size = component.Size,
   MaterialIds = component.Materials.Select(x => x.Id),
   BrandNames = component.Brands.Select(x => x.Name),
   SegmentRatings = segmentFinanceRatingWithDefault
}).ToList().GroupBy(s=> s.SegmentRatings);

Answer 2

在这种情况下，在匿名类型中进行联接要容易得多：

from component in Materials.OfType<Container>().Where(m => m.Active)
select new
{
   id = component.Id,
   name = component.Name,
   subType = component.SubType,
   size = component.Size,
   MaterialIds = component.Materials.Select(x => x.Id),
   BrandNames = component.Brands.Select(x => x.Name),
   SegmentRatings = (from segmentFinanceRating in segmentFinanceRatingView
                     where segmentFinanceRating.MaterialId == component.Id
                     select segmentFinanceRating)
}

当特定组件没有空SegmentRatings集合时，您将有一个空的集合，从而产生与外部连接相同的效果。