Using C# Linq’s ToDictionary from DataTable

I  have a sql query that I return into a DataTable:

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH 
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME IN (SELECT DBTABLE FROM MY_APP_SETTINGS)

With this, I want to create a Dictionary<string, List<ColumnInfo>>, where the key is TABLE_NAME and ColumnInfo is a custom class with properties to match the other three fields returned in the query.

However, this has two problems: 1) There are duplicate keys, and 2) the Value is just a single instance of the class, not a collection.

To acheive this conversion using linq..

First, use a GroupBy in order to group the rows by table name. This ensures that the table names will be unique.

IEnumerable<DataRow> rows = MyDataTable.Rows.Cast<DataRow>();
Dictionary<string, List<ColumnInfo>> ExistingColumns = rows
    .GroupBy(r => r["TABLE_NAME"].ToString(),
             r => new ColumnInfo {
                 ColumnName = r["COLUMN_NAME"].ToString(),
                 DataType = r["DATA_TYPE"].ToString(),
                 DataLength = Int32.Parse(r["DATA_LENGTH"].ToString())
             }
        )
    .ToDictionary(g => g.Key, g => g.ToList());

The first argument to group by selects the key, the second argument the value. Then it is easy to create the dictionary. The grouping g is an IEnumerable<ColumnInfo>. So the list can simply be created with g.ToList().

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s