Entity Framework SqlQuery not returning row_number() value

Question

I have a SELECT statement that returns row_number() value with all other fields from table. Query is pretty simple and is working in SSMS:

SELECT 
    *, CAST(ROW_NUMBER() OVER(ORDER BY TablePrimaryKey) AS INT) AS [RowNumber]
FROM 
    TableName 
WHERE 
    TablePrimaryKey > 10

(In real application WHERE statement is much more complex, but for simplicity I put it like this)

In my project I created a POCO class that contains RowNumber property (with all other necessary properties)

...
public int RowNumber { get; set; }
...

And I made sure migrations won't create additional columns in table at my entityConfiguration class:

this.Ignore(x => x.RowNumber);

Problem is that with Entity Framework's SqlQuery() method it returns all columns from the table as it should, but RowNumber is always 0. I'm using Entity Framework 6.1 version. Did I miss something, or can this not be done this way?

possible duplicate of [How do I add ROW\_NUMBER to a LINQ query or Entity?](http://stackoverflow.com/questions/1165028/how-do-i-add-row-number-to-a-linq-query-or-entity) — Stephen Kennedy, Oct 26 '14 at 09:31
I saw that answer, and it works but i came to other problem. To get a list of objects from databse with row_number value in that way works well. Problem is what if user wants to get specifically record with row_number() value of 2 in the way we dont have to always return all records from database and then search for that record in return list? — Kapo, Oct 26 '14 at 09:48
If you're using Entity Framework - *why* are you using `SqlQuery` and not the "native" Linq querying that EF offers? — marc_s, Oct 26 '14 at 09:50
Ok, can you help me how to write this query with Linq: WITH main AS ( SELECT * ,ROW_NUMBER() OVER(ORDER BY PrimaryKeyId) AS 'RowNumber' FROM [TableName] WHERE [month] = 10 AND [year] = 2014 ) SELECT * FROM main WHERE RowNumber = 2 All I want is that on my edit page of ASP.MVC project i get row_number() value for that record with all other properties. — Kapo, Oct 26 '14 at 09:56

score 0 · Answer 1 · answered Feb 17 '15 at 23:10

This is to answer your question in comment.

WITH main AS ( SELECT * ,ROW_NUMBER() OVER(ORDER BY PrimaryKeyId) AS 'RowNumber' FROM [TableName] WHERE [month] = 10 AND [year] = 2014 ) SELECT * FROM main WHERE RowNumber = 2

C#/EF code:

int RowNumber = 2;
var row = TableName.OrderBy(t=>t.PrimaryKeyId).Skip(RowNumber -1).Take(1);

The SQL generated looks like:

DECLARE @p0 Int = 1
DECLARE @p1 Int = 1

SELECT [t1].[ID], [t1].c1 FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [t0].[ID]) AS [ROW_NUMBER], 
           [t0].[ID], [t0].c1
    FROM [TableName] AS [t0]
) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
ORDER BY [t1].[ROW_NUMBER]

I am using Linq-to-SQL, EF should be similar: the sorting is on server side, and it returns 1 record only.

Entity Framework SqlQuery not returning row_number() value

1 Answers1