1

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?

JMarsch
  • 21,484
  • 15
  • 77
  • 125
  • is there any reason why the column is CHAR? is it permissible to simply change that to a varchar2? – Harrison May 03 '11 at 17:04
  • 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 May 03 '11 at 17:20

2 Answers2

2

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.

Naor
  • 23,465
  • 48
  • 152
  • 268
  • 1
    I marked yours as answer because the Padright ended up being the best solution for my situation. (I have to support both SQL Server and Oracle).I did find one other solution that would benefit Oracle-only development: You can apply the [Column] attribute to the columns and pass "CHAR" as the typename named parameter. That will make everything work in Oracle, but you will get a runtime exception with SQL Server. – JMarsch May 04 '11 at 15:41
1

We have replied to you here at our forum.
Please inform us if you encounter any problems with the provided solution.

Devart
  • 119,203
  • 23
  • 166
  • 186