Why Reverse() cannot be converted into SQL?

Question

My application is running under ASP.NET 4.0, which uses BLToolkti as ORM tool.

I have some queryable expression:

var q = db.GetTable<T>()
    .Where(tb=>tb.TeamId==MyTeamId && tb.Season==MySeasonId)
    .OrderByDescending(tb=>tb.Id)
    .Take(20)
    .Reverse()

Attempt to convert q.ToList() causes the following error:

Sequence 'Table(TeamBudget).Where(tb => ((tb.TeamId == value(VfmElita.DataLogicLayer.Teams.Team+TeamBudget+<>c__DisplayClass78).teamId) AndAlso (tb.Season == value(VfmElita.DataLogicLayer.Teams.Team+TeamBudget+<>c__DisplayClass78).season))).OrderByDescending(tb => Convert(tb.Id)).Take(20).Reverse()' cannot be converted to SQL.

If I remove ".Reverse()" from the queryable object everything works fine.

What is the reason why queryable object with .Reverse() cannot be converted into SQL? Is that BLToolkit limitation? Is there any solution workaround for that?

Thank you!

Answer 1

It's pretty clear what the other LINQ methods convert to (where, order by, top(20)), but what would Reverse() convert to? I can't think of an SQL statement I've seen that mimics that behavior, and when you're querying the database your LINQ statement must ultimately resolve to valid SQL.

This may not be what you're going for, but one option would be to execute the query first using ToList(), then apply Reverse():

var q = db.GetTable<T>()
          .Where(tb => tb.TeamId == MyTeamId && tb.Season == MySeasonId)
          .OrderByDescending(tb => tb.Id)
          .Take(20)
          .ToList()
          .Reverse();

Alternatively, you could get the count and skip that many records first, although this could be inaccurate if the number of records change between calls. Plus it's two queries instead of just one.

var totalRecords = db.GetTable<T>()
                     .Count(tb => tb.TeamId == MyTeamId && tb.Season == MySeasonId);

var q = db.GetTable<T>()
          .Where(tb => tb.TeamId == MyTeamId && tb.Season == MySeasonId)
          .Order(tb => tb.Id)
          .Skip(totalRecords)
          .Take(20);