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?
If, as I'm guessing from the column name, PublicationDate
doesn't change, you can do this in two separate queries:
PublicationDate
of the Article
with Id == 100
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With