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

asp.net entity-framework linq

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?

Popular Answer

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 });


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why