Entity Framework, devArt, and Oracle VarChar params

Question

Hello: I am using devArt's Oracle Connect to work with Entity Framework 4.1 POCO's and Oracle.

I have a problem with certain queries where the Oracle column type is Char (fixed-length). The parameter that is used in the generated SQL query is formatted as a VarChar instead of a Char, and it is causing my queries to return zero rows. Is there some way to force EntityFramework/DevArt to pad the parameter?

Here's an example of the problem (querying for a username). This code should return rows, but it does not.


string aUserName = "Test";
var query = from u in users
            where u.UserName == aUserName
            select u;

If I change the first line of code to:

string aUserName = "Test".PadRight(20);

Then it works (the Oracle column is Char(20)). I would like to not have to do the padding (I don't have to with SQL Server). Is there some configuration change that I can make? A connection string switch? An Attribute on my POCO?

is there any reason why the column is CHAR? is it permissible to simply change that to a varchar2? — Harrison
Wish I could , but this is a legacy database, with tons of code touching it and multiple installations (we're an ISV). So I have to roll with the schema we've got. — JMarsch

Naor Naor · Accepted Answer · 2011-05-04T03:49:22

You have two options:
1. Do what you did and pad right the user name string variable.
2. remove empty chars - trim right the username field.

Other option:

string aUserName = "Test";
var query = users.select(x => string.Join(string.Empty, x.UserName).TrimEnd()).
                  where(x => x==aUserName)

(this will return you the user names and not the whole user objects)

Hope this helps.

Entity Framework, devArt, and Oracle VarChar params

2 Answers