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.

Answer 3

Try this

var x = Products.OrderByDecending(u => u.Sales.Count());
var y = x.ToList();

for(int i = 0; i < y.Count; i++) {
    int myNumber = i; // this is your order number
}

As long as the list stays in the same order, which should happen unless the sales number changes. You could be able to get an accurate count;

There is also this way of doing it.

var page = 2;
var count = 10;
var startIndex = page * count;

var x = Products.OrderByDecending(u => u.Sales.Count());
var y = x.Skip(startIndex).Take(count);

This gives the start index for the page, plus it gives you a small set of sales to display on the page. You just start the counting on your website at startIndex.

Answer 4

Here is a long winded answer. First create a class to house the number/item pair like so:

public class NumberedItem<T>
{
    public readonly int Number;
    public readonly T Item;

    public NumberedItem(int number, T item)
    {
        Item = item;
        Number = number;
    }
}

Next comes an abstraction around a page of items (numbered or not):

class PageOf<T> : IEnumerable<T>
{
    private readonly int startsAt;
    private IEnumerable<T> items;

    public PageOf(int startsAt, IEnumerable<T> items)
    {
        this.startsAt = startsAt;
        this.items = items;
    }

    public IEnumerable<NumberedItem<T>> NumberedItems
    {
        get
        {
            int index = 0;
            foreach (var item in items)
                yield return new NumberedItem<T>(startsAt + index++, item);
            yield break;
        }
    }

    public IEnumerator<T> GetEnumerator()
    {
        foreach (var item in items)
            yield return item;
    }

    System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()
    {
        return this.GetEnumerator();
    }
}

Once you have that you can "Paginate" a particular queryable collection using this:

class PaginatedQueryable<T>
{
    private readonly int PageSize;
    private readonly IQueryable<T> Source;

    public PaginatedQueryable(int PageSize, IQueryable<T> Source)
    {
        this.PageSize = PageSize;
        this.Source = Source;
    }

    public PageOf<T> Page(int pageNum)
    {
        var start = (pageNum - 1) * PageSize;
        return new PageOf<T>(start + 1, Source.Skip(start).Take(PageSize));
    }
}

And finally a nice extension method to cover the ugly:

static class PaginationExtension
{
    public static PaginatedQueryable<T> InPagesOf<T>(this IQueryable<T> target, int PageSize)
    {
        return new PaginatedQueryable<T>(PageSize, target);
    }
}

Which means you can now do this:

var products = Products.OrderByDescending(u => u.Sales.Count()).InPagesOf(20).Page(1);

foreach (var product in products.NumberedItems)
{
    Console.WriteLine("{0} {1}", product.Number, product.Item);
}