Linq selecting range of records

问题:

    var q = (from Comments in db.tblBlogComments where Comments.blogID == this.ID orderby Comments.date descending select new {
        Comments.userID, Comments.comment, Comments.date
    });

This returns ALL my associated records, how best would I select say records #10 to #20 only so I don't load any redundant data?

回答1:

How about:

var q = (
from Comments in db.tblBlogComments 
where Comments.blogID == this.ID 
orderby Comments.date descending 
select new { Comments.userID, Comments.comment, Comments.date }).Skip(10).Take(10);

回答2:

You can use the .Skip() and .Take() methods on your result set. Example:

var q = (from Comments in db.tblBlogComments where Comments.blogID == this.ID orderby Comments.date descending select new {
    Comments.userID, Comments.comment, Comments.date
});

And then use:

int pageSize = 10;
int page = 3;
var currentPage = q.Skip((currentPage - 1) * pageSize).Take(pageSize);

And then

foreach(var item in currentPage)
{
    ...
}

Since Linq uses deferred execution, the actual query will be created and executed during the foreach loop. So the SQL query will only return the records for the current page.

Edit: More information about this subject

回答3:

int start = 10;
int end = 20;
var q = (from Comments in db.tblBlogComments 
            where Comments.blogID == this.ID 
            orderby Comments.date descending 
            select new {
                          Comments.userID, 
                          Comments.comment, 
                          Comments.date
                       }).Skip(start).Take(end - start);

I'm not sure if Skip translates to SQL executed in the database, so this might be not so efficient.