Full outer join linq using union

Question

I have two lists

var left={[ID=1,Name='A',Qty1=0,Qty2=5],[ID=2,Name=B,Qty1=0,Qty2=52]};
var right={[ID=1,Name='A',Qty1=57,Qty2=0],[ID=2,Name=B,Qty1=84,Qty2=0]};
var outer=left.union(right);

I want to get the following result:

outer={[ID=1,Name='A',Qty1=57,Qty2=5],[ID=2,Name=B,Qty1=84,Qty2=52]}

How do I get that? How to write the comparator class?

Edit: I have two lists

var target=(...new ClassA{ID=a.ID,Name=a.Name,TargetQty=b.TargetValue}).ToList();
var sales=(....new ClassA{ID=a.ID,Name=a.Name,SalesQty=b.SaleValue}).ToList();

Now I want a full outer join. How can I get that?

Are you just trying to sum the quantity values based on ID and Name? If so, it sounds like you want a group join instead. — Jon Skeet, Feb 01 '15 at 08:59
Note that it's not clear why you think you want a full outer join - if you really do want that, it would be useful if you could make that clear in your example. The example you've already got works fine with an inner join, as per my answer. — Jon Skeet, Feb 01 '15 at 09:09
So now you've edited it so that they're not even the same types (one has `TargetQty` and the other has `SalesQty`) and you *still* haven't provided an example of incoming data and expected results other than in an example where both lists have the same IDs (so an inner join would work) — Jon Skeet, Feb 01 '15 at 20:49

score 2 · Answer 1 · answered Feb 01 '15 at 09:07

2

It sounds like you possibly want an inner join:

var query = left.Join(right, l => l.Id, r => r.Id,
                      (l, r) => new { l.Id, l.Name, r.Qty1, l.Qty2 });

(You may want to join on both Id and Name; it's not clear whether the Id is enough.)

answered Feb 01 '15 at 09:07

Jon Skeet

1,421,763
867
9,128
9,194

I need outer join.Not inner join. – decoder Feb 01 '15 at 10:35
@decoder: Well your example doesn't show *how* you want to use an outer join - an inner join with your sample input will give you the required output. If you want a better answer, you should provide more details in your question. – Jon Skeet Feb 01 '15 at 16:21

score 1 · Answer 2 · answered Feb 01 '15 at 12:20

In this case union will not bring the desired output.

Jon Skeet is right in his direction that inner join will do what you want. Considering your question it seems like you need to have inner join with sum of respective row like below. If it is not your requirement then you need to modify your question.

    public class Program
{
    public static void Main()
    {
        var left= new List<Product>(){
            new Product(){ID=1,Name="A",Qty1=0,Qty2=5},
            new Product(){ID=2,Name="B",Qty1=0,Qty2=52}
        };
        var right= new List<Product>(){
            new Product(){ID=1,Name="A",Qty1=57,Qty2=0},
            new Product(){ID=2,Name="B",Qty1=84,Qty2=0}
        };

        var outer=left.Union(right);
        Console.WriteLine(outer.Count());//will be four which is not expected.

        var query = left.Join(right, d => d.ID, e=> e.ID, (f,g) => new Product(){ID = f.ID, Name = f.Name, Qty1 = f.Qty1+g.Qty1, Qty2 = f.Qty2+g.Qty2});

        foreach(var item in query)
        {
            item.Print();
        }
    }
}

public class Product {
    public int ID{get; set;}
    public string Name{get; set;}
    public int Qty1{get; set;}
    public int Qty2{get; set;}

    public void Print()
    {
        Console.WriteLine("ID  : {0}", ID);
        Console.WriteLine("Name: {0}", Name);
        Console.WriteLine("Qty1: {0}", Qty1);
        Console.WriteLine("Qty2: {0}", Qty2);
    }
}

Here is output from above code:

4

ID : 1 Name: A Qty1: 57 Qty2: 5

ID : 2 Name: B Qty1: 84 Qty2: 52

You may modify and test this code here => link

Ok thats fine for now.I modify my code.I avoid this kind of situation.i solved using outer join.Thanx for ur answer. — decoder, Feb 01 '15 at 13:18

Full outer join linq using union

2 Answers2