How to Join two dictionary collections linq query

Question

My first data collection is like this:

IEnumerable<Dictionary<string, object>> firstSourceData;

Items are like this:

new Dictionary<string, object>
{
    ["id"] = 1,
    ["name"] = "some",
    ["age"] = 30
}

And my second data is another dictionary collection:

IEnumerable<Dictionary<string, object>> secondSourceData;

Items are like this:

new Dictionary<string, object>
{
    ["id"] = 1,
    ["sales"] = 58,
    ["age"] = 30
}

These two data comes from different sources and I will create a single dictionary collection that does not contain duplicated values. Only Id key is standart for Dictianaries and other properties may be change.

IEnumerable<Dictionary<string, object>> joined;

new Dictionary<string, object>
{
    ["id"] = 1,
    ["sales"] = 58,
    ["name"] = "some",
    ["age"] = 30
},

How can I do this with LINQ lambda expressions? (And is there any problem if sources length difference)

Joined dictioanry collection will not contain duplicated items. — barteloma, Aug 15 '17 at 08:32
Any reason why data is stored in a dictionary instead of a class? — Gilad Green, Aug 15 '17 at 08:41
@GiladGreen Because property names may be changed in some requests. So only id parameter key does not change. So I will join collecitons by id. — barteloma, Aug 15 '17 at 08:43

Gilad Green · Accepted Answer · 2017-08-15T11:17:42.400

2

This is what you are looking to do:

Merge the two collections of dictionaries.
Group items by the "id" key-value.
For each group you have multiple dictionaries so use SelectMany to flatten and then GroupBy on the key. Now you can recreate the dictionaries - ToDictionary. Notice that you might have keys repeating themselves so that is why the nested GroupBy and for the value select the one you want. Here I just used FirstOrDefault

So:

var result = firstSourceData.Concat(secondSourceData)
                .GroupBy(item => item["id"])
                .Select(group => group.SelectMany(item => item)
                                      .GroupBy(item => item.Key)
                                      .ToDictionary(key => key.Key, 
                                                    value => value.FirstOrDefault().Value));

This is the result:

new Dictionary<string, object>
{
    ["id"] = 1,
    ["sales"] = 58,
    ["name"] = "some",
    ["age"] = 30
},
new Dictionary<string, object>
{
    ["id"] = 2,
    ["sales"] = 58,
    ["age"] = 30
}

For this test case:

var firstSourceData = new List<Dictionary<string, object>>
{
    new Dictionary<string, object>
    {
        ["id"] = 1,
        ["sales"] = 58,
        ["age"] = 30
    },
    new Dictionary<string, object>
    {
        ["id"] = 2,
        ["sales"] = 58,
        ["age"] = 30
    }
};

var secondSourceData = new List<Dictionary<string, object>>
{
    new Dictionary<string, object>
    {
        ["id"] = 1,
        ["name"] = "some",
        ["age"] = 30
    }
};

edited Aug 15 '17 at 11:17

answered Aug 15 '17 at 10:49

Gilad Green

36,708
7
61
95

1

`.ToDictionary(key => key.Key, value => value.FirstOrDefault().Value)` is worked for me. Thanks – barteloma Aug 15 '17 at 10:54
@bookmarker - ops true :) Otherwise the value is `KeyValuePair`. Corrected – Gilad Green Aug 15 '17 at 11:18
Can we do this without using id key. Can you join all, witout duplicates? So all properties will insert in new dictionary. – barteloma Aug 15 '17 at 12:25
@bookmarker - what is the output you want? Still a collection of dictionaries? – Gilad Green Aug 15 '17 at 12:31
yes collection of dictionary. Join dictionary but not duplicated. – barteloma Aug 15 '17 at 12:33
@bookmarker - join dictionaries but how? If you want to "join" you need a key to join by – Gilad Green Aug 15 '17 at 12:37
For example get first Dictionary properties of collections and create a new dictionary. But duplicated pairs will not contain. – barteloma Aug 15 '17 at 12:41
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/151958/discussion-between-gilad-green-and-bookmarker). – Gilad Green Aug 15 '17 at 13:18
@bookmarker That is a follow-up question and should be asked in a separate question. If you post one you can tag me and I will be happy to help – Gilad Green Aug 15 '17 at 16:10

Jakub Dąbek · Answer 2 · 2017-08-15T09:39:24.500

0

You can use Join like this

from dict1 in firstSourceData
join dict2 in secondSourceData
on dict1["id"] equals dict2["id"]
select dict1.Concat(                                      //concatenates 2 dictionaries together
          dict2.Where(kv => !dict1.ContainsKey(kv.Key))   //chooses non-repeating keys
       ).ToDictionary(kv => kv.Key, kv => kv.Value)       //gets a new dictionary from that

If the lengths of the sources are different join will only select these dictionaries, whose ids are in the first source. If you want to get all ids, regardless whether you have data from both sources, you can use DefaultIfEmpty

from dict1 in firstSourceData
join tempDict2 in secondSourceData
on dict1["id"] equals tempDict2["id"] into joined
from dict2 in joined.DefaultIfEmpty(new Dictionary<string, object>()) //make a new dictionary if there's none
select dict1.Concat(
          dict2.Where(kv => !dict1.ContainsKey(kv.Key))
       ).ToDictionary(kv => kv.Key, kv => kv.Value)

If you want dictionaries from both regardless of ids refer to this question.

edited Aug 15 '17 at 09:39

answered Aug 15 '17 at 09:02

Jakub Dąbek

1,044
1
8
17

Zip doesn't join by Id but by index. OP wants a flattened version where the collection containsone dictionary for every id and with all collected properties that belong to this id and were found in all dictionaries. – Tim Schmelter Aug 15 '17 at 09:02
well, op hasn't mentioned anything like that :) – Tim Schmelter Aug 15 '17 at 09:04
but you make assumptions that were never mentioned, OP has just said that he wants one dictionary for every id which also contains all other "properties"(key-value pairs) that belong to that ID and was collected from all dictionaries in both sequences. The only thing that is static is that every dictionary contains the id-key. – Tim Schmelter Aug 15 '17 at 09:09
What if one source contains id's that are not in the other? This approach excludes them. – Tim Schmelter Aug 15 '17 at 09:18

score -1 · Answer 3 · answered Aug 15 '17 at 08:29

You can do Union:

Dictionary<string,object> dict1 = new Dictionary<string, object>();
        dict1.Add("id", 1);
        dict1.Add("name", "Some");
        dict1.Add("age", 30);

        Dictionary<string, object> dict2 = new Dictionary<string, object>();
        dict2.Add("id", 1);
        dict2.Add("sales", 58);
        dict2.Add("age", 30);

        Dictionary<string, object> dict3 = new Dictionary<string, object>();
        dict3 = dict1.Union(dict2).ToDictionary(c => c.Key, c => c.Value);

Resulting values are:

[0] = {[id, 1]}
[1] = {[name, Some]}
[2] = {[age, 30]}
[3] = {[sales, 58]}

Not `Dictionary` it s collection `IEnumerabl> ` – barteloma Aug 15 '17 at 09:25 — barteloma, Aug 15 '17 at 09:25

How to Join two dictionary collections linq query

3 Answers3