I have two Lists of custom objects:
List1: Year, Month, ValueA
List2: Year, Month, ValueB
I want to get a third List with a merge between the two:
List3: Year, Month, ValueA, ValueB
Is there any elegant way to perform that in LINQ VB.Net?
Example:
List1:
2010 - 6 - 2
2010 - 7 - 5
2010 - 10 - 3
List2:
2010 - 7 - 2
2010 - 8 - 1
2010 - 10 - 2
List3 (result):
2010 - 6 - 2 - 0
2010 - 7 - 5 - 2
2010 - 8 - 0 - 1
2010 - 10 - 3 - 2
Thanks in advance.
Solution
VB.Net translation of the solution :
Dim ListA = From a In List1
Group Join b In List2
On a.Year Equals b.Year And a.Month Equals b.Month Into bGroup = Group
From b In bGroup.DefaultIfEmpty()
Select a.Year, a.Month, a.Value1, Value2 = If(b Is Nothing, 0, b.Value2)
Dim ListB = From b In List2
Group Join a In List1
On b.Year Equals a.Year And b.Month Equals a.Month Into aGroup = Group
From a In aGroup.DefaultIfEmpty()
Select b.Year, b.Month, Value1 = If(a Is Nothing, 0, a.Value1), b.Value2
Dim List3 = ListA.Union(ListB)
Sure, you're looking to perform a Full Outer Join on the data, which doesn't exist in LINQ, so we fake it with two unioned Left Outer Joins:
var A = from a in List1
join b in List2 on new { a.Year, a.Month } equals new { b.Year, b.Month }
into bgroup
from b in bgroup.DefaultIfEmpty()
select new { a.Year, a.Month, a.ValueA, ValueB = (b == null ? 0 : b.ValueB) };
var B = from b in List2
join a in List1 on new { b.Year, b.Month } equals new { a.Year, a.Month }
into agroup
from a in agroup.DefaultIfEmpty()
select new { b.Year, b.Month, ValueA = (a == null ? 0 : a.ValueA), b.ValueB };
var List3 = A.Union(B);
Many apologies for the C#, I could not get my VB.Net example to work for the life of me. You need two left outer joins to be unioned to produce the correct answer. None of the code converters I tried worked either.
The following is VB.Net that LINQPad chokes on, but every example I can find says should be correct:
Dim A = From a In List1 _
Group Join b In List2 _
On New With { a.Year, a.Month } Equals New With { b.Year, b.Month} _
Into bGroup = Group _
From b In bGroup.DefaultIfEmpty() _
Select a.Year, a.Month, a.ValueA, ValueB = If(b Is Nothing, 0, b.ValueB)
Dim B = From b In List2 _
Group Join a In List1 _
On New With { b.Year, b.Month } Equals New With { a.Year, a.Month} _
Into aGroup = Group _
From a In aGroup.DefaultIfEmpty() _
Select b.Year, b.Month, ValueA = If(a Is Nothing, 0, a.ValueA), b.ValueB
Dim List3 = A.Union(B)
Try with this LINQ Extension
public interface IMerge<out T>
{
IEnumerable<IMergeMatched<T>> Matched();
IEnumerable<IMergeMatched<T>> Matched(Func<T, T, bool> predicate);
IEnumerable<T> NotMatchedBySource();
IEnumerable<T> NotMatchedBySource(Func<T, bool> predicate);
IEnumerable<T> NotMatchedByTarget();
IEnumerable<T> NotMatchedByTarget(Func<T, bool> predicate);
}
public interface IMergeMatched<out T>
{
T Source { get; }
T Target { get; }
}
public static class Enumerable
{
public static IMerge<TSource> Merge<TSource>(this IEnumerable<TSource> source, IEnumerable<TSource> target,
Func<TSource, TSource, bool> predicate)
{
return new Merge<TSource>(source, target, predicate);
}
}
public class Merge<T> : IMerge<T>
{
private readonly Func<T, T, bool> _predicate;
private readonly IEnumerable<T> _source;
private readonly IEnumerable<T> _target;
private IEnumerable<IMergeMatched<T>> _matcheds;
private IEnumerable<T> _notMatchedBySource;
private IEnumerable<T> _notMatchedByTarget;
public Merge(IEnumerable<T> source, IEnumerable<T> taget, Func<T, T, bool> predicate)
{
_source = source;
_target = taget;
_predicate = predicate;
}
public IEnumerable<IMergeMatched<T>> Matched()
{
if (_matcheds == null)
{
Analize();
}
return _matcheds;
}
public IEnumerable<IMergeMatched<T>> Matched(Func<T, T, bool> predicate)
{
return Matched()
.Where(t => predicate.Invoke(t.Source, t.Target))
.ToArray();
}
public IEnumerable<T> NotMatchedBySource()
{
if (_notMatchedBySource == null)
{
Analize();
}
return _notMatchedBySource;
}
public IEnumerable<T> NotMatchedBySource(Func<T, bool> predicate)
{
return NotMatchedBySource()
.Where(predicate)
.ToArray();
}
public IEnumerable<T> NotMatchedByTarget()
{
if (_notMatchedByTarget == null)
{
Analize();
}
return _notMatchedByTarget;
}
public IEnumerable<T> NotMatchedByTarget(Func<T, bool> predicate)
{
return NotMatchedByTarget()
.Where(predicate)
.ToArray();
}
private void Analize()
{
var macheds = new List<MergeMached<T>>();
var notMachedBySource = new List<T>(_source);
var notMachedByTarget = new List<T>(_target);
foreach (var source in _source)
{
foreach (var target in _target)
{
var macth = _predicate.Invoke(source, target);
if (!macth) continue;
macheds.Add(new MergeMached<T>(source, target));
notMachedBySource.Remove(source);
notMachedByTarget.Remove(target);
}
}
_matcheds = macheds.ToArray();
_notMatchedBySource = notMachedBySource.ToArray();
_notMatchedByTarget = notMachedByTarget.ToArray();
}
}
public class MergeMached<T> : IMergeMatched<T>
{
public MergeMached(T source, T target)
{
Source = source;
Target = target;
}
public T Source { get; private set; }
public T Target { get; private set; }
}
Impementation:
var source = new List<CustomObject>
{
new CustomObject
{
Year = 2010,
Month = 6,
Value = 2
},
new CustomObject
{
Year = 2010,
Month = 7,
Value = 5
},
new CustomObject
{
Year = 2010,
Month = 10,
Value = 3
}
};
var target = new List<CustomObject>
{
new CustomObject
{
Year = 2010,
Month = 7,
Value = 2
},
new CustomObject
{
Year = 2010,
Month = 8,
Value = 1
},
new CustomObject
{
Year = 2010,
Month = 10,
Value = 2
}
};
var merge = source.Merge(target, (x, y) => x.Year == y.Year && x.Month == y.Month);
var toUpdate = merge.Matched((x, y) => x.Value != y.Value)
.ToArray();
var inSourceButNotInTarget = merge.NotMatchedBySource();
var inTargetButNotInSource = merge.NotMatchedByTarget();
Console.WriteLine("Objects to Update");
foreach (var mergeMatched in toUpdate)
{
Console.WriteLine("Source[{0} -{1} - {2} - {3}]",
mergeMatched.Source.Year,
mergeMatched.Source.Month,
mergeMatched.Source.Value,
mergeMatched.Target.Value);
}
Console.WriteLine("In source but not in target");
foreach (var customObject in inSourceButNotInTarget)
{
Console.WriteLine("Source[{0} -{1} - {2} - 0]",
customObject.Year,
customObject.Month,
customObject.Value);
}
Console.WriteLine("In target but not in source");
foreach (var customObject in inTargetButNotInSource)
{
Console.WriteLine("Source[{0} -{1} - 0 - {2}]",
customObject.Year,
customObject.Month,
customObject.Value);
}
Result:
Objects to Update
2010 -7 - 5 - 2
2010 -10 - 3 - 2
In source but not in target
2010 -6 - 2 - 0
In target but not in source
2010 -8 - 0 - 1
