3

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.

user1205746
  • 3,110
  • 11
  • 44
  • 73
  • 1
    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
  • 1
    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
  • 1
    @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
  • 1
    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

3 Answers3

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
41686d6564 stands w. Palestine
  • 19,168
  • 12
  • 41
  • 79
Theodor Zoulias
  • 34,835
  • 7
  • 69
  • 104
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);
        }
Qamar Zaman
  • 2,521
  • 2
  • 10
  • 16
0

you can get a Collection:

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