Linq - Full Outer Join

LINQ - Full Outer Join

I don't know if this covers all cases, logically it seems correct. The idea is to take a left outer join and right outer join then take the union of the results.

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()
    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()
    select new
    {
        last.ID,
        FirstName = first?.Name,
        LastName = last.Name,
    };
var fullOuterJoin = leftOuterJoin.Union(rightOuterJoin);

This works as written since it is in LINQ to Objects. If LINQ to SQL or other, the query processor might not support safe navigation or other operations. You'd have to use the conditional operator to conditionally get the values.

i.e.,

var leftOuterJoin =
    from first in firstNames
    join last in lastNames on first.ID equals last.ID into temp
    from last in temp.DefaultIfEmpty()
    select new
    {
        first.ID,
        FirstName = first.Name,
        LastName = last != null ? last.Name : default,
    };

Linq Full outer join

you can see this

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);

see LINQ - Full Outer Join

How to do a full outer join in Linq?

I think I have the answer here, which is not as elegant as I'd hoped, but it should do the trick:

var studentIDs = StudentClasses.Select(sc => sc.StudentID)
  .Union(StudentTeachers.Select(st => st.StudentID);
  //.Distinct(); -- Distinct not necessary after Union
var q =
  from id in studentIDs
  join sc in StudentClasses on id equals sc.StudentID into jsc
  from sc in jsc.DefaultIfEmpty()
  join st in StudentTeachers on id equals st.StudentID into jst
  from st in jst.DefaultIfEmpty()
  where st == null ^ sc == null
  select new { sc, st };

You could probably squeeze these two statements into one, but I think you'd sacrifice code clarity.

Linq Full Outer Join on Two Objects

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);
    }
}

Linq Full Outer Join Error

As i can see , there is type issue , both anonymous type have different property , so that will be causing the issue for using Union . you can create a common type with same property and use it. or have both property and use according to query Like:

var LeftOuterJoin =  from p in Products
join pv in ProductVendors on p.ProductID equals pv.ProductID into ppv
from pv in ppv.DefaultIfEmpty()
select new {VendorName = "",
            ProductName = p.Name,};

var RightOuterJoin =  from v in Vendors
join pv in ProductVendors on v.BusinessEntityID equals pv.BusinessEntityID into ppv
from pv in ppv.DefaultIfEmpty()
select new {VendorName = v.Name,
            ProductName = "",};

var result = LeftOuterJoin.Union(RightOuterJoin).ToList(); // force execution to get results.

use this if you are using LinqPad

var result = LeftOuterJoin.Union(RightOuterJoin)
result .Dump();

This will work for you.

C# Linq full outer join on repetitive values

using System;
using System.Collections.Generic;
using System.Linq;    

namespace Testing
{
    public class Property
    {
        public string Name { get; set; }

        public override bool Equals(object obj)
        {
            var item = obj as Property;

            if (item == null)
            {
                return false;
            }
            return item.Name == Name;
        }

        public override int GetHashCode()
        {
            return Name.GetHashCode();
        }
    }

    public class JoinedProperty
    {
        public Property Name1 { get; set; }
        public Property Name2 { get; set; }

        public override string ToString()
        {
            return (Name1 == null ? "" : Name1.Name)
                + (Name2 == null ? "" : Name2.Name);
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            var list1 = new List<Property>
            {
                new Property{ Name = "A" },
                new Property{ Name = "A" },
                new Property{ Name = "A" },
                new Property{ Name = "B" }
            };

            var list2 = new List<Property>
            {
                new Property{ Name = "A" },
                new Property{ Name = "B" },
                new Property{ Name = "B" }
            };

            var allLetters = list1.Union(list2).Distinct().ToList();

            var result = new List<JoinedProperty>();

            foreach (var letter in allLetters)
            {
                var list1Count = list1.Count(l => l.Name == letter.Name);
                var list2Count = list2.Count(l => l.Name == letter.Name);

                var matchCount = Math.Min(list1Count, list2Count);

                addValuesToResult(result, letter, letter, matchCount);

                var difference = list1Count - list2Count;

                if(difference > 0)
                {
                    addValuesToResult(result, letter, null, difference);                   
                }
                else
                {
                    difference = difference * -1;
                    addValuesToResult(result,null, letter, difference);                   
                }
            }
            foreach(var res in result)
            {
                Console.WriteLine(res.ToString());
            }
            Console.ReadLine();                
        }

        private static void addValuesToResult(List<JoinedProperty> result, Property letter1, Property letter2, int count)
        {
            for (int i = 0; i < count; i++)
            {
                result.Add(new JoinedProperty
                {
                    Name1 = letter1,
                    Name2 = letter2
                });
            }
        }
    }
}

Run this and you get the result

AA
A
A
BB
B

and the contents of the result list are what you are after.

Edit: Updated my answer to use the Property that was specified.

Full outer Join in LINQ optimized for unique keys

Based on the referenced answer I tried something that doesn't create Lookup's but Dictionary's instead.

IMHO that's the only thing you can cut that will actually save some time.

Skipping creation of the HashSet (as proposed in my rushed comment) is not an option as this will lead to the duplication of all joined pairs.

public static class Extensions {
    public static IEnumerable<TResult> FullOuterJoin<TA, TB, TKey, TResult>(
        this IEnumerable<TA> a,
        IEnumerable<TB> b,
        Func<TA, TKey> selectKeyA,
        Func<TB, TKey> selectKeyB,
        Func<TA, TB, TKey, TResult> projection,
        TA defaultA = default(TA),
        TB defaultB = default(TB),
        IEqualityComparer<TKey> cmp = null) {

        cmp = cmp ?? EqualityComparer<TKey>.Default;
        var adict = a.ToDictionary(selectKeyA, cmp);
        var bdict = b.ToDictionary(selectKeyB, cmp);

        var keys = new HashSet<TKey>(adict.Keys, cmp);
        keys.UnionWith(bdict.Keys);

        var join = from key in keys
                   let xa = adict.GetOrDefault(key, defaultA)
                   let xb = bdict.GetOrDefault(key, defaultB)
                   select projection(xa, xb, key);

        return join;
    }

    public static T GetOrDefault<K, T>(this IDictionary<K, T> d, K k, T def = default(T)) 
        => d.TryGetValue(k, out T value) ? value : def;
}

---

Related Topics