Linq to Entities Left outer join grouped into a collection

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
}

I have a LINQ to Entities query that has LEFT JOINto get rating values ​​for 1 or more segments for a given component.

An object segmentFinanceRatinghas properties{ MaterialId, SegmentId, Rating, LowRated }

Currently, the results are not grouped into the corresponding component, i.e. the property is SegmentRatingsnot a separate collection of objects segmentFinanceRating, instead I have several rows of data with 1 segmentFinanceRatingobject in each.

I saw several examples of use group x by y into z, but I could not get it to work, perhaps due to some collections of the component that I also need, I'm not sure.

Any help would be appreciated on how to do this, thanks.

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, , , .