Linq Full Outer Join on Two Objects

Question

I have two objects called CountryMobility that I believe I need to combine with a full outer join. How can I do this using linq?

public class CountryMobility
{
    public string countryCode { get; set; }
    public int inbound { get; set; }
    public int outbound { get; set; }
} 

I want to combine two of these objects like so:

inboundStudents:
countryCode | inbound | outbound
         EG |    2    |     0
         CA |    3    |     0
         CH |    5    |     0

outboundStudents:
countryCode | inbound | outbound
         PE |    0    |     1
         CA |    0    |     4
         CH |    0    |     5


                      -
                      -
                      -
                      -
                      V

combinedStudents:
countryCode | inbound | outbound
         PE |    0    |     1
         CA |    3    |     4
         CH |    5    |     5
         EG |    2    |     0

I have tried the following linq statements but have not been able to figure out the correct syntax. I am currently getting a syntax error near temp.DefaultIfEmpty(new { first.ID, inbound = 0, outbound=0 }) in both statements.

var leftOuterJoin = 
    from first in inboundActivities
    join last in outboundActivities
    on first.countryCode equals last.countryCode
    into temp
    from last in temp.DefaultIfEmpty
    (new { first.countryCode, inbound = 0, outbound=0 })
    select new CountryMobility
    {
        countryCode = first.countryCode,
        inbound = first.inbound,
        outbound = last.outbound,
    };
var rightOuterJoin = 
    from last in outboundActivities
    join first in inboundActivities
    on last.countryCode equals first.countryCode
    into temp
    from first in temp.DefaultIfEmpty
    (new { last.countryCode, inbound = 0, outbound = 0 })
    select new CountryMobility
    {
        countryCode = last.countryCode,
        inbound = first.inbound,
        outbound = last.outbound,
    };

var fullOuterJoin = leftOuterJoin.Union(rightOuterJoin); 

Answer 1

After your latest information. It seems to me that you can do something much simpler. Namely a UNION ALL that you subsequently group by country code. A UNION ALL can be created using the Concat method.

The sample below works for me (using in memory collections). The query is shown in the Run method.

public class CountryMobility
{
    public string countryCode { get; set; }
    public int inbound { get; set; }
    public int outbound { get; set; }
}

public static class JoinedMobilityQuery
{
    static CountryMobility[] inbound = {
        new CountryMobility() { countryCode = "EG", inbound = 2 },
        new CountryMobility() { countryCode = "CA", inbound = 3 },
        new CountryMobility() { countryCode = "CH", inbound = 5 },
    };
    static CountryMobility[] outbound = {
        new CountryMobility() { countryCode = "PE", outbound = 1 },
        new CountryMobility() { countryCode = "CA", outbound = 4 },
        new CountryMobility() { countryCode = "CH", outbound = 6 },
    };

    static IQueryable<CountryMobility> Inbound()
    {
        return inbound.AsQueryable();
    }

    static IQueryable<CountryMobility> Outbound()
    {
        return outbound.AsQueryable();
    }

    public static void Run()
    {
        var transfers = from t in Inbound().Concat(Outbound())
                        group t by t.countryCode into g
                        select new CountryMobility() {
                            countryCode = g.Key,
                            inbound = g.Sum(x => x.inbound),
                            outbound = g.Sum(x => x.outbound),
                        };
        foreach (var transfer in transfers)
            Console.WriteLine("{0}\t{1}\t{2}", transfer.countryCode, transfer.inbound, transfer.outbound);
    }
}

Answer 2

Your DefaultIfEmpty is thropwing an error, because you are defining an anonymous object, but you are creating stronly typed objects in your select statement. They both have to be of the same type.

So define a default object like this:

var defaultActivity = new CountryMobility() { countryCode = String.Empty, outbound = 0, inbound = 0 };

After this, use it in your DefaultIfEmpty() method:

from last in temp.DefaultIfEmpty(defaultActivity)
select new CountryMobility
{
   //...
};

last but not least, you have to do a groupby to get the desired results:

var fullOuterJoin = leftOuterJoin.Union(rightOuterJoin)
                             .GroupBy (oj => oj.countryCode)
                             .Select (oj => oj.FirstOrDefault()); 

Output:

Full Demo Code:

http://share.linqpad.net/u46gar.linq

Answer 3

You can do like this

 List<CountryMobility> inboundStudents = new List<CountryMobility>{ 
            new CountryMobility { countryCode="EG", inbound=2, outbound = 0},
            new CountryMobility { countryCode="CA", inbound=3, outbound = 0},
            new CountryMobility { countryCode="CH", inbound=5, outbound = 0}};

        List<CountryMobility> outboundStudents = new List<CountryMobility>{ 
            new CountryMobility { countryCode="PE", inbound=0, outbound = 1},
            new CountryMobility { countryCode="CA", inbound=0, outbound = 4},
            new CountryMobility { countryCode="CH", inbound=0, outbound = 5}};

                var joinedList = inboundStudents.Concat(outboundStudents).GroupBy(item => new { item.countryCode});
                var result = joinedList.Select(x => new 
                {
                    countryCode = x.Key.countryCode,
                    inbound = x.Sum(i => i.inbound),
                    outbound = x.Sum(i => i.outbound)
                });