how to denormalize a collection which contains a collection using Linq

Question

I am trying to convert the following collection:

Source

"a", "b", {1,2,3}
"d", "f", {1,2,2}
"y", "z", {}

Destination

"a", "b", 1
"a", "b", 2
"a", "b", 3
"d", "f", 1
"d", "f", 2
"d", "f", 2
"y", "z", null

I've researched it, and think that the answer lies somewhere with the SelectMany() method but I can seem to pin down the answer.

The problem is similar to: How do I select a collection within a collection using LINQ? which denormalises the collection but it doesn't show how to include the related columns (columns 1 and 2 in my example).

The difference is that I need to include the first two columns and also return a row where there are no entries in the collection.

Show some code. What's the type of the collections? Something like `IEnumerable>>`? — Henrik, Apr 10 '15 at 12:27
@Henrik Apologies, I should have supplied some code. I don't post up very often but I'll certainly bear that in mind for future posts to make my questions clearer for everyone. By the way, you were spot on with your guess! — dubs, Apr 10 '15 at 13:02

score 2 · Accepted Answer · answered Apr 10 '15 at 12:45

2

Using a List of Tuples as an example:

var source = new List<Tuple<string, string, int[]>> {
    Tuple.Create("a", "b", new int[] {1,2,3}),
    Tuple.Create("d", "f", new int[] {1,2,2}),
    Tuple.Create("y", "z", new int[0])
};


var destination =
from t in source
from i in t.Item3.Select(x => (int?)x).DefaultIfEmpty()
select Tuple.Create(t.Item1, t.Item2, i);

answered Apr 10 '15 at 12:45

Dennis_E

8,751
23
29

bang on the money! Thanks. – dubs Apr 10 '15 at 13:12

score 2 · Answer 2 · answered Apr 10 '15 at 12:47

Suppose there are some suitable types Src and Dst for the elements of Source and Destination. Then, similar to the example from the documentation, the task can be solved with the following statement.

Dest = Source.SelectMany(
    iSrc => iSrc.Third,
    (iSrc,No) => new Dest(){ First = iSrc.First,
                             Second = iSrc.Second,
                             Third = No
                           }
);

score 0 · Answer 3 · answered Apr 10 '15 at 12:45

Check the one of possible solutions:

var source = new List<Tuple<string, string, int[]>>{
        new Tuple<string,string,int[]>("a", "b", new int[]{1,2,3}),
        new Tuple<string,string,int[]>("d", "f", new int[]{1,2,2}),
        new Tuple<string,string,int[]>("y", "z", new int[]{})
    };

    var destination = source.SelectMany(
        tuple => tuple.Item3.Length == 0 ? new int?[] { null } : tuple.Item3.Cast<int?>(),
            (tuple, collectionElement) => new { tuple.Item1, tuple.Item2, collectionElement }
    ).ToList();

how to denormalize a collection which contains a collection using Linq

3 Answers3