Transform a DataTable into Dictionary C#

Question

I want to know how to transform a DataTable into a Dictionary. I did something like this.

using System.Linq;

internal Dictionary<string,object> GetDict(DataTable dt)
{
    return dt.AsEnumerable()
      .ToDictionary<string, object>(row => row.Field<string>(0),
                                row => row.Field<object>(1));
}

But I get:

System.Data.EnumerableRowCollection does not contains a definition for 'ToDictionary' and the best extension method overload 'System.Linq.Parallel.Enumerable.ToDictionary(System.Linq.ParallelQuery, System.Func, System.Collections.Generic.IEqualityComrparer)' has some invalid argumentsch

How can I resolve this?

Thanks

Please post the complete error message. – cadrell0 Oct 31 '13 at 17:39 — cadrell0, Oct 31 '13 at 17:39
I added the full error message – Maximus Decimus Oct 31 '13 at 17:45 — Maximus Decimus, Oct 31 '13 at 17:45

score 100 · Accepted Answer · answered Oct 31 '13 at 17:44

100

The generic method ToDictionary has 3 parameters. You left one off, so it doesn't know what to do. If you want to specify all of the parameters, it would be <DataRow, string, object>.

internal Dictionary<string,object> GetDict(DataTable dt)
{
    return dt.AsEnumerable()
      .ToDictionary<DataRow, string, object>(row => row.Field<string>(0),
                                row => row.Field<object>(1));
}

Of course, if you leave them off, the compiler is able to infer the types, so you don't get the error.

answered Oct 31 '13 at 17:44

cadrell0

17,109
5
51
69

1

+1, yes, this is the main reason, more details at [ToDictionary](http://msdn.microsoft.com/en-us/library/bb548657.aspx) – Ilya Ivanov Oct 31 '13 at 17:46
@Ilaria You have to cast it to an enumerable one way or another, or you can't use Linq. Another way to do it is `dt.Rows.Cast()`. – Kilazur Apr 25 '19 at 07:58

score 20 · Answer 2 · answered Mar 06 '14 at 09:46

20

All the previos answers didn't help me, so I did this:

myList = dt.AsEnumerable()
.ToDictionary<DataRow, string, string>(row => row[0].ToString(),
                                       row => row[1].ToString());

and it worked great!

answered Mar 06 '14 at 09:46

ParPar

7,355
7
43
56

Yakir Manor · Answer 3 · 2018-06-24T13:37:36.980

i prefer this method:

public static List<Dictionary<string, string>> GetDataTableDictionaryList(DataTable dt)
{
    return dt.AsEnumerable().Select(
        row => dt.Columns.Cast<DataColumn>().ToDictionary(
            column => column.ColumnName,
            column => row[column].ToString()
        )).ToList();
}

the reason why is because this code can also deal with Booleans or other data types by calling the ToString method.

Notice this returns a list of dictionaries, you can modify it to a dictionary of dictionaries if you have key for each row.

iterate over a bool column might look like so:

var list = GetDataTableDictionaryList(dt);

foreach (var row in list)
{
    if (row["Selected"].Equals("true", StringComparison.OrdinalIgnoreCase))
    {
        // do something
    }
}

Kevin · Answer 4 · 2013-10-31T17:47:03.340

ToDictionary is expecting the IEnumberable<T> as the first type... you were telling it that it was a string which is wrong it's IEnumerable<DataRow>

It's getting confused by you specifying the types... try this...

internal Dictionary<string,object> GetDict(DataTable dt)
{
    return dt.AsEnumerable()
      .ToDictionary(row => row.Field<string>(0),
                                row => row.Field<object>(1));
}

score 3 · Answer 5 · answered Mar 29 '19 at 20:04

Given solutions assume only 2 columns. In case you want multi column representation, you need a list of dictionary

class Program
{
    static void Main(string[] args)
    {
        DataTable dt = new DataTable();
        dt.Columns.Add("Column1");
        dt.Columns.Add("Column2");
        dt.Columns.Add("Column3");
        dt.Rows.Add(1, "first", "A");
        dt.Rows.Add(2, "second", "B");

        var dictTable = DataTableToDictionaryList(dt);
        var rowCount = dictTable.Count;
        var colCount = dictTable[0].Count;

        //Linq version
        var dictTableFromLinq = dt.AsEnumerable().Select(
                // ...then iterate through the columns...  
                row => dt.Columns.Cast<DataColumn>().ToDictionary(
                    // ...and find the key value pairs for the dictionary  
                    column => column.ColumnName,    // Key  
                    column => row[column] as string // Value  
                    )
                ).ToList();
    }

    public static List<Dictionary<string, object>> DataTableToDictionaryList(DataTable dt)
    {
        var result = new List<Dictionary<string, object>>();
        //or var result = new List<Dictionary<string, string>>();

        foreach (DataRow row in dt.Rows)
        {
            var dictRow = new Dictionary<string, object>();
            foreach (DataColumn col in dt.Columns)
            {
                dictRow.Add(col.ColumnName, row[col]);
                //or dictRow.Add(col.ColumnName, row[col].ToString());
            }

            result.Add(dictRow);
        }

        return result;
    }
}

score 2 · Answer 6 · answered Oct 31 '13 at 17:41

I found the solution but don't know why. I edited my Question completing the code just for make it clear what I was doing an I changed to this

    internal Dictionary<string, object> GetDict(DataTable dt)
    {
        Dictionary<String, Object> dic = dt.AsEnumerable().ToDictionary(row => row.Field<String>(0), row => row.Field<Object>(1));
        return dic;
    }

score 2 · Answer 7 · answered Oct 31 '13 at 17:43

2

i think this will help you:

            DataTable dt = new DataTable();
            dt.Columns.Add("Column1");
            dt.Columns.Add("Column2");
            dt.Rows.Add(1, "first");
            dt.Rows.Add(2, "second");
            var dictionary = dt.Rows.OfType<DataRow>().ToDictionary(d => d.Field<string>(0), v => v.Field<object>(1));

answered Oct 31 '13 at 17:43

terrybozzio

4,424
1
19
25

Compiler error: 'DataRow' does not contain a definition for 'Field' and no accessible extension method 'Field' accepting... – Pawel Cioch Mar 29 '19 at 19:24
Needed to install System.Data.DataSetExtensions.dll in .NET Core, all good – Pawel Cioch Mar 29 '19 at 19:27

score 0 · Answer 8 · answered Mar 02 '20 at 20:43

Visual basic
Using sql to load a datatable and create a dictionary

    Dim SalesRep As New System.Collections.Generic.Dictionary(Of String, String)(StringComparer.InvariantCultureIgnoreCase)
    Using tbl As New Data.DataTable("SalesRep")
        SqlCommand.CommandText = "Select Initial,FullName from QB_SalesRep"
        tbl.Load(SqlCommand.ExecuteReader())
        ' --- Option ONE use the array to iterate
        Array.ForEach(tbl.Rows.Cast(Of Data.DataRow).ToArray(),
                      Sub(f)
                          SalesRep.Add(f.ItemAsString("Initial"), f.ItemAsString("FullName"))
                      End Sub)
        ' --- Option TWO use plain linq to create the dictionary
        SalesRep = tbl.Rows _
            .Cast(Of Data.DataRow) _
            .AsEnumerable() _
            .ToDictionary(Function(f) f.Item("Initial").toString(), Function(f) f.Item("FullName").toString)
    End Using

score -1 · Answer 9 · answered Aug 28 '22 at 08:48

I found the solution above works but it formats my multiple row table to 2 items only.

To fix this I found something on codegrepper really helpful

private List<Dictionary<string, object>> DataTableToDictionary(DataTable dt)
{
    var dictionaries = new List<Dictionary<string, object>>();
    foreach (DataRow row in dt.Rows)
    {
        Dictionary<string, object> dictionary = Enumerable.Range(0, dt.Columns.Count).ToDictionary(i => dt.Columns[i].ColumnName, i => row.ItemArray[i]);
        dictionaries.Add(dictionary);
    }

    return dictionaries;
}

reference: codegrepper.com Convert DataTable to Dictionary in C#

Transform a DataTable into Dictionary C#

9 Answers9

Linked