How to get all the items in both lists even when items not existing in both lists?

Question

I have two enumerable lists.

List<List_Data> List1 = new List<List_Data>();
List1.Add(new List_Data { Material = "1", Batch = "B1", QTY = 5 });
List1.Add(new List_Data { Material = "1", Batch = "B2", QTY = 5 });
List1.Add(new List_Data { Material = "2", Batch = "B1", QTY = 15 });

List<List_Data> List2 = new List<List_Data>();
List2.Add(new List_Data { Material = "1", Batch = "B1", QTY = 2 });
List2.Add(new List_Data { Material = "3", Batch = "B1", QTY = 5 });
List2.Add(new List_Data { Material = "3", Batch = "B2", QTY = 15 });

What i want is to compare the two lists and get the difference QTY (list1.QTY - list2.QTY) base on material and batch. Even if a item not exist on the other list i need to get minus or plus qty base on that material and batch.

This is the out put i'm expecting.

Material = "1", Batch = "B1", QTY = 3
Material = "1", Batch = "B2", QTY = 5 
Material = "2", Batch = "B1", QTY = 15
Material = "3", Batch = "B1", QTY = -5
Material = "3", Batch = "B2", QTY = -15

This is what i done so far,

SendList = (from l1 in List1
            join l2 in List2 on new { l1.Material, l1.Batch } equals new { l2.Material, l2.Batch } into temp
            from l2 in temp.DefaultIfEmpty()
            select new Report_Class
            {
                Material = l1.Material != null ? l1.Material : l2.Material, 
                Batch = l1.Batch != null ? l1.Batch : l2.Batch, 
                Difference = l1 != null && l2 != null ? (l1.QTY - l2.QTY).ToString() : l1 != null ? l1.QTY.ToString() : l2.QTY.ToString(), 

            }).ToList();

problem is it returns the list1 all items that exists, but not returns the item only exists in list 2. Any help would be greatly appreciated.

Thanks.

Answer 1

Here another solution

var result = List1
    .Select(e => new        
    {
        key = new
        {
            e.Material, 
            e.Batch
        }, 
        QTY = e.QTY
    })
    .Concat(List2
        .Select(e => new
        {
            key = new
            {
                e.Material, 
                e.Batch
            }, 
            QTY = -e.QTY
        }))
    .GroupBy( e => e.key, e => e.QTY )
    .Select(g => new Report_Class
    {
        Material = g.Key.Material, 
        Batch = g.Key.Batch, 
        Difference = g.Sum()
    })
    .ToList();

.net fiddle sample

Answer 2

Here's one approach:

• Reverse the QTY values on List2
• Concatenate the result of the above with List1.
• Group the concatenated list by Material and Batch, and sum up the QTY values

Here it is in code:

var result = List1.Concat(
             List2.Select(list2Item => new List_Data
             {
                 Material = list2Item.Material,
                 Batch = list2Item.Batch,
                 QTY = list2Item.QTY * -1
             }))
             .GroupBy(item => new { item.Material, item.Batch })
             .Select(grouped => new List_Data
             {
                 Material = grouped.First().Material,
                 Batch = grouped.First().Batch,
                 QTY = grouped.Sum(item => item.QTY)
             })
             .ToList();

Even if you have null QTY, it will still work. For example, having these values:

List<List_Data> List1 = new List<List_Data>();
List1.Add(new List_Data { Material = "1", Batch = "B1", QTY = 5 });
List1.Add(new List_Data { Material = "1", Batch = "B2", QTY = 5 });
List1.Add(new List_Data { Material = "2", Batch = "B1", QTY = 15 });
List1.Add(new List_Data { Material = "3", Batch = "B1", QTY = null });
List1.Add(new List_Data { Material = "3", Batch = "B3", QTY = 4 });

List<List_Data> List2 = new List<List_Data>();
List2.Add(new List_Data { Material = "1", Batch = "B1", QTY = 2 });
List2.Add(new List_Data { Material = "3", Batch = "B1", QTY = 5 });
List2.Add(new List_Data { Material = "3", Batch = "B2", QTY = 15 });
List2.Add(new List_Data { Material = "3", Batch = "B3", QTY = null });

will result to:

Material: "1", Batch: "B1", QTY: 3
Material: "1", Batch: "B2", QTY: 5
Material: "2", Batch: "B1", QTY: 15
Material: "3", Batch: "B1", QTY: -5
Material: "3", Batch: "B3", QTY: 4
Material: "3", Batch: "B2", QTY: -15

Answer 3

If we assume that in the second list there would be at most (if at all) one element with the same Material and Bacth value, then a naive solution could be the following:

// Initially project each element in the list to an element that 
// has also the info in which list this item is contained.
var list1 = List1.Select(x => new {Data = x, List = 1});
var list2 = List2.Select(x => new {Data = x, List = 2});

var result = list1.Concat(list2)
            .GroupBy(x => new {x.Data.Batch, x.Data.Material})
            .Select(gr =>
            {
                var itemsInGroup = gr.Count();
                if (itemsInGroup == 1)
                {
                    var onlyItemInGroup = gr.First();

                    if (onlyItemInGroup.List == 1)
                    {
                        return onlyItemInGroup.Data;
                    }

                    // Item came from the second list. So multiply it's quantity by -1.
                    onlyItemInGroup.Data.QTY *= -1;

                    return onlyItemInGroup.Data;
                }

                // Since for each item in list 1 there is at most one item in the list2
                // and vice versa itemsInGroup now is 2 and it is safe to use First as below
                // to grab the items.

                var itemFromFirstList = gr.First(x => x.List == 1);
                var itemFromSecondList = gr.First(x => x.List == 2);

                return new List_Data
                {
                    Material = gr.Key.Material,
                    Batch = gr.Key.Batch,
                    QTY = itemFromFirstList.Data.QTY - itemFromSecondList.Data.QTY
                };
            }).ToList();

Essentially all the work is done inside the Select after we have concatenated the two list and grouped the items in the resulting list based on the key Material and Batch. The options we have based on the initially assumption are the following:

• The group contains only one item and this item is from the first list. In this case we just return the Data that this item contains.
• The group contains only one item and this item is from the second list. In this case we have to multiply the value QTY with -1. Remember that the type you want to use is the list1.QTY - list2.QTY and there isn't any associated element in the first list, list1. So you want to get -list2.QTY as you have declared.
• The group contains two items and since we have assumed that there would be at most (if at all) one associated element in one list for another element in the other. In this case, we have just to subtract list2.QTY from list1.QTY to get the new quantity.