We have recently just finished the final stages of testing our new product version that makes use of .NET 4, Entity Framework 4 (EF4), WCF Data Services (WCF DS), Task Parallel Library, etc. When we switched from testing the MS SQL version to the Oracle version, a few new issues arose that I’ll note in this post.
WCF DS Queries
When writing queries against WCF DS with your backend using EF4, the query against EF4 is automatically generated. This works fine if your backend store is using MS SQL, but we found that some of our queries resulted in EF4 queries that were invalid for an Oracle store (please refer to ef4 part 8: database agnostic linq to entities).
This is a pain, as you cannot simply take the ToTraceString approach we take to detect bad queries as we would do when writing linq to entities ourselves. You have to really know what to look for and avoid, and ensure you properly test the code against all store types.
WCF DS Server Side Paging
We originally had server side paging in some places, but due to us using an Expand the automatically generated queries generate a CROSS APPLY, and hence failure when targeting an Oracle store.
The solution to this was simple: use client side paging. And the streaming nature of WCF DS means that we don’t suffer from any performance hit either (well, we don’t appear to?!).
EF4 Nested Tables
Some of our EF4 queries needed some rework for an Oracle backend due to nesting…this is probably better explained with some code:
Problem Query
var students = (from s in context.PlacementGroupStudents
from sa in s.SchoolStudent.Student.StudentAddresses
where s.PlacementGroupId == placementGroupId
&& sa.AddressTypeId == (int)AddressTypes.Street
select new
{
s.PlacementGroupStudentId,
sa.Address
});
This kind of query worked fine with our MS SQL backend, but failed due to the nesting depth on Oracle. It required reworking to make use of explicit join statements:
Reworked Query
var studentQuery = (from s in context.PlacementGroupStudents
join ss in context.SchoolStudents on s.SchoolStudentId equals ss.SchoolStudentId
join sa in context.StudentAddresses on ss.StudentContactId equals sa.StudentContactId
where s.PlacementGroupId == placementGroupId
&& sa.AddressTypeId == (int)AddressTypes.Street
select new
{
s.PlacementGroupStudentId,
sa.Address
});
Oracle is SOOO Sensitive
By default, a string query on MS SQL or MySQL is case insensitive. Not so for Oracle! So we had to adjust this. We found the following excellent article: cAsE sEnSiTiVe Oracle.
You can make these settings at various different levels, i.e. Server, Instance, Database or Session. Instance or Database would seem to be the most appropriate?!
Oracle thinks an Empty String is a Null?!
It would seem that for historical reasons (we’ve heard that before!) Oracle treats empty strings in varchar columns as null. This introduced a new issue for us where we had string fields that were non-nullable in our model; they worked fine using an MS SQL store, but died horribly when using Oracle. Our approach to fix was two-fold:
1. When saving an entity that had an empty string, Oracle would translate this to a null, and then die during the insert/update as the column was not defined as nullable. Our fix was to modify the actual Oracle schema to make such columns nullable, BUT we left the EF4 model and store definition files as they were (i.e. non-nullable).
2. When reading an entity back, we needed to get these null strings back to being empty strings. Devart provide such a flag for just this purpose:
Devart.Data.Oracle.Entity.OracleEntityProviderServices.HandleNullStringsAsEmptyStrings = true;
The result is our strings now get both saved and retrieved as if the backend store was MS SQL.
Update 5th February 2011
Further testing and research would seem to indicate that it is actually best to make the case sensitivity settings at session level due to the way that Oracle settings at the different levels seem to propagate and override each other; it seems to be the only way that we can ‘guarantee’ getting the expected results.
Additionally, you may well want to consider adjusting your Oracle indexing (i.e. adding more) to ensure performance is retained when making the aforementioned changes.
Finally, we also found that when we obtained the database DateTime (we prefer to do this to use for setting the created and updated DateTime fields rather than DateTime.Now) as follows:
EF4 Query to get DB DateTime
DateTime dateUpdated = CreateQuery<DateTime>("CurrentDateTime()").Execute(MergeOption.NoTracking).First();
That when the backing store was Oracle we got a UTC DateTime whilst MS SQL was giving us the value for the locale. This is rather annoying as we are deliberately executing a ‘generic’ query, but the translation and ultimately result differ…lame…
The solution was to ensure the Oracle session time zone is set to Local:
ExecuteStoreCommand("ALTER SESSION SET TIME_ZONE = LOCAL");
Print | posted on Friday, 28 January 2011 2:20 PM