How do I translate a query that uses ROW_NUMBER() into linq?

Question

My table consists of three columns (sno,name,age). I am retrieving this table from the database with extra column (row number) and I used the following code:

select * from (
    select ROW_NUMBER() over (order by SNo asc)as rowindex,SNo,Name,Age 
    from tblExample) 
    as example where rowindex between ((pageindex*10)+1) and ((pageindex+1)*10)

Note, pageindex is the variable that takes some integer value which is passed by the user.

My database is Sql Server 2008. I want to write the same query using Linq. How do I do that?

Answer 1

You can write query as beow

var index=1;
var pageIndex=1;
var pageSize = 10;
data.Select(x => new
{
    RowIndex = index++,
    Sno = x.Sno,
    Name = x.Name,
    Age = x.Age
}).OrderBy(x => x.Name)
.Skip(pageSize * (pageIndex - 1)).Take(pageSize).ToList();

Answer 2

Not a direct translation, but since your row number only appears to be used for paging, try:

db.tblExample
  .OrderBy(t => t.SNo)
  .Select((t,i) => new {rowindex = i+1, t.SNo, t.Name, t.Age })
  .Skip(pageIndex * 10)
  .Take(10);

EDIT

If Select((t,i) => ...) doesn't work you can try synthesizing the index number by adding it after hydrating the query:

db.tblExample
  .OrderBy(t => t.SNo)
  .Skip(pageIndex * 10)
  .Take(10)
  .AsEnumerable();
  .Select((t,i) => new {rowindex = (pageIndex*10)+i+1, t.SNo, t.Name, t.Age })

You should end up with equivalent SQL other than the fact that the rowindex field will not be returned from the SQL query, but will instead be added by the member selection expression.