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.