NULL and empty strings in Oracle

[prodok]

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

[AndreyR]

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.

[prodok]

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

[AndreyR]

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.

[prodok]

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.

[Alladin]

Or you can use

field ?? ""

coalesce operator

[prodok]

Not when binding the result to a list or something else.

[AndreyR]

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.

[LordFjord]

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.

[AndreyR]

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.