NULL and empty strings in Oracle

NULL and empty strings in Oracle

Postby prodok » Thu 26 Mar 2009 12:22

Hi,

we use dotConnect for Oracle with entity framework. Everything works fine but there is one problem:

We would like to get strings that are null in the database as empty strings in the entity objects.

Is there a way to tell dotConnect for Oracle to convert null strings to an empty string when reading the data ?

Thanks in advance

Oliver
prodok
 
Posts: 4
Joined: Thu 26 Mar 2009 12:12

Postby AndreyR » Fri 27 Mar 2009 12:41

If you don't use Devart Entity Developer, the only way is to change code manually or write your own code generator.
If you use Entity Developer 2.0 Beta, then you can influence the code generation using templates.
You can find the template file for property in %Program Files%\Devart\dotConnect\EntityDeveloper\Templates\EntityFramework\CS\Property.tmpl.
Replace these lines:
Code: Select all
get
            {
                [%currentPropertyType%] value = this._[%Name%];
                OnGet[%Name%](ref value);
                return value;
            }

with the following lines:
Code: Select all
get
           {
                [%currentPropertyType%] value = this._[%Name%];[%
If [Equals [%[%Type%]%] "String"] [%
                if (value == null)
                  value = String.Empty;%]%]
                OnGet[%Name%](ref value);
                return value;
            }

As an alternative, you can implement the OnGetXXXX(ref value) partial method only for the properties that need such an implementation.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby prodok » Mon 18 May 2009 17:21

First: Thanks for the suggestion and sorry for the late response.

We tried that and at the beginning it looked like it would be the solution. But now we have recognized that the code in the generated entity class is not always executed so that it works only sometimes.

When this query is executed, the code in the entity class is executed
Code: Select all
var query = from c in context.Projects
            orderby c.ProjectName
            select c;


When this query is executed, the code in the entity class is not executed
Code: Select all
var query = from c in context.Projects
            orderby c.ProjectName
            select new { c.ID, c.ProjectName, c.Remark };


I guess that the generated class is only used when a complete entity is needed like in the first case and otherwise the framework does it in a different way.

Do you have an idea what we could do?

Thanks in advance

Oliver
prodok
 
Posts: 4
Joined: Thu 26 Mar 2009 12:12

Postby AndreyR » Tue 19 May 2009 09:58

One of the possible solutions is to create a fake entity class (containing necessary properties only) in the model
and executing the select of this class instance.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby prodok » Tue 19 May 2009 15:34

That's no real option for us. We have 110 Tables each having about 100 or 200 fields.

One of the reasons to do a "select new { c.ID, c.ProjectName, c.Remark };" is to read only the data that is needed for the current operation. That can be nearly any combination of the fields in the table.

The three fields in the "select new" i wrote were only an example to point out the problem.

Something where we can hook in would really help, not only for the "null problem"

Or, if we could configure the provider to convert nulls to String.Empty would solve the main problem.
prodok
 
Posts: 4
Joined: Thu 26 Mar 2009 12:12

Postby Alladin » Tue 19 May 2009 18:09

Or you can use

field ?? ""

coalesce operator
Alladin
 
Posts: 149
Joined: Mon 27 Nov 2006 16:18

Postby prodok » Wed 20 May 2009 06:48

Not when binding the result to a list or something else.
prodok
 
Posts: 4
Joined: Thu 26 Mar 2009 12:12

Postby AndreyR » Wed 20 May 2009 14:33

There is one more proposal.
Try setting Delay Loaded to true for all properties you are not going to use in the query.
Then you will be able to query the whole entity and the methods will fire, and the properties, which you don't need to be loaded from db,
will not be queried until they are explicitly called in the code.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby LordFjord » Wed 18 Aug 2010 13:49

Hi,
I found this thread after some searching, but i havent found any good solution or workaround for the oracle - null - string.Empty issue.

In the oracle world, an empty string is the same as null, and it cannot be inserted into not nullable columns. (for whatever reasons)

It might be easy to tackle this issue if you only use oracle databases, however with a mixed environment of ms sql server and oracle things get messy. I am porting a ms sql database to oracle, so i cant simply change colums to be nullable on the oracle side.

In addition to this, at some points it is indeed important to distuingish between an empty and a not-existing value.

What I am testing right now is to replace empty strings with a " " (blank).
I have edited the template for my entity developer model to add methods to all non-nullable string properties that handle the conversion of string empty to " " back and forth.

This looked promising in the beginning, but it failed on more complex LINQ queries. I guess the properties are not used while processing those?

Small example:
context is my datacontext to access the DB. MyProperty1 of the object MyObject has a method added to its get/set that handles the string.empty->" " conversion.
Code: Select all
MyObject obj = context.MyObjects.SingleOrDefault(x => x.MyProperty1 == myProp1);

The Property is not accessed on this call, the value is not converted and the query is returning null even if the data exists in the database.

Similar to this, when inserting data, I'm getting the ORA-01400 Cannot insert NULL... errors.

So far the only working (and very very tedious and error-prone) workaround I found is to manually check before each DB access if the values are ok. As you can imagine with a lot of table this is nearly not maintainable.

Has anyone found a generic approach to handle this issue? Any help would be appretiated.
LordFjord
 
Posts: 18
Joined: Thu 22 Apr 2010 07:55

Postby AndreyR » Thu 19 Aug 2010 12:22

Unfortunately, this is an Oracle limitation.
There is no convenient workaround. You can try to modify both getter and setter, or use some special value like "" instead of sending String.Empty to Oracle.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16


Return to dotConnect for Oracle