CODEWITHSUNDEEP
sql-serverentity-frameworklinq
Rakoo
Rakoo

Reputation: 546

Entity Framework - SkipWhile

I have a following rows ordered by desc date:

var query = this.DbContext.Items.OrderByDescending(g => g.UpdatedAt);

0 Dog       2016-03-08
9 Cat       2016-03-07
2 Elephant  2016-03-06
8 Apple     2016-03-05
3 Banana    2016-03-04
7 Juice     2016-03-03
4 Potato    2016-03-02
5 Cafee     2016-03-01

The first query returns limited (for example 4) number of ordered elements:

var result = query.Take(4).ToList();

0 Dog       2016-03-08
9 Cat       2016-03-07
2 Elephant  2016-03-06
8 Apple     2016-03-05

The next query should return limited (4) number of ordered elements starting from ID 3:

var result = query.ToList()
             .SkipWhile(g => g.Id != startFrom.Value)
             .Take(limit));

3 Banana    2016-03-04
7 Juice     2016-03-03
4 Potato    2016-03-02
5 Cafee     2016-03-01

The problem is with performance, because SkipWhile is not supported by EF, so I have to fetch all data first.

Of course I can cut all older rows by:

var banana = this.Db.Context.Items.FirstOrDefault(g => g.Id == 3);
var result = query.Where(g => g.CreatedAt < banana.CreatedAt).Take(limit);

Is it possible to achieve that by single, well performance EF query?

Upvotes: 3

Views: 1731

Answers (1)

Gert Arnold
Gert Arnold

Reputation: 109185

Two statements don't always perform worse than one, more complex, statement, but you could do this:

var result = query.Where(g => g.CreatedAt < 
                                  this.DbContext.Items.FirstOrDefault(g2 => g2.Id == 3)
                                 .CreatedAt)
                  .Take(limit);

In fact, it's the only possibility I see to mimic SkipWhile behavior in a SQL-friendly manner.

If you're really tight on performance, you should compare it to the second-best (or maybe best) option:

var createdAt = this.Db.Context.Items
                    .Where(g => g.Id == 3)
                    .Select(g => g.CreatedAt)
                    .FirstOrDefault();
var result = query.Where(g => g.CreatedAt < createdAt).Take(limit);

This only selects one date value from the database in stead of a complete Item.

Upvotes: 3

Related Questions