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!

What happens when using `.OrderBy(tb => tb.Id)` instead of `.Reverse()`? — user2864740, Aug 13 '14 at 01:45
It does, but the problem it also introduces new aliases to fields and BLToolkit ORM mapping cannot map sql columns to object fields anymore. But thank you for the idea — Budda, Aug 13 '14 at 02:36

score 4 · Accepted Answer · answered Aug 13 '14 at 01:54

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);

"Reverse" by a descending ordering over a [ROW_NUMBER](http://msdn.microsoft.com/en-us/library/ms186734.aspx) equivalent (even without knowning the initial ordered column) in an outer select of a derived table is very possible. It is trivial in SQL Server and possible in MySQL; not sure about other vendors. — user2864740, Aug 13 '14 at 06:16
Also, with correct transaction isolation levels (and usage), there should be no issue with the 2nd approach and atomicity/consistency for a proper ACID provider. (In EF/L2S, I'd just use a TransactionScope and call it a day.) — user2864740, Aug 13 '14 at 06:22

Why Reverse() cannot be converted into SQL?

1 Answers1