How to group an array list, within a list of records, Linq GroupBy
-
Scratching my head on this. I have a list of inventory items, and I want to add how many has sold from orders. I can get the inventory, and then loop that inventory to get the "itemNumber", and query the orders to extract only orders that contain that item. But each order can have multiple items sold, so I need to get the "items sold array" within the order. This is written for MongoDB, and is not really a MongoDB question. I have no clue how to write this in pure Mongo C# driver.
OrderId OrderNumber OrderItems = > ItemId OrderItems = > ItemNumber OrderItems = > Qty OrderItems = > PriceI wrote code to get all my inventory items, and used a for each of each item to query the orders. But I can't figure out the GroupBy part, or perhaps GroupBy is not the right solution. The var in purple below is suggesting the order model, and not the OrderItems within the Order model. I know I constructed the GroupBy wrong, or using GroupBy is just plain wrong.// Process inventory items
var start = (page * show) - show;
var query = _context.MarketPlaceInventory.Find(mp => mp.MarketChannel.Equals(marketChannel)).SortBy(s => s.ItemNumber);
var totalTask = query.CountDocumentsAsync();
var inventoryTask = query.Skip(start).Limit(show).ToListAsync();// Wait for inventory to process
await Task.WhenAll(totalTask, inventoryTask);// Process units sold from orders, bylooping the inventory
foreach (var product in inventoryTask.Result)
{
var queryFilter = Builders.Filter.ElemMatch(oi => oi.OrderItems, item => item.ItemNumber == product.ItemNumber);
var ordersTask = _context.MarketPlaceOrders.Find(queryFilter).ToListAsync();
await Task.WhenAll(ordersTask);var orderItems = ordersTask.Result.GroupBy(x => x.OrderItems).Select(item => new MarketPlaceOrderItems() {
ItemQty = item.Sum(qty =>qty.ItemQty) // This is suggesting the order, and not orderItems
});
Console.Write(orderItems);}
return new Get_MarketPlaceInventory
{
Count = totalTask.Result,
Inventory = inventoryTask.Result,
Page = page,
Show = show
};If it ain't broke don't fix it Discover my world at jkirkerx.com
-
Scratching my head on this. I have a list of inventory items, and I want to add how many has sold from orders. I can get the inventory, and then loop that inventory to get the "itemNumber", and query the orders to extract only orders that contain that item. But each order can have multiple items sold, so I need to get the "items sold array" within the order. This is written for MongoDB, and is not really a MongoDB question. I have no clue how to write this in pure Mongo C# driver.
OrderId OrderNumber OrderItems = > ItemId OrderItems = > ItemNumber OrderItems = > Qty OrderItems = > PriceI wrote code to get all my inventory items, and used a for each of each item to query the orders. But I can't figure out the GroupBy part, or perhaps GroupBy is not the right solution. The var in purple below is suggesting the order model, and not the OrderItems within the Order model. I know I constructed the GroupBy wrong, or using GroupBy is just plain wrong.// Process inventory items
var start = (page * show) - show;
var query = _context.MarketPlaceInventory.Find(mp => mp.MarketChannel.Equals(marketChannel)).SortBy(s => s.ItemNumber);
var totalTask = query.CountDocumentsAsync();
var inventoryTask = query.Skip(start).Limit(show).ToListAsync();// Wait for inventory to process
await Task.WhenAll(totalTask, inventoryTask);// Process units sold from orders, bylooping the inventory
foreach (var product in inventoryTask.Result)
{
var queryFilter = Builders.Filter.ElemMatch(oi => oi.OrderItems, item => item.ItemNumber == product.ItemNumber);
var ordersTask = _context.MarketPlaceOrders.Find(queryFilter).ToListAsync();
await Task.WhenAll(ordersTask);var orderItems = ordersTask.Result.GroupBy(x => x.OrderItems).Select(item => new MarketPlaceOrderItems() {
ItemQty = item.Sum(qty =>qty.ItemQty) // This is suggesting the order, and not orderItems
});
Console.Write(orderItems);}
return new Get_MarketPlaceInventory
{
Count = totalTask.Result,
Inventory = inventoryTask.Result,
Page = page,
Show = show
};If it ain't broke don't fix it Discover my world at jkirkerx.com
Inventory: Item #, Quantity on hand (QTY). Order Item: Item #, Quantity sold (-QTY). Merge and sum to get the inventory balance (or stock "not committed").
It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it. ― Confucian Analects: Rules of Confucius about his food