CODEWITHSUNDEEP
c#sqllinqlinq-to-sqllinq-to-objects
Lorenzo Polidori
Lorenzo Polidori

Reputation: 10512

LINQ to SQL SkipWhile implementation with duplicate values in the sort column

I have a list of articles sorted by descending rating. If the rating is the same, they are sorted by ascending ID (the IDs are unique):

ID      RATING
9       34
3       32
6       32
8       32
12      32
1       25
2       23

I want to query pages of 3 articles, which means the first page will have articles 9, 3 and 6. This is done by querying the top 3 articles form the sorted list.

Now, I want to take the next 3 articles resuming from article 8, using the article ID as marker for where to resume, as opposed to just skipping the first 3 articles. This is because the content of the article table changes very rapidly and a standard pagination approach like this:

var articles = 
    db.Articles
    .OrderByDescending(a => a.Rating).ThenBy(a => a.Id)
    .Skip(3)
    .Take(3);

would not work reliably because articles can be added or deleted at any time (just assume the rating doesn't change here).

If this was LINQ to Object, I could use SkipWhile:

var articles = 
    db.Articles
    .OrderByDescending(a => a.Rating).ThenBy(a => a.Id)
    .SkipWhile(a => a.Article.Id != 8)
    .Take(3);

but SkipWhile is not implemented in LINQ to SQL (see here).

If I did like this:

var articles = 
    db.Articles
    .Where(a => a.Rating 
             < db.Articles.Single(aa => aa.Id == 8).Rating)
    .OrderByDescending(a => a.Rating)
    .Take(3);

I would skip article 12, while if I did something like this:

var articles = 
    db.Articles
    .Where(a => a.Rating 
             <= db.Articles.Single(aa => aa.Id == 8).Rating)
    .OrderByDescending(a => a.Rating)
    .Take(3);

I would take articles 3 and 6 twice.

Using LINQ to SQL, what's the best way to resume the pagination from article 8, using the article ID as marker for where to resume?

Upvotes: 0

Views: 562

Answers (1)

Dave
Dave

Reputation: 3621

Can you just do:

var articles = 
db.Articles
.OrderByDescending(a => a.Rating).ThenBy(a => a.Id)
.Skip(3)
.Take(3);

Okay, if that won't work, I'd write a function something like this:

    static IEnumerable<Article> GetArticles(List<Article> articles, int articlesToRetrieve, int startingID)
    {
        IEnumerable<Article> results = null;
        results = articles.OrderByDescending(a => a.Rating).ThenBy(a => a.ID);

        if (startingID > 0)
        {
            int lastRating = articles.Single(aa => aa.ID == startingID).Rating;
            results = results.Where(a => a.Rating < lastRating || (a.Rating == lastRating && a.ID > startingID));
        }

        if (articlesToRetrieve > 0)
        {
            results = results.Take(articlesToRetrieve);
        }
        return results;
    }

which I think will do the trick.

Upvotes: 1

Related Questions