How do I add ROW_NUMBER to a LINQ query or Entity?

Question

I'm stumped by this easy data problem.

I'm using the Entity framework and have a database of products. My results page returns a paginated list of these products. Right now my results are ordered by the number of sales of each product, so my code looks like this:

return Products.OrderByDescending(u => u.Sales.Count()); 

This returns an IQueryable dataset of my entities, sorted by the number of sales.

I want my results page to show the rank of each product (in the dataset). My results should look like this:

Page #1 1. Bananas 2. Apples 3. Coffee  Page #2 4. Cookies 5. Ice Cream 6. Lettuce 

I'm expecting that I just want to add a column in my results using the SQL ROW_NUMBER variable...but I don't know how to add this column to my results datatable.

My resulting page does contain a foreach loop, but since I'm using a paginated set I'm guessing using that number to fake a ranking number would NOT be the best approach.

So my question is, how do I add a ROW_NUMBER column to my query results in this case?

Answer 1

Use the indexed overload of Select:

var start = page * rowsPerPage; Products.OrderByDescending(u => u.Sales.Count())     .Skip(start)     .Take(rowsPerPage)     .AsEnumerable()     .Select((u, index) => new { Product = u, Index = index + start }); 

Answer 2

Actually using OrderBy and then Skip + Take generates ROW_NUMBER in EF 4.5 (you can check with SQL Profiler).

I was searching for a way to do the same thing you are asking for and I was able to get what I need through a simplification of Craig's answer:

var start = page * rowsPerPage; Products.OrderByDescending(u => u.Sales.Count())     .Skip(start)     .Take(rowsPerPage)     .ToList(); 

By the way, the generated SQL uses ROW_NUMBER > start and TOP rowsPerPage.