LINQ: Add RowNumber Column

How can the query below be modified to include a column for row number (ie: one-based index of results)?

var myResult = from currRow in someTable
               where currRow.someCategory == someCategoryValue
               orderby currRow.createdDate descending
               select currRow;

EDIT1: I'm looking for the results to be {idx, col1, col2...col-n} not {idx, row}.

EDIT2: The row number should correspond to result rows not the table rows.

EDIT3: I DataBind these results to a GridView. My goal was to add a row number column to the GridView. Perhaps a different approach would be better.

Solution 1:

Use the method-syntax where Enumerable.Select has an overload with the index:

var myResult = someTable.Select((r, i) => new { Row = r, Index = i })
    .Where(x => x.Row.someCategory == someCategoryValue)
    .OrderByDescending(x => x.Row.createdDate);

Note that this approach presumes that you want the original index of the row in the table and not in the filtered result since i select the index before i filter with Where.

EDIT: I'm looking for the results to be {idx, col1, col2...col-n} not {idx, row}. The row number should correspond to result rows not the table rows.

Then select the anonymous type with all columns you need:

var myResult = someTable.Where(r => r.someCategory == someCategoryValue)
        .OrderByDescending(r => r.createdDate)
        .Select((r, i) => new { idx = i, col1 = r.col1, col2 = r.col2, ...col-n = r.ColN });

Solution 2:

Use this Select method:

Projects each element of a sequence into a new form by incorporating the element's index.

Example:

var myResult = someTable.Where(currRow => currRow.someCategory == someCategoryValue)
                        .OrderByDescending(currRow => currRow.createdDate)
                        .Select((currRow, index) => new {Row = currRow, Index = index + 1});

In response to your edit:

If you want a DataTable as result, you can go the non-Linq way by simply using a DataView and add a additional column afterwards.

someTable.DefaultView.RowFilter = String.Format("someCategory = '{0}'", someCategoryValue);
someTable.DefaultView.Sort = "createdDate";
var resultTable = someTable.DefaultView.ToTable();
resultTable.Columns.Add("Number", typeof(int));
int i = 0;
foreach (DataRow row in resultTable.Rows)
    row["Number"] = ++i;

Solution 3:

what about?

int i;
var myResult = from currRow in someTable
           where currRow.someCategory == someCategoryValue
           orderby currRow.createdDate descending
           select new {Record = i++, currRow};

Solution 4:

Just for fun, here's an alternative to Select with two arguments:

var resultsWithIndexes = myResult.Zip(Enumerable.Range(1, int.MaxValue - 1),
                                      (o, i) => new { Index = i, Result = o });