Convert DataTable into a Dictionary using Linq/Lambda

Question

I have a DataTable that I would like to convert into dictionary in C# for my project. I can use the traditional way of programming to achieve the goal but it is not as elegant as using linq/lambda. I tried to use Lambda but I got stuck in how to flatten multiple rows into 1.

I have a mock DataTable for testing purpose.

static DataTable GetData()
{
    DataTable table = new DataTable();
    table.Columns.Add("Field1", typeof(string));
    table.Columns.Add("Field2", typeof(string));

    table.Rows.Add("A", "A1");
    table.Rows.Add("A", "A2");
    table.Rows.Add("B", "B1");
    table.Rows.Add("A", "A3");
    table.Rows.Add("C", "C1");
    table.Rows.Add("D", "D1");
    table.Rows.Add("A", "A5");

    return table;
}

My traditional way to convert it to Dictionary is:

    Dictionary<string, ArrayList> t = new Dictionary<string, ArrayList>();
    foreach (DataRow r in GetData().Rows)
    {
        string k = (string)r["Field1"];
        string v = (string)r["Field2"];
        if (!t.Keys.Contains(r["Field1"]))
        {
            t.Add(k, new ArrayList());
        }
        if (t.Values == null)
        {
            t[k] = new ArrayList();
        }
        t[k].Add(v);
    }

How do I achieve the same thing with Linq?

I have tried:

var res = GetData()
                     .AsEnumerable()
                     .GroupBy(row => row.Field<string>("Field1"))
                     .Select(grp => grp.First());

This only gives me the first occurrence of the item. I am stuck. Please help.

Instead of Select, use ToDictionary and see how far you get. You'll use grp.Key and grp.ToList(). — Anthony Pegram, Apr 25 '19 at 19:09
You may want to read [What's so bad about ArrayLists?](https://stackoverflow.com/q/17238764/215552)... — Heretic Monkey, Apr 25 '19 at 19:12
Possible duplicate of [Linq Expression to Turn DataTable to Dictionary of >](https://stackoverflow.com/questions/21617932/linq-expression-to-turn-datatable-to-dictionary-of-key-listvalues) — Heretic Monkey, Apr 25 '19 at 19:17
Actually you don't want to convert it to a `Dictionary`, but to a [`Lookup`](https://learn.microsoft.com/en-us/dotnet/api/system.linq.lookup-2). — Theodor Zoulias, Apr 25 '19 at 19:24
@HereticMonkey: I know ArrayList is overkill in this simplification but in my project, the object type in the list is not string and not just 1 type. I want it to be generic and can take any type. Thank you for the warning though. — user1205746, Apr 25 '19 at 19:25
@TheodorZoulias: May I ask why do I not want it to be a dictionary but look up? Is it speed related? Performance? — user1205746, Apr 25 '19 at 19:38
@user1205746 Read: [Difference between Lookup() and Dictionary(Of list())](https://stackoverflow.com/q/13362490/4934172) so you can decide what works best for you. — 41686d6564 stands w. Palestine, Apr 25 '19 at 19:53
A lookup allows efficient search for a key, like a dictionary, but unlike a dictionary can hold multiple values per key. It seems that it's the kind of data structure that you are aiming for. It has some restrictions though. It is immutable (read-only), and the values of each key are accessible as an Enumerable, not as an indexed collection (although they can be casted to IList, but this is undocumented and so not future-proof). — Theodor Zoulias, Apr 25 '19 at 20:02

score 4 · Accepted Answer · edited Apr 25 '19 at 19:26

4

Actually, you don't want to convert it to a Dictionary, but to a Lookup. Here's an example:

var lookup = GetData().AsEnumerable()
    .ToLookup(r => r.Field<string>("Field1"), r => r.Field<string>("Field2"));
foreach (var grouping in lookup)
{
    Console.WriteLine(grouping.Key + ": " +  String.Join(", ", grouping));
}

Output:

A: A1, A2, A3, A5
B: B1
C: C1
D: D1

edited Apr 25 '19 at 19:26

41686d6564 stands w. Palestine

19,168
12
41
79

answered Apr 25 '19 at 19:23

Theodor Zoulias

34,835
7
69
104

Qamar Zaman · Answer 2 · 2019-04-25T19:28:10.463

2

Get Data from Datatable as Dictionary without Linq/Lambda

        DataTable dataTable = GetData();
        var data = new List<Dictionary<string, object>>();
        foreach (DataRow dataTableRow in dataTable.Rows)
        {
            var dic = new Dictionary<string, object>();
            foreach (DataColumn tableColumn in dataTable.Columns)
            {
                dic.Add(tableColumn.ColumnName, dataTableRow[tableColumn]);
            }
            data.Add(dic);
        }

edited Apr 25 '19 at 19:28

answered Apr 25 '19 at 19:15

Qamar Zaman

2,521
2
10
16

var data = new List>(); so the object in that dictionary can be any datatype? @Qamar Zaman – Sovit Maharjan Dec 08 '20 at 14:33
Yes @SovitMaharjan – Qamar Zaman Jan 01 '21 at 12:06

score 0 · Answer 3 · answered Apr 25 '19 at 19:14

0

you can get a Collection:

            var res = GetData()
                 .AsEnumerable()
                 .Select(grp => new KeyValuePair<string, string>(grp[0].ToString(), grp[1].ToString()));

answered Apr 25 '19 at 19:14

lem2802

1,152
7
18

Convert DataTable into a Dictionary using Linq/Lambda

3 Answers3

Get Data from Datatable as Dictionary without Linq/Lambda

Linked