How to access columns by index in LINQ

Question

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 each column with their index.

for example, to get data in CL3 as follows:

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

can someone help me?

Answer 1

You can 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 index.

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

Answer 2

The properties of an object are not necessarily in the same order as the columns in the database.

You can do a reflection to select a property by index, but that doesn't make sense. Instead, use column names.

Based on your comment that columns have a name ending with a number, 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)