Retrieve a list of object from database in sequence

Question

I am looking for a way to get a list of items in sequence, meaning that each calls will retrieve a set of database values in order.

Ok, that's not very clear, so let me show you.

Say that I have 200 entries of objects with a date properties, each entered from the beginning of February to today.

My first call would retrieve a set of the 20 latest entries, then the next call would retrieve entries 21-40, then the next call 41-60, and so on, as long as there's data to get.

I am using C# / Entity Framework and am familiar with Linq and the LinqKit, but that's a new challenge to me and am opened to any suggestions!

EDIT Ok, based on comments, my example is not adequate.

So let's say instead of having 200 I have 200 MILLIONS of entries, and more are pouring in. But, as I mentioned, I just want to load the first twenty, then the next twenty on each call, but I don't know how many times the user may make the call, so that's why I cannot "align" Take(20) calls since this is dynamic.

EDIT2

Here's a sample of code to guide you and me on my work :)

List<Book> listBooks = (from b in DbContext.Books
                                    orderby b.DateAdded
                                    select b).Take(count).ToList();

See, this chunk of code is not perfect because it will always return the 20 latests books added. But at the second call I would like to have the 20 next, then on the third call the 41-60 books, and so on.

EDIT3

After some research I have found something that might suit my needs:

Paging with LINQ for objects

However I will need to work around a bit to figure out how to make this work.

@GrantWinney Yep, each row will have an ID as primary key, along with the datetime field and so on. — hsim, Mar 02 '14 at 01:33

score 2 · Accepted Answer · answered Mar 02 '14 at 00:49

2

If they are only 200, I recommend to you to get all the data with only one query to the database.

Then store in a for example a list, and then do something like.

list = list.OrderByDescending(x => x.Name).ToList();

Then to get the first 20 elements:

var result = list.Take(20);

And to get the 21-40:

var result = list.Skip(20).Take(20);

This way if insted of 20 you want more or less, you only need to convert 20 in a variable ;)

I hope this helps

answered Mar 02 '14 at 00:49

Oscar Bralo

1,912
13
12

Ok, yeah, I do understand your point and you are right in a way. I have placed a bad example, I will correct this. – hsim Mar 02 '14 at 00:57
As I mentioned in my call, it is a dynamic call and I cannot guess how much times this method will be called. I'd need a way to get the data by blocks of 20 entries, each time by their own filters. – hsim Mar 02 '14 at 01:03
I think that then you can use linq, maybe the same way I said, but instead of a list, do it making a call to the database. Somethink like when you in SQl Management Studio check a table, with SELECT TOP 20.. ORDER BY n.Name.. And opening a conection each time you want to retrieve something.BTW, the approach of my answer I think that will be useful if you have 20000000 of entries too ;)You can try it – Oscar Bralo Mar 02 '14 at 01:07
Yeah, I think that something like this would be ok, but how could I do it in C#? – hsim Mar 02 '14 at 01:34
Try using SqlConnection (passing a connection string), then a SqlCOmmand, passing a query and a connection string, and then something like, var list = command.ExecuteNonQuery(). The qery something like, SELECT TOP 20 FROM xxxx WHERE Name = 'asdsa' ORDER BY Name or soemthing like that – Oscar Bralo Mar 02 '14 at 08:53

score 1 · Answer 2 · answered Sep 21 '15 at 10:58

As has been alluded to, use skip() and take() along with variables to accomplish what you wish:

var numberOfBooksToGrab = 20;
var numberOfTimesYouHaveGrabbedBooks = 0;

List<Book> listBooks = (from b in DbContext.Books
                                    orderby b.DateAdded
                                    select b).Skip(numberOfBooksToGrab * numberOfTimesYouHaveGrabbedBooks)
                                             .Take(numberOfBooksToGrab)
                                             .ToList();

You will obviously need to update the numberOfTimesYouHaveGrabbedBooks variable each time you make the call, but that should be easy enough for you (and will depend on how the rest of your project is structured).

Retrieve a list of object from database in sequence

2 Answers2