CODEWITHSUNDEEP
c#linq
Paul
Paul

Reputation: 3954

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. So, far I have:

IEnumerable<DataRow> rows = MyDataTable.Rows.Cast<DataRow>();
var ExistingColumns = MyDataTable.AsEnumerable().Select(dataRow => rows
    .Select(row => new { 
        Key = row["TABLE_NAME"], 
        Value = new ColumnInfo(){ 
            ColumnName = row["COLUMN_NAME"].ToString(), 
            DataType = row["DATA_TYPE"].ToString(), 
            DataLength = int.Parse(row["DATA_LENGTH"].ToString())
        }
    }).ToDictionary(data => data.Key, data => data.Value));

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.

How can I acheive this conversion using linq?

Thanks!

Upvotes: 2

Views: 4240

Answers (3)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112642

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().

Upvotes: 2

Ahmad Mageed
Ahmad Mageed

Reputation: 96547

GSerg's suggestion to use ToLookup resolves the duplicate keys issue. However, to demonstrate how to achieve this and get a Dictionary, you need to group the table names first, then call ToDictionary:

var query = MyDataTable.AsEnumerable()
                       .GroupBy(row => row["TABLE_NAME"].ToString())
                       .ToDictionary(g => g.Key,
    g => g.Select(row => new ColumnInfo()
    { 
        ColumnName = row["COLUMN_NAME"].ToString(), 
        DataType = row["DATA_TYPE"].ToString(), 
        DataLength = int.Parse(row["DATA_LENGTH"].ToString())
    }));

This returns a Dictionary<string, IEnumerable<ColumnInfo>>. If you need a list instead, simply append ToList() to the Select statement.

Upvotes: 1

GSerg
GSerg

Reputation: 78190

ToDictionary() is one to one.

One to many is ToLookup() with same arguments.

Upvotes: 2

Related Questions