views:

32

answers:

1

Following situation: There exists an array acting as crosstable (mergeSet). Additionally there exists a set of target values and a set of source values. The source values can be joined with the target values via the crosstable. - But how is it possible to express an outer join of, e.g., the targetSet against the other tables by using only one LINQ expression? Currently I found only solutions using multiple LINQ expressions (i.e. an inner join of targetSet+mergeSet+sourceSet, then the pure left outer part and finally a concatenation of the innerJoinResult and the outerPart).

        var mergeSet = new[] 
        {
            new KeyValuePair<int, int>(3, 4), 
            new KeyValuePair<int, int>(5, 6)
        };

        var targetSet = new[] { 1, 3, 5, 7 };
        var sourceSet = new[] { 4, 6 };

        var innerJoinResult =
          from mergeItem in mergeSet
          join sourceItem in sourceSet on mergeItem.Value equals sourceItem
          join targetItem in targetSet on mergeItem.Key equals targetItem
          group sourceItem by targetItem;

        var outerPart =
            from targetItem in targetSet
            where !mergeSet.Any(mergeItem => mergeItem.Key == targetItem)
            group 0 by targetItem; // Fill the right part with zero.

        var outerJoinResult = outerPart.Concat(innerJoinResult);
+1  A: 

The following code:

    var outerJoinResult =
        (from ti1 in targetSet
         join mi1 in mergeSet on ti1 equals mi1.Key into joinedMerge
         from jm1 in joinedMerge.DefaultIfEmpty()
         join si1 in sourceSet on jm1.Value equals si1 into joinedSource
         from js1 in joinedSource.DefaultIfEmpty()
         select new { target = ti1, source = js1 }).ToList();

    outerJoinResult.ForEach(x => Console.Out.WriteLine("Source: {0}, Target: {1}", x.source, x.target));

printed:

Source: 0, Target: 1
Source: 4, Target: 3
Source: 6, Target: 5
Source: 0, Target: 7
AntonioR
Thank you AntonioR! My final solution ended like this (the select projection was replaced by a suitable group clause):var outerJoinResult = from ti1 in targetSet join mi1 in mergeSet on ti1 equals mi1.Key into joinedMerge from jm1 in joinedMerge.DefaultIfEmpty() join si1 in sourceSet on jm1.Value equals si1 into joinedSource from js1 in joinedSource.DefaultIfEmpty() group js1 by ti1;
Nico