0

I am using LINQ TO SQL in a ASP.NET MVC application and my code looks something like this

ISingleResult<proc_get_dataResult> sproc_result = db.proc_get_data();
return sproc_result .AsEnumerable().Cast<proc_get_dataResult>().ToList();

My code above returns the entire result set as a list. However, the query in question can return a ton of data so I want to limit the number of objects that are being created. Therefore, I now pass a page number into this method and if for example I pass in page 2, I would only like to return entries 26-50 if the per page constant is set to 25.

Is there a way to do this?

thanks in advance

user327999
  • 443
  • 1
  • 9
  • 21

2 Answers2

1

Well, it will be questionably efficient but it appears to be part of your requirement to query and bring back all the results. From there you can use the Skip() and Take() extension methods in LINQ to get what you want:

 return sproc_result.AsEnumerable().Cast<proc_get_dataResult>().ToList().OrderBy(r=>r.WhateverField).Skip(25).Take(25);

Edit: I forgot you have to OrderBy before you can Skip or Take. Just substitute whatever the ordered field is.

Rick Petersen
  • 734
  • 5
  • 15
0

See this thread.

This isn't an exact duplicate since you're asking in the context of LINQ. You'll need to modify your sproc to use one of the paging methods in that thread. The CTE / Row_Number() over... method is a pretty efficient way of assigning indices to your result set that can then be used to sort/page your results.

Community
  • 1
  • 1
3Dave
  • 28,657
  • 18
  • 88
  • 151
  • Is there a way to do this in LINQ? I do not want to limit the data beging returned from the procedure for this case. – user327999 Dec 05 '12 at 17:55
  • @user327999 not to my knowledge, but that is admittedly limited re LINQ. Your sample indicates that you're calling a sproc. Modify your sproc to use one of the referenced methods. – 3Dave Dec 05 '12 at 17:59
  • 1
    ScottGu has a great writeup on extending LINQ with SPROCs : http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrieving-data-using-stored-procedures.aspx – Reacher Gilt Dec 05 '12 at 18:05
  • @user327999 you said that you don't want to limit the data being returned, but you don't want to return the whole result set, either. Which is it? – 3Dave Dec 05 '12 at 18:15
  • I want to grab all data from the procedure since this procedure is used in a number of different spots throughout my application. Some need all the data and others only need the paginated data. The stored procedure is very efficient however, the performance issues comes when trying to convert the large result set into Data objects. That is the main reason I only want to limit the data within the middle tier – user327999 Dec 05 '12 at 18:32