Using EntityFramework, LINQ and Paging with Oracle DBs
Note: this blog was written years ago (left it in my SublimeText editor until I can find motivation to make it a blog) and never got around to posting it until I saw a twitter conversation between @julielerman and @paulpacurar.
I was in the middle of completing a MVC3 project using SQL Server 2008 and Entity Framework 4.3.1 until we switched gears and decided on using Oracle as the database. For a good reason at that too, so we won't delve into which databases are better. Besides, you know which database is better, don't you? ;-)
Anyhoo, converting projects from SQL Server to Oracle is a huge undertaking in itself. Looking at Oracle in the Entity Framework designer is just garish! Everything is in CAPS and UNDERSCORES! Ugh! (paging Roosevelt E. Roosevelt talking about the heat).
To supplement my MVC3 app, I was using Troy Goode's PagedList MVC solution and it's a great go-top grid/paging solution that'll work using ajax calls in jQuery popups as well as rendering it in a MVC View.
At first I was happily programming along and writing paging stuff as to not bring down the server on its knees with 1000s of records; deployed it into our test environment. Test users started reporting that records weren't displaying correctly and not in the right order. Looked at it again, tested it again, it works perfectly. Asked the users what EXACTLY were you querying.
Using the query parameters I got from the test users, I had a look at the LINQ-generated SQL code using the ToTraceString(). [n.b.: in EF6+ using DbContext, just simple .ToString() will do.]
var sql = ((System.Data.Objects.ObjectQuery)q).ToTraceString();
That's when it dawned me that NULLs were at the top of the Orders. Just so happens that my test data doesn't have NULLs in the fields I'm sorting. My data is perfect, the users' aren't. (that seems to always happens!)
This is the fix (I think I got it from stackoverflow, been a while.):
result .OrderBy(e => e.UpdateTimeStamp == null) .ThenByDescending(e => e.UpdateTimeStamp) .ToPagedList(pageNumber, 15);
Why did I do 'OrderBy' and 'ThenByDescending' in the same LINQ code?
By default, Oracle handles NULLs differently than SQL Server. In SQL Server if you do an ORDER BY on a field that has NULLs in them, the NULLs are at the end of orders. Oracle has it at the start of orders. Like butter and margarine; same in concept but different.
Even then, the .ToPagedList() alone doesn't work since Oracle causes issues in Troy's library since he uses Enumerators all over (not the 'true' query yet) so had to force the LINQ query to return the FULL queried list then we may page it.
result .OrderBy(e => e.UpdateTimeStamp == null) .ThenByDescending(e => e.UpdateTimeStamp) .ToList() .ToPagedList(pageNumber, 15);
So if you plan on using .ToList(), try and filter your data down as much as possible using Select/anonymous types or go outside the Entity Framework (a stubborn no!) and pull in data using procedures or views with limited number of columns that you only need in your paging data's ViewModel.
Email or comment away if you have questions/suggestions! The MVC/Oracle app is still running for 2+ years now and there's always ways to take some ideas and make some improvements to it.