C# DataTable to Dictionary with multiple values

Question

I have a DataTable that returns me data as follows:

APP | VERSION
____|________  
app1| 1.0
app1| 2.0
app1| 3.0
app2| 1.0
app3|

As you see, "app1" has multiple versions, and some apps don't have any versions. I need to store this Datatable result in some sort of List for later usage in my code, so I came up with Dictionary, but can't get It to work. Here is what I tried:

    public static Dictionary<string, List<string>> AppVer()
        {
         string SQL = "SELECT column1, column2 FROM myTable a LEFT JOIN 
         myTable2 v ON v.ID_FK=a.ID_PK";
         Dictionary<string, List<string>> versions = new Dictionary<string, 
         List<string>>();

         DataTable dt = new DataTable();

            try
            {
                using (var conn = new OracleConnection(conn_string))
                {
                    conn.Open();

                    using (OracleCommand cmd = new OracleCommand(SQL, conn))
                    {
                        using (OracleDataAdapter dad = new OracleDataAdapter(cmd))
                        {
                            dad.Fill(dt);
                        }
                        versions = dt.AsEnumerable().ToDictionary<DataRow, string, List<string>>(row => row[0].ToString(), new List<string>(row=> row[1].ToString()));
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
             return versions;
        }

However, I get error

"Cannot convert lambda expression to type 'string' because it is not a delegate type".

How could I store my DataTable to dictionary ?

EDIT:

 Dictionary<string, List<Version>> all_versions =  AppVer();

            StringBuilder sb = new StringBuilder();

            foreach (var item in all_versions)
            {
                sb.AppendFormat("{0} - {1}{2}", item.Key, item.Value, Environment.NewLine);
            }

            string result = sb.ToString().TrimEnd();
            MessageBox.Show(result);

Btw, you know that there's a `Version`-class which you could use? So i'd prefer a `Dictionary>` — Tim Schmelter, Mar 05 '18 at 13:24
You may want to look at `.ToLookup` which is very similar to ToDictionary but for when there are duplicate values for the same key. https://msdn.microsoft.com/en-us/library/system.linq.enumerable.tolookup%28v=vs.110%29.aspx — Chris, Mar 05 '18 at 13:26
@Chris, thanks, do you have any examples, I can't find any proper one ? — Lucy82, Mar 05 '18 at 13:29
@TimSchmelter, never done that I'm not quite sure why I'd want to run DB connection twice for same data. If that is what you meant by class "Version". — Lucy82, Mar 05 '18 at 13:30

kara · Answer 1 · 2018-03-05T14:42:12.850

2

ILookup<string, string> myLookup = dt.AsEnumerable().ToLookup(row => row[0].ToString(), row => row[1].ToString());

IEnumerable<string> myValues = myLookup["app1"];

// Loop through lookup.
foreach(var group in myLookup)
{
    string key = group.Key;
    IEnumerable<string> myValues = group;

    Console.WriteLine("Group {0} has {1} elements", key, myValues);
}

edited Mar 05 '18 at 14:42

answered Mar 05 '18 at 13:31

kara

3,205
4
20
34

thans for your answer. But how could I loop through values based on a key ? – Lucy82 Mar 05 '18 at 13:47
1

@Lucy82: I've just made a minor edit to the answer to make it clearer that the second line is fetching the data (I hope you don't mind, kara). At that point you have an `IEnumerable` which will contain all your different versions that you can loop through as you would any other `IEnumerable`. – Chris Mar 05 '18 at 13:52
@kara & Chris, thanks for answer. I like this one too, but Tim answered in first place, so I hope you don't mind ;) – Lucy82 Mar 05 '18 at 13:57
@Chris thanks! Lucy82: I added a foreach-loop to the example. – kara Mar 05 '18 at 15:01
@Lucy82: Its entirely your choice which answer you accept. :) – Chris Mar 05 '18 at 15:03

score 1 · Accepted Answer · answered Mar 05 '18 at 13:31

You need to GroupBy by the key, then you can use ToList on the groups. You know that there's a Version-class which you could use? So i'd prefer a Dictionary<string, List<Version>>:

public static Dictionary<string, List<Version>> AppVer()
{
    Dictionary<string, List<Version>> versions = null;
    string SQL = "SELECT column1, column2 FROM myTable a LEFT JOIN myTable2 v ON v.ID_FK = a.ID_PK";
    var dt = new DataTable();

    try
    {
        using (var conn = new OracleConnection(conn_string))
        using (var da = new OracleDataAdapter(SQL, conn))
        {
            da.Fill(dt);
        }

        versions = dt.AsEnumerable()
            .GroupBy(r => r.Field<string>(0))
            .ToDictionary(
                g => g.Key, 
                g => g.Select(r => Version.Parse(r.Field<string>(1))).ToList());
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }

    return versions;
}

One of the benefits of the Version class it that it sorts correctly, so 10.1 after 2.1.

thanks for this. I got It to work without errors, but how can I loop through values now ? See my edited question, that doesn't work. — Lucy82, Mar 05 '18 at 13:44
@Lucy82: the `Value` is now a `List`, so you could use `sb.AppendFormat("{0} - {1}{2}", item.Key, String.Join(",",item.Value), Environment.NewLine);`. This will concatenate each app-version with comma. If you want a different delimiter change the first parameter of `String.Join` accordingly. — Tim Schmelter, Mar 05 '18 at 13:48
thanks for answer. I accepted It because you were first one :) — Lucy82, Mar 05 '18 at 13:56

C# DataTable to Dictionary with multiple values

2 Answers2

Linked