I have a problem with query, please take a look. My aim is:
Products with one Image. Products has Values which are some additional information aboute the product like specification etc.Image and Values can be nullProducts with Image and ValuesValues I need only Ids so its ok to get List<int> of ValuesProductValues and ImageObjects are connecting table for relationship --> Products can have many ProductValues also Products can have many ImageObjects but can have one ImageDistinctBy is from more linqQuestion 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(),
});
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
{
...
}),
});