LINQ pagination on stored procedures?

Question

Is there a way that I can use something like the following code to split the data without loading the entire data set into the program?

var r = from c in entities.GetSearchData(null,"en",null,true) select c; IPagedList<Models.SearchResult> results = r.ToPagedList<Models.SearchResult>(1, 10); 

I am trying to use a stored procedure with LINQ to get a paged result. (By the way, the above code gives "The query result could not be listed more than once").) Is this possible?

Answer 1

It is not possible to test with the Entity Framework at the moment, but regular LINQ-to-SQL allows the following statements:

 var rpage1 = entities.GetSearchData(null,"en",null,true).Skip(0).Take(10) var rpage2 = entities.GetSearchData(null,"en",null,true).Skip(10).Take(10) var rlist = rpage1.ToList(); 

LINQ will generate a sentence like

 WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1 

In my case, the resulting query was (GetConstantsValues ​​- stored procedure):

 SELECT [t1].[value] AS [Value] FROM ( SELECT ROW_NUMBER() OVER (ORDER BY [t0].[value]) AS [ROW_NUMBER], [t0].[value] FROM [dbo].[GetConstantsValues](@p0) AS [t0] ) AS [t1] WHERE [t1].[ROW_NUMBER] BETWEEN @p1 + 1 AND @p1 + @p2 ORDER BY [t1].[ROW_NUMBER] 

Thus, only relevant results are loaded into the program.

I believe that EF should not be very different from it. Perhaps wrong.

Answer 2

No need to return to LINQ to SQL. There is a tweak you can do. Using an entity structure, the result of a stored procedure is an ObjectSet collection, so enumerations will not be resolved more than once unless we transfer them to a regular enumerated collection.

The easiest way to handle this case, as shown below,

 var r = from c in entities.GetSearchData(null,"en",null,true) select c; IPagedList<Models.SearchResult> results = r.ToList().ToPagedList<Models.SearchResult>(1, 10); 

Here r.ToList () does the magic and the code will work without any hiccups!

Note *: I know that this is a rather old post, but I thought about helping those who come here to seek help!