How to do full outer join in c#?

Question

I am a newbie with Linq methods in C#. I was learning Join clause and I think I understand all three types of joins i.e

1. Inner join
2. Group join
3. Left outer join

What I understand about Left Outer Join is that it return all the elements of Left Source Sequence whether there is a matching element in right sequence or not.

Now I have a scenario where I want to perform Left Outer Join and also I want all the elements of Right Source Sequence whether there is a matching element in Left sequence or not.

How can I do this ?

Answer 1

You are basically asking about Full Outer Join. In LINQ generally using left and right joins and union together. Please look into the following threads

Thread 1 Thread 2

Sample :

var firstNames = new[]
{
    new { ID = 1, Name = "John" },
    new { ID = 2, Name = "Sue" },
};
var lastNames = new[]
{
    new { ID = 1, Name = "Doe" },
    new { ID = 3, Name = "Smith" },
};
var leftOuterJoin = from first in firstNames
                    join last in lastNames
                    on first.ID equals last.ID
                    into temp
                    from last in temp.DefaultIfEmpty(new { first.ID, Name = default(string) })
                    select new
                    {
                        first.ID,
                        FirstName = first.Name,
                        LastName = last.Name,
                    };
var rightOuterJoin = from last in lastNames
                     join first in firstNames
                     on last.ID equals first.ID
                     into temp
                     from first in temp.DefaultIfEmpty(new { last.ID, Name = default(string) })
                     select new
                     {
                         last.ID,
                         FirstName = first.Name,
                         LastName = last.Name,
                     };
var fullOuterJoin = leftOuterJoin.Union(rightOuterJoin);

Answer 2

you need a full join, you can do a left join and a right join and then union them, because in linq to entity there is not full join, for exmple:

        // left join: left table = TableA, right table = TableB
        var q1 = (from a in TableA
                  join b in TableB on a.ID equals b.ID into JoinedList
                  from b in JoinedList.DefaultIfEmpty()
                  select new
                  {
                      a,
                      b
                  });

        // right join: left table = TableB, right table = TableA
        var q2 = (from b in TableB
                  join a in TableA on b.ID equals a.ID into JoinedList
                  from a in JoinedList.DefaultIfEmpty()
                  select new
                  {
                      a,
                      b
                  });

        var query = q1.Union(q2).ToList();