how to access columns by index in LINQ

Question

Dears, I have a table like this in my Linq to sql class :

ID   CL1    CL2    CL3 ...  CL20
--  ----   ----   -----    ------
1     12     35     54 ....  44
2     11     35     78 ..... 75

data is not important in this example.
I need to access to each column with their index.

for example to reach data in CL3 like this:

var x = db.myTable.single(a=>a.ID==1)[3]; 

can anyone help me please?

Answer 1

Properties in the object are not necessarily in the same order the columns are in the database.

You could do reflection to select a property by index but that wouldn't make sense. You should use names of the columns instead.

Based on your comment that the columns have name ending with a digit here is what you can do.

int columnIndex = 3;
var property = (from p in db.myTable.GetType().GetProperties()
                where p.Name.EndsWith(columnIndex.ToString())
                select p).First();
var record = db.myTable.single(a=>a.ID==1);
var x = property.GetValue(record, null)

Answer 2

You could convert your result to a DataTable like this

public static DataTable ConvertToDataTable<T>(IList<T> list)
{
    var dt = new DataTable();
    var properties = typeof(T).GetProperties();

    foreach (var pi in properties)
        dt.Columns.Add(pi.Name, pi.PropertyType);

    foreach (T element in list) {
        var row = dt.NewRow();
        foreach (var pi in properties)
            row[pi.Name] = pi.GetValue(element, null);
        dt.Rows.Add(row);
    }
    return dt;
}

and then you can access the columns by name or by index.

var dt = ConvertToDataTable<test>(list);
var CL5 = dt.Rows[0][5];
var CL5_by_name = dt.Rows[1]["CL5"];