Empty strings in the database are null on the entities

Posts   
 
    
p74dan
User
Posts: 5
Joined: 21-Aug-2019
# Posted on: 21-Aug-2019 12:57:32   

LLBLGen 5.4.3

I have a column in my database, which is an nvarchar(MAX) called DerivedFrom. It's NOT NULL and therefor never has any null values.

When I fetch my entity, the DerivedFrom property is NULL. I'd expect it to be an empty string. Why is this?

Basically it's causing me an issue with some automated property mapping I'm doing, because the mapping code thinks that the value has changed vs the source data. So the source data has an empty string, but the entity has a null. It sets the value back to string.Empty and sees it as a change. Any changes detected have a further set of processing on them, so it's causing a bit of an issue.

For the time being I've just checked if the target property is a string and made my code set the value anyway and ignore it as a change. If I don't set the property, then any new entities fail to save, because I've not set a value to the property so LLBLGen doesn't set a value so it throws an exception as you'd expect:

Cannot insert the value NULL into column 'DerivedFrom', table 'RP2.15.dbo.RP_SpecificationComposite'; column does not allow nulls. INSERT fails. The statement has been terminated.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 22-Aug-2019 02:01:45   

I can't reproduce it. An empty string in a SQL database always returns string.Empty.

p74dan
User
Posts: 5
Joined: 21-Aug-2019
# Posted on: 22-Aug-2019 09:26:09   

Interesting! Well, it certainly is for this particular property for us. I double checked the designer and it's definitely not marked as optional. The field is definitely NOT NULL, yet the field is definitely coming out as null. I've had to change our domain construction to be:

ourDomainObj.DerivedFrom = entity.DerivedFrom ?? string.Empty;

I'm guessing I'd be best off making a repro from this with only the offending table and a console app. I should have time on Friday, so will post again then or sooner if I get some free time at the end of the sprint.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 22-Aug-2019 09:44:05   

If the field isn't optional (so it can't have nulls) that doesn't mean a value automatically is inserted when you insert a row. You need a default constraint on the table field which will provide the value that's inserted when a NULL (or no value) is inserted in the field.

It's odd though, as our framework has a facility to provide default values for fields which aren't optional but turn out to be null in the DB (which can happen if you materialize the entity using a projection for instance). So even if the value from the DB is null, the field should have returned an empty string (this is configurable however in the project settings so it might be this has been switched off).

A small repro would be good indeed.

Just for completeness: you're sure you're connecting to the right database? The name of the catalog in the project is in the query, so you might be reading from the wrong catalog

Frans Bouma | Lead developer LLBLGen Pro
p74dan
User
Posts: 5
Joined: 21-Aug-2019
# Posted on: 22-Aug-2019 09:49:57   

Otis wrote:

If the field isn't optional (so it can't have nulls) that doesn't mean a value automatically is inserted when you insert a row. You need a default constraint on the table field which will provide the value that's inserted when a NULL (or no value) is inserted in the field.

It's odd though, as our framework has a facility to provide default values for fields which aren't optional but turn out to be null in the DB (which can happen if you materialize the entity using a projection for instance). So even if the value from the DB is null, the field should have returned an empty string (this is configurable however in the project settings so it might be this has been switched off).

A small repro would be good indeed.

Just for completeness: you're sure you're connecting to the right database? The name of the catalog in the project is in the query, so you might be reading from the wrong catalog

Hey, the field is definitely NOT NULL and we don't have any default values setup on the column. We are definitely inserting the row with a string.Empty (otherwise LLBLGen throws an exception saying that it 'cannot insert null into column DerivedFrom' as per the initial post. It's definitely an empty string in the database. However, when the value comes out of the NOT NULL nvarchar(MAX) column, it's 100% truly honestly NULL.

I will get a repro together as soon as possible.

And yes, I'm definitely connecting to the correct database. smile

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 22-Aug-2019 10:40:22   

I'd add a default constraint to the column, so '' gets inserted when the value is null. Saves you some trouble when you forget to set the default value... simple_smile

Frans Bouma | Lead developer LLBLGen Pro