Preserve empty String from Database

Question

I use the entity framework 4.3 in my ASP.NET MVC Application and a NOT NULL column in my database table can contain five blanks. When I retrieve the data from the database the mapped property in my entity is always String.Empty and does not contains the five blanks. How can i configure the entity framework to preserve the blanks?

The situation you describe seems odd. If you have 5 blanks in your database, you should get 5 blanks from the database. — Silvermind, Dec 10 '13 at 16:16
The property in the mapped entity is an string. The database field is an _CHAR(5 BYTE)_. The database is Oracle. — soema, Dec 10 '13 at 16:17
@Silvermind - yes, seems odd to me, too. Even if I just query the database with an plain old sql statement (via _Context.SqlQuery_) the blanks are not preserved ... — soema, Dec 10 '13 at 16:23
But they are there when you query the database directly in (I don't know the name, but) the management tool for oracle? In other words, are you sure it is the `EntityFranework`? — Silvermind, Dec 10 '13 at 16:52
Yes, I am very sure. The blanks exists in database and it must be an Entity-Framework issue. Maybe it's a problem with the Code-Generation-Template ...? — soema, Dec 10 '13 at 16:57
In MSSQL/Transact SQL the same behaviour is achieved with [SET ANSI_PADDING OFF](http://msdn.microsoft.com/en-us/library/ms187403.aspx) I do not know how this can be changed in Oracle, but perhaps you can find out more about it yourself. — Silvermind, Dec 12 '13 at 11:45

score 0 · Answer 1 · answered Dec 10 '13 at 16:07

0

I think try to compare your property's value with the explicit empty string. Ex: myProperty == ""

answered Dec 10 '13 at 16:07

Jack

329
2
7

Hm, I use the Database-First Approach and I cannot check the value of the property when it´s set from the ef - or what do you mean exactly? – soema Dec 10 '13 at 16:14

score 0 · Answer 2 · edited May 23 '17 at 11:49

I not sure that your problem is entirely with entity framework. I think the database may be treating a char(5) field that contains only empty space in ways that you might not expect.

Try this test. I ran mine in Sql Server but you should be able to do the equivalent in Oracle:

CREATE TABLE dbo.Table_1
    (
    ID int NOT NULL IDENTITY (1, 1),
    Name char(5) NOT NULL
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.Table_1 ADD CONSTRAINT
    PK_Table_1 PRIMARY KEY CLUSTERED 
    (
    ID
    )

GO

DELETE FROM table_1

INSERT dbo.table_1 (name)
    VALUES ('11111')

INSERT dbo.table_1 (name)
    VALUES ('     ')

INSERT dbo.table_1 (name)
    VALUES ('2    ')

UPDATE dbo.table_1
SET name = REPLACE(name, '1', ' ')

SELECT
    LEN(name)
FROM dbo.table_1 -- 0, 0, 1 - you might have expected 5, 5, 5

SELECT
    COUNT(*)
FROM dbo.table_1
WHERE name = '    ' -- 2 as you might expect...but


SELECT
    COUNT(*)
FROM dbo.table_1
WHERE name = ''    --  2! you might not expect that 
                   --  you'd get the same result with 
                   --  any string containing only whitespace

Reference

LEN function not including trailing spaces in SQL Server

I checked it and it works like I expect it to work. The `Length(name)` return 5 even if the value of that column is `' '`. So there is a big difference between SQL Server and Oracle. — soema, Dec 11 '13 at 11:15

score 0 · Accepted Answer · answered Dec 12 '13 at 11:29

I found a workaround, now. It's not perfect but for the moment quite OK. I set the property in my *.edmx-File to private setter and getter an add new Property in the partial-Declaration of the class, there I control the value. If is set with an empty string '' it must be a string with five blanks (because of the fixed length) and I can handle it.

    private string blankValue; // From *.edmx-File 
    public string BlankValue
    {
        get
        {
            return string.Equals(this.blankValue, "") ? "     " : this.blankValue;
        }
        set
        {
            if (value == "")
                this.blankValue= "     ";
            else
                this.blankValue= value;
        }
    }

Preserve empty String from Database

3 Answers3