Achieve equivelent of T-SQL ROW_NUMBER+OVER in Linq

Question

Is it possible to achieve the following in Linq?

What I am trying to get is a list of records that have a column that displays the row number starting with 1. I need to display the row number in a gridview control to denote visually to the user how many rows have been populated. Why you ask? The user needs to enter an exact number of data points into this grid to match the "sample size". This is an app for recording quality data...

Maybe this can be achieved instead using the RowDataBound event and building it on the fly?

Here is the query:

SELECT ROW_NUMBER() OVER (ORDER BY [InspDataID]) AS 'RowNumber'
,[InspDataID]
      ,[KeyCharID]
      ,[InspValue]
  FROM [IncomingInsp].[dbo].[InspData]
where [KeyCharID] = 41

I am using SubSonic3 and Linq syntax in the project. I am still learning more Linq all the time.

I tried making a view based on the query above (minus the criteria) BUT the criteria gets applied after all the rows are selected and my rownumber column ends up incorrect.

For now I have created a stored proc that take the KeyCharID as criteria. This works. BUT I was hoping to be able to achieve this in Linq. I have seen posts where this ROW_NUMBER function is used in EF for paging but haven't figured it out beyond that...

Thanks for your help,

Josh Blair

Answer 1

LINQ's Select() method has an overload which gives you a row number. Check out http://stackoverflow.com/questions/1165028/how-do-i-add-rownumber-to-a-linq-query-or-entity