C# join items which are the same with lambda expression

Question

With the following code how would I join items together.

var shippedItems = _orderService.GetOrderById(shipment.OrderId).Shipments
    .Where(x => x.ShippedDateUtc != null && x.OrderId == shipment.OrderId)
    .Select(x => x.ShipmentItems)
    .ToList();

So there may be two or more shipments for the order id. Each shipment may contain identical items.

Need a nudge in the right direction

Have tried

var shippedItems = _orderService.GetOrderById(shipment.OrderId).Shipments
    .Where(x => x.ShippedDateUtc != null && x.OrderId == shipment.OrderId)
    .Select(x => x.ShipmentItems.Join(x.ShipmentItems
        .Where(y => y.ShipmentId == shipment.Id)))
    .ToList();

and

var shippedItems = _orderService.GetOrderById(shipment.OrderId).Shipments
    .Where(x => x.ShippedDateUtc != null && x.OrderId == shipment.OrderId)
    .Select(x => x.ShipmentItems.GroupBy(y => y.Id))
    .ToList();

and

var shippedItems = _orderService.GetOrderById(shipment.OrderId).Shipments
    .Where(x => x.ShippedDateUtc != null && x.OrderId == shipment.OrderId)
    .Select(x => x.ShipmentItems.Distinct())
    .ToList();

Here is the code I'm using for output the items:

foreach (var shipmentItem in shippedItems)
{
    System.Diagnostics.Debug.WriteLine("item = " + shipmentItem);
    System.Diagnostics.Debug.WriteLine("item = " + shipmentItem.OrderItemId);
}

The above output produces:

shippedItemsList count = 9 item = System.Data.Entity.DynamicProxies.ShipmentItem_18BEAAFA747B42988EC4CB25D967298CC6736AF528389FC98E81143F7D629631 item = 356077

item = System.Data.Entity.DynamicProxies.ShipmentItem_18BEAAFA747B42988EC4CB25D967298CC6736AF528389FC98E81143F7D629631 item = 356078

item = System.Data.Entity.DynamicProxies.ShipmentItem_18BEAAFA747B42988EC4CB25D967298CC6736AF528389FC98E81143F7D629631 item = 356079

item = System.Data.Entity.DynamicProxies.ShipmentItem_18BEAAFA747B42988EC4CB25D967298CC6736AF528389FC98E81143F7D629631 item = 356077

item = System.Data.Entity.DynamicProxies.ShipmentItem_18BEAAFA747B42988EC4CB25D967298CC6736AF528389FC98E81143F7D629631 item = 356078

item = System.Data.Entity.DynamicProxies.ShipmentItem_18BEAAFA747B42988EC4CB25D967298CC6736AF528389FC98E81143F7D629631 item = 356079

item = System.Data.Entity.DynamicProxies.ShipmentItem_18BEAAFA747B42988EC4CB25D967298CC6736AF528389FC98E81143F7D629631 item = 356077

item = System.Data.Entity.DynamicProxies.ShipmentItem_18BEAAFA747B42988EC4CB25D967298CC6736AF528389FC98E81143F7D629631 item = 356079

item = System.Data.Entity.DynamicProxies.ShipmentItem_18BEAAFA747B42988EC4CB25D967298CC6736AF528389FC98E81143F7D629631 item = 356080

So the excepted output should be:

shippedItemsList count = 4 item = System.Data.Entity.DynamicProxies.ShipmentItem_18BEAAFA747B42988EC4CB25D967298CC6736AF528389FC98E81143F7D629631 item = 356077

item = System.Data.Entity.DynamicProxies.ShipmentItem_18BEAAFA747B42988EC4CB25D967298CC6736AF528389FC98E81143F7D629631 item = 356079

item = System.Data.Entity.DynamicProxies.ShipmentItem_18BEAAFA747B42988EC4CB25D967298CC6736AF528389FC98E81143F7D629631 item = 356078

item = System.Data.Entity.DynamicProxies.ShipmentItem_18BEAAFA747B42988EC4CB25D967298CC6736AF528389FC98E81143F7D629631 item = 356080

The above output is from using var shippedItems = _orderService.GetOrderById(shipment.OrderId).Shipments.Where(x => x.ShippedDateUtc != null && x.OrderId == shipment.OrderId).SelectMany(x => x.ShipmentItems).ToList();

NOTE: shipment item is ICollection<ShipmentItem> Shipment.ShipmentItems when I hover it in visual studio

UPDATED senario:

So let's say that the order has 3 items(a = 3, b= 5, c = 3), Now 1 shipment is sent with items(a = 1, b = 2, c = 0), Now a second shipment is sent with items(a = 1, b = 1, c = 1). I would like the quantity of items from both shipments. So I expect items(a = 2, b = 3, c = 1), I currently get items(a = 1, b = 2) + items(a = 1, b = 1, c = 1). So the list of shipment items I loop through are appearing more than once.

I thought maybe union but not sure how to put it together :/

UPDATE MY SOLUTION

I could not manage to accomplish this using lambda expression, so I had to do the following solution to get the result I wanted. I hope this helps others looking for workarounds.

//calculate quantity of total shipped items from each shipment
            List<ShipmentItem> alreadyShippedItemsList = new List<ShipmentItem>();
            Dictionary<int, int> alreadyShippedItems = new Dictionary<int, int>();

            var shippedItems = _orderService.GetOrderById(shipment.OrderId).Shipments
            .Where(x => x.ShippedDateUtc != null)
            .SelectMany(x => x.ShipmentItems)
            .ToList();

            //create a list of shipment items
            for (int i = 1; i <= shipmentsList.Count - 1; i++)
            {
                var si = shipmentsList[i];

                var sii = si.ShipmentItems.ToList();

                foreach (var item in sii)
                {
                    var itemInList = alreadyShippedItemsList.Where(x => x.OrderItemId == item.OrderItemId).FirstOrDefault();
                    int sum = 0;

                    //create a list of shipment items and check for duplicate items
                    if (itemInList == null)
                    {
                        alreadyShippedItems.Add(item.OrderItemId, item.Quantity);
                        alreadyShippedItemsList.Add(item);
                    }
                    else
                    {
                        //if duplicate item is found update the quantity in the dictionary
                        sum = itemInList.Quantity + item.Quantity;
                        alreadyShippedItems[item.OrderItemId] = sum;
                    }
                }
            }

Depend on your requirement, is this "Join", or "Union". Perharp if you can provide your data, and your required output, it should be easier for us — Jacky, Oct 03 '17 at 03:24
@Sujit.Warrier so 1 order may have 3 shipments, each shipment can have any number of items from the order. — Web Dev Guy, Oct 03 '17 at 04:14
Consider that a Shipment contains unique ShipmentItems but shared OrderItems. — Raphael, Oct 03 '17 at 06:09

score 3 · Answer 1 · answered Oct 03 '17 at 03:24

3

You need to use SelectMany, which flattens a list

var shippedItems = _orderService.GetOrderById(shipment.OrderId).Shipments
.Where(x => x.ShippedDateUtc != null && x.OrderId == shipment.OrderId)
.SelectMany(x => x.ShipmentItems)
.ToList();

answered Oct 03 '17 at 03:24

Richard Schneider

34,944
9
57
73

This is still not working as expected. See my updated question, have example of output I should be getting. Thanks for helping :) – Web Dev Guy Oct 03 '17 at 03:43
This appears to be the correct solution from my googling. But the list is not flattened, so the same item is appearing in the list more than once. :( – Web Dev Guy Oct 03 '17 at 21:56
Perhaps you need to apply a `.Where` to the `ShippingItems`? – Richard Schneider Nov 09 '17 at 03:34

Sujit.Warrier · Answer 2 · 2017-10-03T06:13:46.677

1

You are using Distinct in the wrong place

var shippedItems = _orderService.GetOrderById(shipment.OrderId).Shipments
.Where(x => x.ShippedDateUtc != null && x.OrderId == shipment.OrderId)
.Select(x => x.ShipmentItems).Distinct()
.ToList();

Either override Equals function for the ShipmentItems class using this Correct way to override Equals() and GetHashCode() and use distinct directly or do two steps

 var distinctShippedItemIds = _orderService.GetOrderById(shipment.OrderId).Shipments
.Where(x => x.ShippedDateUtc != null && x.OrderId == shipment.OrderId)
.Select(x => x.ShipmentItems.OrderItemId).Distinct()
.ToList();

List<ShippingItems> UniqueItemList=new List<ShippingItems>();
 foreach(int OrderId in distinctShippedItemIds)
 {
     var shippedItems = _orderService.GetOrderById(shipment.OrderId).Shipments
.Where(x => x.ShippedDateUtc != null && x.OrderId == OrderId)
.Select(x => x.ShipmentItems).FirstOrDefault();
      if(shippedItems !=null)
      {
         UniqueItemList.Add(shippedItems);
      }
 }

edited Oct 03 '17 at 06:13

answered Oct 03 '17 at 04:13

Sujit.Warrier

2,815
2
28
47

damn i thought ShipmentItems was an Int property – Sujit.Warrier Oct 03 '17 at 04:29
Distinct is not working because, runtime doesnt know what makes two shipmentitems equals. you will have to override the equals() and gethashcodemethod() of your ShipmentItems class give this information to runtime. use this link https://stackoverflow.com/questions/9317582/correct-way-to-override-equals-and-gethashcode – Sujit.Warrier Oct 03 '17 at 04:32
Nah shipment item is `ICollection Shipment.ShipmentItems` when I hover it. This has me stumpped :( – Web Dev Guy Oct 03 '17 at 04:33
did you override the equals method in your shipmentitems class? should solve your problem – Sujit.Warrier Oct 03 '17 at 05:09
The part of your edit can be done in one statement. See my answer – Raphael Oct 03 '17 at 06:07
but as mentioned the best and easiest way is to override the equals function. – Sujit.Warrier Oct 03 '17 at 06:12
Distinct on ShipmentItems only makes sense if you fetch the same Shipment more than once since ShipmentItems are always bound to one Shipment. A distinct to the OrderItem of the ShipmentItem makes sense, cause there can be multiple ShipmentItems for one OrderItem. Check Edit 2 of my Answer – Raphael Oct 03 '17 at 06:17
See my updated question for the solution which worked for me. This was helpful though. Thanks for the help :) – Web Dev Guy Oct 06 '17 at 01:40

Raphael · Answer 3 · 2017-10-03T06:24:40.380

1

I may get you wrong but is this what you want?

SELECT * FROM [ShipmentItem]
INNER JOIN [Shipment] ON [ShipmentItem].[ShipmentId] = [Shipment].[Id]
WHERE [Shipment].[ShippedDateUtc] IS NOT NULL AND [Shipment].[OrderId] = @OrderId

So this will do the job

var shippedItems = _orderService.GetOrderById(orderId)
        .Shipments
        .Where(s => s.ShippedDateUtc != null)
        .SelectMany(s => s.ShipmentItems)
        .ToList();

Sample

Edit

If You you want to fetch the order items you have to do something like this

var orderItems = _orderService
    .GetOrderById(orderId)
    .Shipments
    .Where(s => s.ShippedDateUtc != null)
    .SelectMany(s => s.ShipmentItems)
    .Select(si => _orderService.GetOrderItemById(si.OrderItemId))
    .Distinct()
    .ToList();

or if you want to produces less DB queries

var orderItems = _orderService
    .GetOrderById(orderId)
    .Shipments
    .Where(s => s.ShippedDateUtc != null)
    .SelectMany(s => s.ShipmentItems)
    .Select(si => si.OrderItemId)
    .Distinct()
    .Select(id => _orderService.GetOrderItemById(id))
    .ToList();

Edit 2

nopCommerce data of a order with 4 order items (quantity of 2) and 4 shipments

edited Oct 03 '17 at 06:24

answered Oct 03 '17 at 05:38

Raphael

990
1
13
24

The second bit of code lambda expression code does not work. So let's say that the order has 3 items(a = 3, b= 5, c = 3), Now 1 shipment is sent with items(a = 1, b = 2, c = 0), Now a second shipment is sent with items(a = 1, b = 1, c = 1). I would like the quantity of items from both shipments. So I expect items(a = 2, b = 3, c = 1), I currently get items(a = 1, b = 2) + items(a = 1, b = 1, c = 1). So the list of shipment items I loop through are appearing more than once. Hope that makes sense. Thanks for helping – Web Dev Guy Oct 03 '17 at 23:50
In order to get quantities of both shipments you have to group ShipmentItems by OrderItemId and count Quantity together – Raphael Oct 05 '17 at 08:02
See my updated question for the solution which worked for me, for some reason your solution would not work for me. Thanks for your help though :) – Web Dev Guy Oct 06 '17 at 01:39

C# join items which are the same with lambda expression

3 Answers3