Why can't Reverse () be converted to SQL?
My application is running under ASP.NET 4.0 which uses BLToolkti as an ORM tool.
I have some requested expression:
var q = db.GetTable<T>()
.Where(tb=>tb.TeamId==MyTeamId && tb.Season==MySeasonId)
.OrderByDescending(tb=>tb.Id)
.Take(20)
.Reverse()
Trying to convert q.ToList () throws 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 + TeamBudget <> c__DisplayClass78) .. season))) OrderByDescending (TB => Convert (tb.Id)). Take (20) .Reverse () 'cannot be converted to SQL.
If I remove ".Reverse ()" from the requested object everything works fine.
What is the reason why the requested object with .Reverse () cannot be converted to SQL? Is this a BLToolkit limitation? Is there any solution for this?
Thank!
source to share
It's pretty clear what other LINQ methods convert to ( where, order by, top(20)
), but what would they convert Reverse()
? I can't think of a SQL statement I've seen that mimics this behavior, and when you query the database, the LINQ statement should eventually resolve to valid SQL.
This may not be what you are going to do, but one option is to execute the query first with ToList()
and 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 can get the score and skip that many entries, although this may not be accurate if the number of entries changes between calls. In addition, these are two requests instead of 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);
source to share