asked on

Linq to SQL 2016 Stored Procedure OFFSET FETCH NEXT incorrect return type

I have an interesting issue that has kept me googling for a couple of days.

I am executing a stored store procedure that implements Offset Fetch Next for custom .net paging.

The entity framework can't seem to figure out the correct return type. It thinks I am trying to return an int instead of a result set. Therefore I can not use select.

sproc code:

      @param1 bit = null,
      @param2 bit = null,
      @pageNumber int = 0,
      @rowsPage int = 10

SELECT id, name
FROM table
WHERE
            (@param1 IS NULL OR (param1 = @param1 ))
            AND (@param1 IS NULL OR (param1 = @param1 ))

      ORDER BY name DESC
      OFFSET ((@pageNumber - 1) * @rowsPage) ROWS
      FETCH NEXT @rowsPage ROWS ONLY

Linq Code.

var result = context.storedProceure(params).Select(s => new object { blah = s.blah, blah1 = s.blah1}).ToList();

Error: 'int' does not contain a definition for 'Select' and no extension method 'Select' accepting a first argument of type 'int' could be found (are you missing a using directive or an assembly reference?)

Linq can figure out the complex result type if I remove the Offset Next Command.

How can I help the entity framework figure out the result type?

I've tried:

SET NOCOUNT ON;

SET FMTONLY OFF;

neither seem to make a difference...

Fernando Soto

Hi dtechfish;

I just tried a stored procedure similar to your pseudocode for a store procedure and it worked with no issues.

Please provide the following to be able to help with this issue.
In Solution Explorer make sure that the Show All Files is selected. Then Expand your EDMX model and also your Model.Context.tt and open the Model.Context.cs file and post the context.storedProceure function here. Please also post the actual stored procedure as well and the actual call to the context.storedProceure.

dtechfish

ASKER

Hi Fernando,

Unfortunately I have my boss telling me that I am not allowed to post the actual code but I'll do my best to get make it generic here.

The stored procedure runs beautifully inside SQL Server 2016 and returns the correct result set, but when trying to execute through entity framework in .net web app, the linq code wants an int instead of the complex return type. This is only when trying to use the OFFSET FETCH feature. If I result back to an older method of sql paging using ROW_NUMBER() OVER (ORDER BY ...) AS RowNumber, the entity framework works too. But the performance using the Offset method is supposed to be much better...

Here's the context code:

    public virtual int sproc_CustomPaging(Nullable<int> p1, Nullable<int> p2, Nullable<System.DateTime> p3, Nullable<System.DateTime> p4, Nullable<int> pageNumber, Nullable<int> rowsPage)
    {
        var p1Parameter = p1.HasValue ?
            new ObjectParameter("p1", p1) :
            new ObjectParameter("p1", typeof(int));

        var p2Parameter = p2.HasValue ?
            new ObjectParameter("p2", p2) :
            new ObjectParameter("p2", typeof(int));

        var p3Parameter = p3.HasValue ?
            new ObjectParameter("p3", p3) :
            new ObjectParameter("p3", typeof(System.DateTime));

        var p4Parameter = p4.HasValue ?
            new ObjectParameter("p4", p4) :
            new ObjectParameter("p4", typeof(System.DateTime));

        return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("sproc_CustomPaging", p1Parameter, p2Parameter, p3Parameter, p4Parameter, pageNumberParameter, rowsPageParameter);
    }

Open in new window

Stored Procedure:

USE [db1]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[sproc_CustomPaging] 

	@p1 int = null, 
	@p2 int = null,
	@p3 DateTime = null,
	@p4 DateTime = null,
	@pageNumber int = 0,
	@rowsPage int = 10

AS
SET NOCOUNT ON;

	BEGIN

  			SELECT			T1.ID, T2.fname, T2.Lname, T3.TypeName, T4.imageID, T5.videoID
			FROM            T1 INNER JOIN
							T2 ON T1.ID = T2.ID INNER JOIN
							T3 ON T1.typeID = T3.typeID INNER JOIN
							T4 ON T1.ID = T4.t1ID LEFT OUTER JOIN
							T5 ON T1.ID = T5.t5ID LEFT OUTER JOIN
			WHERE
							(@p1 IS NULL OR (T1.ID = @p1)) 
							AND (@p2 IS NULL OR (T1.typeID = @p2))
							AND (@p3 IS NULL OR (T1.myTime >= @p3 AND T1.myTime < @p4)) 
			ORDER BY T1.myTime DESC
			OFFSET ((@pageNumber - 1) * @rowsPage) ROWS
			FETCH NEXT @rowsPage ROWS ONLY
			OPTION	(RECOMPILE)
	END

Open in new window

ASKER CERTIFIED SOLUTION

Fernando Soto

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dtechfish

ASKER

Hi Fernando,

This is Entity Framework v6.1.3. The EF model was creating using the "Add ADO.NET Entity Data Model" wizard. Nothing out of the ordinary.

I realize that this is the code generator not being able to detect the return type, but why. If I remove the OFFSET statement, all is well. I'm using SQL Server 2016 Standard (64bit) v13.01728.2. The app is being developed in Visual Studio 2015 with target framework 4.6.1.

SOLUTION

Fernando Soto

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fernando Soto

Any update?

Fernando Soto

Solution was given but author has not updated my last request.