How do I page the results of a Stored Procedure

Question

0.00/5 (No votes)

See more:

Hi,

I have a stored procedure that return results from a few joined tables. I want to page the results at the server.

I get a ISingleResult<type> result. I have a number of extension methods for paging and here is what I have so far:

C#

public static List<T> GetList<T>(this ISingleResult<T> source, int pageSize, int pageIndex, out int resultCount)
{

    List<T> result = null;
    int count = 0;
    TransactionWrapper(() =>
    {
        IEnumerable<T> agg = source;
        count = source.Count();
        if (pageSize > 0)
        {
            agg = agg.Skip(pageSize * pageIndex);
            agg = agg.Take(pageSize);
        }
        result = agg.ToList();
    });
    resultCount = count;
    return result;
}

I get the "the query results cannot be enumerated more than once".

Is there a way around this?

If not then I will have to change my SP into a view and lose a little query functionality, which is livable.

Thanks ^_^
Andy

Posted 26-Jul-15 23:30pm

Andy Lanng

Add a Solution

Comments

Kornfeld Eliyahu Peter 27-Jul-15 6:39am

Does it happening also if pageSize is 0 and also when not zero?

Andy Lanng 27-Jul-15 6:41am

PageSize will always be > zero. I can try with zero but I'm not sure what that would achieve ?

Kornfeld Eliyahu Peter 27-Jul-15 6:42am

Before that try this:
agg.Skip(...).Take(...).ToList();

Kornfeld Eliyahu Peter 27-Jul-15 6:44am

As for the zero part...Just to explain my thinking...
When I see an if I always ask myself what happening in each branch -so that the reason I asking you about that zero...
And! If pageSize ALWAYS greater than zero, why the if?!

Andy Lanng 27-Jul-15 6:46am

If page size = zero then there is no paging and the whole result should be returned. I see what you mean, now. The error does not occur when PageSize = zero.
(my own code and I had to take a second look :Þ )

Kornfeld Eliyahu Peter 27-Jul-15 6:49am

So the problem is with the skip-take-tolist sequence...
Try to break it into two...
if()
{
sipt().take().tolist();
}
else
{
tolist();
}

Andy Lanng 27-Jul-15 6:51am

I will try and get back to you but I'm looking into how / why my boss has managed to break the emails... again >_<

Will post when I get a chance to look ^_^

Er Daljeet Singh 27-Jul-15 7:39am

Instead of using " IEnumerable<t> agg = source;"use List<t> agg=source,ToList();
Hope this will work

Andy Lanng 27-Jul-15 7:41am

That would work, but where my full result is 10's of K large (if not larger) i would like to reduce the total query size by page. I hope that I can do this with linq2sql from a stored proc, but so far it does not look promising :S

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2015-07-27T03:25:00

Stored procedures cannot be composed; there's no way to select a certain range of rows returned from the procedure.

There appear to be four options:

Use .ToList(), as Er Daljeet Singh suggested in the comments:

C#

result = source.ToList();
count = result.Count;

if (pageSize > 0)
{
    result = result.Skip(pageSize * pageIndex).Take(pageSize).ToList();
}

Use two separate stored procedures - one to count the total results, and one to retrieve a single page of data.
Convert your stored procedure to a view or a table valued function[^].

Use a custom extension method to extract a single page of data and count the total number of records in one hit. That way, you don't have to copy all of the results to a List<T> and then throw most of them away.

Something like this should work:

C#

public static class Extensions
{
    public static List<T> GetPage<T>(this IEnumerable<T> source, int pageSize, int pageIndex, out int totalCount)
    {
        if (source == null) throw new ArgumentNullException("source");
        if (pageSize < 0) throw new ArgumentOutOfRangeException("pageSize");
        if (pageIndex < 0) throw new ArgumentOutOfRangeException("pageIndex");
        
        if (pageSize == 0)
        {
            var result = source.ToList();
            totalCount = result.Count;
            return result;
        }
        
        int index = 0;
        int startIndex = pageSize * pageIndex;
        int endIndex = startIndex + pageSize;
        var pageOfData = new List<T>(pageSize);
        
        foreach (T item in source)
        {
            if (startIndex <= index && index < endIndex)
            {
                pageOfData.Add(item);
            }
            
            index++;
        }
        
        totalCount = index;
        return pageOfData;
    }
}

...

public static List<T> GetList<T>(this ISingleResult<T> source, int pageSize, int pageIndex, out int resultCount)
{
    List<T> result = null;
    int count = 0;
	
    TransactionWrapper(() =>
    {
	    result = source.GetPage(pageSize, pageIndex, out count);
    });
	
    resultCount = count;
    return result;
}