Workaround for Non-Unicode Parameters in Entity Framework with Oracle

I ran into a very interesting problem this week.  I got feedback that a few queries in an application I am working on are not using indexes.  The app itself is using Entity Framework over Oracle.  There was a matching index for each query, but Oracle was not using it.  I decided to use Toad to figure out the problem.  In Toad when I ran the same query it, and it was using an index.  Strange I thought.  So I ran the app and captured the queries in Toad using Database –> Monitor –> SGA Trace menu.  Under Explain Plan tab I saw that the filter expression contained SYS_OP_C2C function, which performs character type conversions between Unicode and Non-Unicode strings.  Of course I did not have an index like that.  Aha, I am getting closer.  Then I used this query to figure out variable that are used in where clause.

select * from v$sql_bind_capture where  sql_id = 'xxxxxxxxx';

You can find SQL ID in SGA Trace window.  My variable type was nvarchar, even though a column I was querying was char(36).  Now I fully understand the issue.  SQL Server provider for Entity Framework used to have the same problem, and it was fixed.  Oracle I am certain is working on the fix as well, as the issue was documented on their forums.

In the mean time I wrote a replacement function for FirstOrDefault (that we use often) that does conversion in .NET.  This will hold me over until the Oracle published the fix.  My typical code would be.

var item = context.ITEMS.FirstOrDefault(id);

In the query above ID is a variable string, not a constant.  That is what is causing the problem.  I decided to write general purpose replacement function FirstOrDefaultById that would take the same variable and figure out everything else on its own.  Since I use it a lot on primary keys, which are char(36) type – this way I have code compatibility between SQL Server and Oracle, but deep inside primary keys are GUIDs.  So, first of all I need to get primary key column for my class (EntityObject based).  This is very easy to do using EdmScalarPropertyAttribute attribute.  Here is one line of code to do this.

public static PropertyInfo GetKeyProperty<TEntity>()
    where TEntity : EntityObject
{
    return typeof(TEntity).GetProperties()
                .First(oneProp => oneProp.GetCustomAttributes(true)
                    .Any(one => one is EdmScalarPropertyAttribute && ((EdmScalarPropertyAttribute)one).EntityKeyProperty));
}

I am still using EF 1.0 style, by the way.  Of course, you can do exactly the same code with Code First and Key attribute.

The last part is FirstOrDefault substitute.   This code is nothing but reflection and Expression objects.  it is also using EntityFunctions.AsNonUnicode function to let EF provider know that the parameter is a non-Unicode parameter.  Instead of my code below you could always use

var item = context.ITEMS.FirstOrDefault(EntityFunctions.AsNonUnicode(id));

Here is more general purpose solution.  You can always modify it for generic where clause instead of FirstOrDefault.

 

private static readonly MethodInfo AsNonUnicodeMethodInfo = typeof(EntityFunctions).GetMethod("AsNonUnicode");
public static TEntity FirstOrDefaultById<TEntity>(this IQueryable<TEntity> collection, string id)
    where TEntity : EntityObject
{
    // item=> portion of FirstOrDefault(item=> item.Id == "xxxxx")
    var parameterExpression = Expression.Parameter(typeof(TEntity), "item");
    // get primary key PropertyInfo object
    var propertyInfo = GetKeyProperty<TEntity>();
    // item => item.Id portion of FirstOrDefault(item=> item.Id == "xxxxx")
    var memberExpression = Expression.MakeMemberAccess(parameterExpression, propertyInfo);
    // "xxxxx" portion of FirstOrDefault(item=> item.Id == "xxxxx")
    var constantExpression = Expression.Constant(id, typeof(string));
    // let EF know this is a non-unicode string
    var idExpression = Expression.Call(AsNonUnicodeMethodInfo, constantExpression);
    // entire where clause - item=> item.Id == "xxxxx"
    var binaryExpression = Expression.Equal(memberExpression, idExpression);
    // finally, convert where clause to lambda expression
    var lambdaExpression = Expression.Lambda<Func<TEntity, bool>>(binaryExpression, parameterExpression);
    //  add it wo FirstOrDefault statement
    return collection.FirstOrDefault(lambdaExpression);
}

I tried to document each step to make sure someone else can debug this code later.  Finally, the replacement code for FirstOrDefault call:

var item2 = context.ITEMES.FirstOrDefaultById(id2);

That is it.  This code will hold me over until Oracle team addresses the issue.

Thanks.

7 Comments

  1. I would like to mention that Oracle provider’s latest release has this issue fixed. While DevArt is an excellent product, Oracle provider is free, while DevArt is not. Just want to make sure everyone is properly informed.

  2. Hi Sergey,
    we see the same problem in Informix using DB2 and database-first implementation of IBM Data Provider for .NET.
    I am a DBA and told the developer that this is a problem with EF and not with the driver.
    You wrote in this article that the problem is fixed for SQL Server and that you think Oracle is working on a patch too.
    Your posting from October 22, 2013 informed that the issue is solved by the latest release of Oracle’s provider.

    Am I right when I expect that IBM should fix this issue in the .Net provider too.

    It would be great to hear from you.

    Many thanks in advance,

    Marion

Leave a Reply

Your email address will not be published. Required fields are marked *