LINQ display row numbers

Question

I simply want to include a row number against the returned results of my query.

I found the following post that describes what I am trying to achieve but gives me an exception

http://vaultofthoughts.net/LINQRowNumberColumn.aspx

"An expression tree may not contain an assignment operator"

In MS SQL I would just use the ROWNUMBER() function, I'm simply looking for the equivalent in LINQ.

Answer 1

LINQ to SQL allows you to map a SQL function. While I've not tested this, I think this construct will work:

public partial class YourDataContext : DatContext
{
    [Function(Name = "ROWNUMBER")]
    public int RowNumber()
    {
        throw InvalidOperationException("Not called directly.");
    }
}

And write a query as follows:

from author in db.Authors
select new { Lp = db.RowNumber(), Name = author.Name };

Answer 2

Use AsEnumerable() to evaluate the final part of your query on the client, and in that final part add a counter column:

int rowNo = 0;
var results = (from data in db.Data
               // Add any processing to be performed server side
               select data)
              .AsEnumerable()
              .Select(d => new { Data = d, Count = ++rowNo });

Answer 3

I'm not sure whether LINQ to SQL supports it (but it propably will), but there's an overload to the Queryable.Select method that accepts an lambda with an indexer. You can write your query as follows:

db.Authors.Select((author, index) => new
{
    Lp = index, Name = author.Name
});

UPDATE:

I ran a few tests, but unfortunately LINQ to SQL does not support this overload (both 3.5sp1 and 4.0). It throws a NotSupportedException with the message:

Unsupported overload used for query operator 'Select'.