Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to implement SkipWhile with Linq to Sql without first loading the whole list into memory?

I need to order the articles stored in a database by descending publication date and then take the first 20 records after the article with Id == 100.

This is what I would like to do with Linq:

IQueryable<Article> articles = 
    db.Articles
    .OrderByDescending(a => a.PublicationDate)
    .SkipWhile(a => a.Id != 100)
    .Take(20);

However, this generates a NotSupportedException because SkipWhile is not supported in Linq to Sql (see here).

A possible solution is to execute the query and then apply SkipWhile using Linq to Object:

IEnumerable<ArticleDescriptor> articles = 
    db.Articles
    .OrderByDescending(a => a.PublicationDate)
    .ToList()
    .SkipWhile(a => a.Article.Id != 100)
    .Take(20);

But this means I need to load the whole ordered list into memory first and then take 20 articles after the one with Id == 100.

Is there a way to avoid this huge memory consumption?

More in general, what is the best way to achieve this in SQL?

like image 542
Lorenzo Polidori Avatar asked Feb 10 '12 12:02

Lorenzo Polidori


1 Answers

If, as I'm guessing from the column name, PublicationDate doesn't change, you can do this in two separate queries:

  • Establish the PublicationDate of the Article with Id == 100
  • Retrieve the 20 articles from that date onwards

Something like:

var thresholdDate = db.Articles.Single(a => a.Id == 100).PublicationDate;
var articles = 
    db.Articles
    .Where(a => a.PublicationDate <= thresholdDate)
    .OrderByDescending(a => a.PublicationDate)
    .Take(20);

It might even be that LINQ to SQL can translate this:

var articles = 
    db.Articles
    .Where(a => a.PublicationDate 
             <= db.Articles.Single(aa => aa.Id == 100).PublicationDate)
    .OrderByDescending(a => a.PublicationDate)
    .Take(20);

but that may be too complex for it. Try it and see.

like image 154
AakashM Avatar answered Oct 21 '22 07:10

AakashM