using CTE in Entity framework for paging

I am trying to use CTE in sql server 2008 and consume it in Entity framework.

Following is the SP:

CREATE PROCEDURE GetReportingCategories
    -- Add the parameters for the stored procedure here
    @StartRow INT,
    @EndRow INT,
    @SortDirection VARCHAR(50),
    @SortExpression VARCHAR(50),
    @TotalRecord int OUTPUT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    ;WITH CTE (CategoryTitle, CreatedDate, UpdatedDate, [Status], RowNumber) AS 
    ( 
        SELECT CategoryTitle, 
            CreatedDate, 
            UpdatedDate, 
            [Status],
            ROW_NUMBER() OVER(ORDER BY
                case when @SortExpression='CategoryTitle' and @SortDirection='DESCENDING' then CategoryTitle end DESC,
                case when @SortExpression='CategoryTitle' and @SortDirection='ASCENDING' then CategoryTitle end ASC) AS RowNumber
        FROM Reportin开发者_如何转开发gCategory
    )

    SELECT * INTO #TtCTE FROM CTE;
    SELECT @TotalRecord = ISNULL(SUM(1), 0) FROM #TtCTE;

    SELECT * 
    FROM #TtCTE 
    WHERE RowNumber >= @StartRow AND RowNumber <= @EndRow;
END
GO

SP executes perfectly. But, problem is that after updating model from database in VS2010, when i try to "Add function import", than i don't get column information. So, unable to consume it.

Please guide how to implement custom page w/o CTE using Entity Framework and Stored Procedure. Thanks.

---

Are you unable to use LINQ to query the entities? If you can using the Skip and Take operations may be an easier fix.

---

Finally, i got it working. SET the following at the database and than use CTE or similar things:

SET FMTONLY OFF

FYI: I didn't get time to investigate more about this statement, but i have tried it and it works.

---

I believe the problem is that you are using an inline temporary table—and even more to the point, creating it using SELECT * INTO—and Entity Framework is therefore unable to figure out what the output columns of your stored procedure will be.

What about something like this?

CREATE PROCEDURE GetReportingCategories
    -- Add the parameters for the stored procedure here    
    @StartRow INT,
    @EndRow INT,
    @SortDirection VARCHAR(50),
    @SortExpression VARCHAR(50)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    ;WITH CTE (CategoryTitle, CreatedDate, UpdatedDate, [Status], RowNumber) AS
    (
        SELECT CategoryTitle,             
            CreatedDate,
            UpdatedDate,
            [Status],
            ROW_NUMBER() OVER(ORDER BY
                case when @SortExpression='CategoryTitle' and @SortDirection='DESCENDING' then CategoryTitle end DESC,
                case when @SortExpression='CategoryTitle' and @SortDirection='ASCENDING' then CategoryTitle end ASC) AS RowNumber
        FROM ReportingCategory    
    )
    SELECT 
        (SELECT COUNT(*) FROM CTE) AS TotalRecords,
        CategoryTitle,             
        CreatedDate,
        UpdatedDate,
        [Status]
    FROM CTE
    WHERE RowNumber >= @StartRow AND RowNumber <= @EndRow
END

...then you don't need the temporary table (or a second query), which should allow Entity Framework to see the output columns.