Convert a DataTable to IList

Question

I am updating some legacy that has a Table that only contains one row. I want to convert it to an IList. The idea is that the first property of IFoo is Name which would be the column name from the Table and the second if Value which is the value of the column.

/// <summary>
/// Name = Column Name
/// </summary>
String Name { get; set; }
/// <summary>
/// Value = Column Value
/// </summary>
String Value { get; set; }

The data may look something this

Foo1     Foo2     Foo3    Foo4     Foo5

xyz      zyx      abc     def      ghi

And would be:

Foo1, xyz

Foo2, zyx

Foo3, abc

Foo4, def

Foo5, ghi

I am not really sure how to accomplish this. Seems like there could be a linq query to do it. Any help would be appreciated.

Rhonda

Why not an `IDictionary` since you're essentially recreating a Key Value pair? — Daniel Gimenez, Aug 08 '13 at 22:40
IDictionary would work as well. Still not really sure how to code it though. — Rhonda, Aug 08 '13 at 22:42

score 4 · Accepted Answer · answered Aug 08 '13 at 22:43

4

Perhaps (works also if the table contains more rows):

IList<Foo> data = table.AsEnumerable()
    .SelectMany(r => table.Columns.Cast<DataColumn>()
        .Select(col => new Foo
        {
            Name = col.ColumnName,
            Value = r.IsNull(col) ? null : r[col].ToString()
        }))
    .ToList();

answered Aug 08 '13 at 22:43

Tim Schmelter

450,073
74
686
939

+1 nice approach. but it gives me an error - The name 'r' does not exist in the current context. – Roman Pekar Aug 09 '13 at 05:38
'r' is the DataRow. I've tested the code and it works (for me). Does the compiler recognize AsEnumeble? Otherwise you need to add the reference to the DataTableExtensions.dll first. – Tim Schmelter Aug 09 '13 at 07:19
yes I see that r should be current row, and AsEnumerable() is working, but still got an error. I've tried it in Roslyn C# interactive, may be this is the case. I'll check it later in C# code. – Roman Pekar Aug 09 '13 at 07:25
That worked great. Although I am considering rewritng the code completely at some point, this will at least get me through this next deployment. – Rhonda Aug 09 '13 at 17:49

score 0 · Answer 2 · edited May 23 '17 at 12:32

Looks like classic SQL columns to rows task. I've added column ID so you can use this query for more than one row:

var dt = new DataTable();
dt.Columns.Add("ID");
dt.Columns.Add("Foo1");
dt.Columns.Add("Foo2");
dt.Rows.Add(1, "xyz", "abc");
dt.Rows.Add(2, "cds", "tgf");

var q = from row in dt.Rows.Cast<DataRow>()
        from col in dt.Columns.Cast<DataColumn>()
        select new {
            ID = row["ID"],
            Name = col.ColumnName,
            Value = row.IsNull(col) ? null : row[col]
        };
q.ToList()

ID, Name, Value
1   Foo1  xyz
1   Foo2  abc
2   Foo1  cds
2   Foo2  tgf

or, if you want to create a Dictionary

q.ToDictionary(x => new { x.ID, x.Name}, x => x.Value)

Convert a DataTable to IList

2 Answers2