How to merge two dictionaries on a common key field using linq in C#

Question

For example, say if I have a the following lists of dictionaries: List<Dictionary<string, object>>

customer = 
[ {Customer: 1, Name: Bob},
  {Customer: 2, Name: Sam},
  {Customer: 3, Name: Rob},
  {Customer: 4, Name: Sally}
]

Purchase = 
[ {Customer: 1, Item: Bat, Cost: 20},
  {Customer: 1, Item: Baseball, Cost: 10},
  {Customer: 3, Item: Basketball, Cost: 10},
  {Customer: 4, Item: Hat, Cost: 10}
]

I want to join these two lists of dictionaries on a common field, similar to SQL inner joins. How would I use LINQ to join on the Customer field?

[
  {Customer: 1, Name: Bob, Item: Bat, Cost: 20},
  {Customer: 1, Name: Bob, Item: Baseball, Cost: 10},
  {Customer: 3, Name: Rob, Item: Basketball, Cost: 10},
  {Customer: 4, Name: Sally, Item: Hat, Cost: 5}
]

Please help! Thanks!

Edit: This is my first time asking a question on here so apologies if it was not as detailed.

Why not create a new `Dictionary` like this. `Dictionary[Object key, List — Levesque Xylia, Jul 23 '21 at 04:39
I guess I would start off by creating a class `Customer`, a property `id` and a property `name` (and a property `name`. Then I would create a `List Orders` and a `List Purchases`. Finally I believe that I would have joined them like this https://www.tutorialsteacher.com/linq/linq-joining-operator-join — Hauns TM, Jul 23 '21 at 04:42
How do you figure that those are lists of dictionaries? They look like arrays of anonymous types to me.. And why does it have to be LINQ? — Caius Jard, Jul 23 '21 at 05:44
The `Purchase` is not a valid `Dictionary`, because it contains the key `"1"` twice. Is it a typo? — Theodor Zoulias, Jul 23 '21 at 06:01

score 1 · Answer 1 · answered Jul 23 '21 at 04:43

You can use Linq. For a bit more readable code I have used in the following example named tuples.

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

var customers = new List<(int Customer, string Name)>
{
    (Customer: 1, Name: "Bob"),
    (Customer: 2, Name: "Sam"),
    (Customer: 3, Name: "Rob"),
    (Customer: 4, Name: "Sally")
};
var purchases = new List<(int Customer, string Item)>
{
    (Customer: 1, Item: "Bat"),
    (Customer: 1, Item: "Baseball"),
    (Customer: 3, Item: "Basketball"),
    (Customer: 4, Item: "Hat")
};

var result = customers.Join(purchases, // join customers with purchases 
    tuple => tuple.Customer, // Id to use on customers
    tuple => tuple.Customer, // Id to use on purchases 
    (tuple, valueTuple) => (Customer: tuple.Customer, Name: tuple.Name, Item: valueTuple.Item)); //     Result construction

foreach (var valueTuple in result)
{
    Console.WriteLine($"{valueTuple.Name} (id: {valueTuple.Customer}) bought {valueTuple.Item}");
}

Output of this is:

Bob (id: 1) bought Bat
Bob (id: 1) bought Baseball
Rob (id: 3) bought Basketball
Sally (id: 4) bought Hat

Vijay Nirmal · Answer 2 · 2021-07-23T17:50:32.963

Here is a solution using Dictionary instead of Tuples

var customers = new List<Dictionary<string, string>>()
{
    new Dictionary<string, string>(StringComparer.Ordinal) { ["Customer"] = "1", ["Name"] = "Bob" },
    new Dictionary<string, string>(StringComparer.Ordinal) { ["Customer"] = "2", ["Name"] = "Sam" },
    new Dictionary<string, string>(StringComparer.Ordinal) { ["Customer"] = "3", ["Name"] = "Rob" },
    new Dictionary<string, string>(StringComparer.Ordinal) { ["Customer"] = "4", ["Name"] = "Sally" }
};
var purchases = new List<Dictionary<string, string>>()
{
    new Dictionary<string, string>(StringComparer.Ordinal) { ["Customer"] = "1", ["Item"] = "Bat" },
    new Dictionary<string, string>(StringComparer.Ordinal) { ["Customer"] = "1", ["Item"] = "Baseball" },
    new Dictionary<string, string>(StringComparer.Ordinal) { ["Customer"] = "3", ["Item"] = "Basketball" },
    new Dictionary<string, string>(StringComparer.Ordinal) { ["Customer"] = "4", ["Item"] = "Hat" }
};

var output = customers.Join(purchases, customer => customer["Customer"], purchase => purchase["Customer"], (customer, purchase) =>
{
    var result = new Dictionary<string, string>(customer, StringComparer.Ordinal);
    purchase.ToList().ForEach(x => result[x.Key] = x.Value);
    return result;
}, StringComparer.Ordinal);

Output (Json format):

[{"Customer":"1","Name":"Bob","Item":"Bat"},
{"Customer":"1","Name":"Bob","Item":"Baseball"},
{"Customer":"3","Name":"Rob","Item":"Basketball"},
{"Customer":"4","Name":"Sally","Item":"Hat"}]

ps; the OP's choice of words is odd; using a dictionary when all we need is a single KeyValuePair is a bizarre concept, kina like declaring all a program's strings to be a `string[1]`.. — Caius Jard, Jul 23 '21 at 10:08
@CaiusJard Completely agree with you, I can't think about a single use case for this question. That's say but I just want to answer his question as it is because recently I saw a meme about stackoverflow, Its say, Question: "How to do A?" Answer 1: "Don't do A, do B", Answer 2: "Don't do B, do C", Person Who Ask Question: ".........". That's why now a days I just answer there questions. ;-) — Vijay Nirmal, Jul 23 '21 at 11:00
This is wrong the name for customer 1 is sam, I am basically trying to replicate an inner join — rz627513, Jul 23 '21 at 13:08
@rz627513 It's right only, Try out this code. While writing this answer I made a mistake in the Sample Output part. Code is right. — Vijay Nirmal, Jul 23 '21 at 17:49

Zealot91 · Answer 3 · 2021-07-25T02:15:38.250

0

This seems to be the answer.

var result = (from t0 in customers
              join t1 in purchases on t0.Customer equals t1.Customer
              select new { Customer: t0.Customer, t0.Name, t1.Item}
              ).ToList()
return result;

This return makes variable result filled with List of items that have the Customer, Name and Item atributes. then you can query it some more or return it as a Json.

edited Jul 25 '21 at 02:15

answered Jul 23 '21 at 05:21

Zealot91

125
3
12

Please always add text to your answer, not just a code-dump, text that tells what the problem is and how your code solves it, else your answer won't be helpful to future site visitors with similar problems, which is the goal of this site. Please read the [answer] link for more on this. – Hovercraft Full Of Eels Jul 23 '21 at 12:58

How to merge two dictionaries on a common key field using linq in C#

3 Answers3