ILookup: A Hidden, Powerful Feature of Linq

Posted on August 23, 2014 Updated on January 4, 2015

The ILookup Interface Just Made My Life Easier!

It was a classic case of something that sounded easy to the users, but which was, in reality,  somewhat tricky. All right, I admit it, at first I thought would be easy too!

Problem: our ERP sometimes generates two line items for the same product. Actually, it wasn’t a problem until we got a new eCommerce partner and they griped about multiple line items for the same thing. “Your’e making us work too hard here, our warehouse people are getting frustrated when they have to go back to the bin they just visited to pickup something they could have grabbed the first time!”.

Naturally, our business folks wanted to please, so I got my marching orders to “Just consolidate the two line items into one and sum the quantity. It’ll be easy!”. Famous last words.

My first approach was too complicated: use a Linq Groupby clause to grab all the duplicates (along with quantities), then iterate all the line items and, for each item, if my list of duplicates contained the current Product ID, write the first line item with the grand total and suppress all subsequent items. Too complicated, I hope you’re still reading!

Here’s what I ended-up doing with ILookup:

int prodIndex = 0;

//Build the lookup
//Each entry will have the ProductId for the key
//The value is the list of all line items sharing that key!
ILookup<string, LineItem> distinctProductIds = ((IEnumerable)orderHeader.DetailList)
                                               .ToLookup(i => i.ProductID);
result = new ShippingProduct[distinctProductIds.Count()];
//Loop through the distinct ProductIDs:
foreach (var anOrderItem in distinctProductIds)
{
        //Grab the first product that might be duplicated
	LineItem firstInItemGoup = distinctProductIds[anOrderItem.Key].First();
	ShippingProduct curProd = new ShippingProduct();
	curProd.SKU = anOrderItem.Key;
	curProd.Name = firstInItemGoup.Description;
	//Here is where the order gets consolidated
	curProd.Quantity = distinctProductIds[anOrderItem.Key].Sum(i => i.Quantity).ToString();
	//... Copy all other remaining fields

	result[prodIndex++] = curProd;
}

Receiving Order Confirmations

OK, that wasn’t really tricky, but receiving the order confirmations from our eCommerce partner was. Fortunately, I used similar code for that, and that made life easier. One reason for the trickiness: our eCommerce partner doesn’t send the Product ID back to us, just the line item IDs. Also tricky because I had to credit the original order line items with the correct quantity.

ILookup<string, LineItem> ProductIDLookup = detailList.ToLookup(i => i.ProductID);

//Loop through each line item from partner and match, on LineItem ID (LineSeq == ItemNumber) to find a line item in Traverse that matches theirs
foreach (OrdersItem orderLineItem in egOrder.Items)
{
   decimal partnerQty = 0;
   decimal.TryParse(orderLineItem.Shipped, out partnerQty);
   if (partnerQty > 0)
   {
      LineItem curOrderDetail = detailList.FirstOrDefault(dtl => dtl.LineSeq.Value.ToString() == orderLineItem.ItemNumber);
      if (curOrderDetail != null)
      {
         string ProductID = curOrderDetail.ProductID;

         decimal lineItemTotQty = ProductIDLookup[ProductID].Sum(i => i.Quantity);
         if (lineItemTotQty !=partnerQty)
         {
            string msg = string.Format("Quantity shipped by does not match ");
            throw new ApplicationException(msg);
         }

         //There should only be one line item for this ProductID (product id) 
         //BUT, if there are duplicates, we will handle them all, so long as we do not exceed the quantity actually shipped by partner
         foreach (LineItem erpLineItem in ProductIDLookup[ProductID].OrderByDescending(l => l.UnitPrice))
         {
            decimal qtyToCredit = erpLineItem.Quantity;
            ReconcileOrder(erpLineItem, qtyToCredit, erpLineItem.TransId);
            partnerQty -= qtyToCredit;
            if (partnerQty <= 0)
               break;
         }
      }
   }
}

 Bottom Line

Any time you need to group members of your list on some field, whether it is Product ID, customer ID, zip code, or any other field, and then do something with each little group, the ILookup interface makes your life easier. For starts, building the list is painless. I’m surprised I never knew about it before!