Linq + MoreLinq how to aggregate one result prop to list?

Question

I have a problem with query, please take a look. My aim is:

• I need to take all Products with one Image.
• Products has Values which are some additional information aboute the product like specification etc.
• Image and Values can be null
• Wanna return all Products with Image and Values
• For Values I need only Ids so its ok to get List<int> of Values
• ProductValues and ImageObjects are connecting table for relationship --> Products can have many ProductValues also Products can have many ImageObjects but can have one Image
• DistinctBy is from more linq

Question I don't know how to aggregate Values in correct way to return list of Values per Product

PS I'm also using more linq

var q1 = (from p in Products
        join pv in ProductValues on p.ProductId equals pv.ProductId into ljpv
        from pv in ljpv.DefaultIfEmpty()
        select new
            {
                ProductId = p.ProductId,
                Description = p.Description,
                Name = p.Name,
                Price = p.Price,
                Quantity = p.Quantity,
                Type = p.Type,
                Values = (from v in ValueTypes 
                            where v.ValueId == pv.ValueId 
                            select new {
                             ValueId = v.ValueId
                            }).ToList(),
                ImageObjects = (from io in ImageObjects
                                where io.ProductId == p.ProductId && io.IsDefault == true
                                select new 
                                    {
                                               Image = io.Image,
                                               IsDefault = io.IsDefault,
                                               ProductId = io.ProductId
                                    })
                                .ToList()
            })
        .DistinctBy(x=>x.Name)
        .OrderBy(x=>x.Name);
q1.Dump();

Answer Values = (from tmp in ljpv select new { ValueId = tmp.ValueId}),

I know that this is not place to answer, but meaby someone will have any addvices to my code or meaby it can be done more clear or faster. I've been wondering how to do this query for a long time, but as I wrote to you, I got a dazzle:)

---

after @Harald Coppoolse answer - the code is more faster!

                return context.Product.GroupJoin(
                    context.ProductValue,
                    context.ImageObject.Include(x => x.Image),
                    p => p.ProductId,
                    pv => pv.ProductId,
                    io => io.ProductId,
                    (p, pv, io) => new ProductModel
                    {
                        ProductId = p.ProductId,
                        Name = p.Name,
                        Price = p.Price,
                        ProductValue = pv
                            .Select(npv => new ProductValueModel
                            {
                                ProductId = npv.ProductId,
                            }).ToList(),
                        ImageObject = io
                            .Select(nio => new ImageObjectModel
                            {
                                Image = nio.Image.DtoToModel(),
                                IsDefault = nio.IsDefault,
                                ProductId = nio.ProductId
                            }).ToList(),
                    });

Answer 1

So you have a table of Products and a table of ProductValues with a one-to-many relation: every Product has zero or more ProductValues and every ProductValue belongs to exactly one Product, namely the Product that the foreign key ProductId points to.

You want (several properties of) all Products, each Product with its ProductValues. After that you DistinctBy and OrderBy, but that is not your problem.

Whenever you want "items with their sub-items", like "Schools with their Students", "Customers with their Orders", "Orders with their Order lines", consider using Enumerable.GroupJoin

GroupJoin is in fact a Left Outer Join, followed by a GroupBy.

var productsWithTheirProductValues = products.GroupJoin(  // GroupJoin Products
    productValues,                                        // with ProductValues
    product => product.ProductId,           // from every Product take the ProductId
    productValue => productValue.ProductId, // from every ProductValue take the foreign key

    // ResultSelector: take the product with its zero or more matching ProductValues
    // to make a new object:
    (product, productValuesOfThisProduct) => new
    {
        // Select only the product properties you plan to use:
        Id = product.Id,
        Name = product.Name,
        Price = product.Price,
        ...

        ProductValues = productValuesOfThisProduct
            // only if you don't want all ProductValues of this product:
            .Where(productValue => ...)   

            .Select(productValue => new
            {
                // again select only the properties you plan to use
                Id = productValue.Id,
                ...

                // not needed: the foreign key, you already know the value
                // ProductId = productValue.ProductId,
            })
            .ToList(),
    });

In your case, you don't want to GroupJoin two sequences, but three sequences. You'l need to do an extra GroupJoin:

var result = products.GroupJoin(productValues,
    product => product.ProductId,
    productValue => productValue.ProductId,

    // ResultSelector: remember the product and all its productValues
    (product, productValuesOfThisProduct) => new
    {
        Product = product,
        ProductValues = productValuesOfThisProduct,
    })

    // now do the 2nd join:
    .GroupJoin(imageObjects,
        firstJoinResult => firstJoinResult.Product.ProductId,
        imageObject => imageObject.ProductId,

        // result selector:
        (firstJoinResult, imageObjectsOfThisProduct) => new
        {
            Product = firstJoinResult.Product,
            ProductValues = firstJoinResult.ProductValues,
            ImageObjects = imageObjectsOfThisProduct,
        })

        // take each element of this group join result and select the items that you want
        .Select(joinResult => new
        {
            ProductId = joinResult.Product.ProductId,
            Price = joinResult.Product.Price,
            ...

            ProductValues = joinResult.ProductValues.Select(productValue => new
            {
                 ...
            })
            .ToList(),

            ImageObjects = joinResult.ImageObjects.Select(imageObject => new
            {
                ...
            })
            .ToList(),
       });

This looks horrible. So if you have to do a GroupJoin with three tables more often, consider to create a GroupJoin for three tables:

static IEnumerable<TResult> GroupJoin<T1, T2, T3, TKey, TResult>(
   this IEnumerable<T1> source1, IEnumerable<T2> source2, IEnumerable<T3> source3,
   Func<T1, TKey> key1Selector,
   Func<T2, TKey> key2Selector,
   Func<T3, TKey> key3Selector,
   Func<T1, IEnumerable<T2>, IEnumerable<T3>, TResult> resultSelector)
{
     // put all source2 and source3 elements in lookuptables, using the keyselector
     var lookup2 = source2.ToLookup(item => key2Selector(item));
     var lookup3 = source3.ToLookup(item => key3Selector(item));

     // now for every source1 item, get all source2 and source3 items with the same key
     // and create the result:
     foreach (var item1 in source1)
     {
         TKey key1 = key1Selector(item1);
         IEnumerable<T2> items2 = lookup2[key1];
         IEnumerable<T3> items3 = lookup3[key1];
         // returns empty collection if no items with this key

         TResult result = resultSelector(item1, items2, items3);
         yield return result;
    }
}

usage:

var result = products.GroupJoin(productValues, imageObjects,
   product => product.ProductId,
   productValue => productValue.ProductId,
   imageObject => imageObject.ProductId,

   (product, productValues, imageObjects) => new
   {
       ProductId = product.ProductId,
       ...

       ProductValues = productValues.Select(productValue => new
       {
          ...
       }),
       ImageObjects = imageObjects.Select(imageObject => new
       {
          ...
       }),
    });