0

I have a problem regarding concepts compatibility between business and data layer, with respect to entities / tables relations and foreign keys enforced on the database layer.

I am using CodeFluent MySql Producer, but I believe the behavior is the same on other Db producers as well.

The question involves one to many simple relationships between entities, which are mapped out to the db as INT datatype fields, with proper foreign keys enforced.

The issue I have is: on the business layer in .NET, the "linking" property for an entity is mapped as Integer type, with default value of -1 (minus one).

This means that, when the information for the linking of the two entities is NON-EXISTENT (not required), the business layer takes into account -1 as the content for the linking property, and this content -1 is also persisted to the database field on the correspondent table.

There lies the problem I need to understand. When I have -1 as content in the database, the associated foreign key is irrelevant / not compliant. This should be set to NULL on the database side, instead of -1. There is no -1 content as primary key in any row to the OTHER target table for the relationship.

One practical difficulty I have is: I can't user data synchronization scripts between databases that were populated with codefluent generated applications, because upon dropping the foreign keys at the beginning of a data synch script, I cannot re-create them later because of all the -1s (minus ones) in place of NULL that are present as a rule for non-existing information in fields representing relationship between 2 tables...

Can you help me understand and solve this issue?

Thanks

Renato Xavier
  • 123
  • 1
  • 9
  • 1
    `-1` is the default value of the property so it should be persisted as `NULL`. I think the following answer may help you to understand how `CodeFluent Entities` deals with default values: http://stackoverflow.com/a/35790190/2996339 – meziantou May 24 '16 at 22:15
  • I understand @meziantou, but in my case the default value of the property is NOT being persisted as NULL. It's being persisted as -1. – Renato Xavier Jun 02 '16 at 14:21
  • Upon reading the link you mentioned, I even tried to change this: into this: But the result is the same on MySql producer: persisted as -1. – Renato Xavier Jun 02 '16 at 14:21
  • The `BaseSave` method of the generated class should contain something like `persistence.AddParameter("@Id", Id, -1);`. The `-1` means if `Id` equals `-1`, send `NULL` to the database. Here's the property declaration `` – meziantou Jun 03 '16 at 09:03
  • The BaseSave method for the generated class for my example is such as: persistence.AddParameter("@oProdutoDepartamento", Me.oProdutoDepartamentopId, CType(-1,Integer)). It checks with your statement – Renato Xavier Jun 03 '16 at 12:34
  • The underlying query captured in the database log that is issued for the .Save method on the BOM is: CALL `mydatabase`.`produto_Save`(-1, 3, 'test', '12345678', 'S', -1, -1, -1, -1, -1, 0, '0001-01-01 00:00:00 ', NULL, 1, NULL, 'MY-LAPTOP\\USER', NULL). Please note that the oProdutoDepartamento parameter is the 6th parameter for this call, which we can see is being passed as -1 to the Entity_Save procedure in the database. – Renato Xavier Jun 03 '16 at 12:37
  • Upon examining the entity_Save procedure, I can see that there are no special treatment regarding incoming parameters to imply that -1 should be transformed into NULL. The procedure simply takes the incoming parameter with the declaration generated as `p_oProdutoDepartamento` INT (11), then uses this parameter in an INSERT INTO clause, on the VALUES list... This way, I get a -1 on this database field when persisted, where a NULL is expected. Can you please clarify what is going on? – Renato Xavier Jun 03 '16 at 12:39
  • Any further comments on this issue? @meziantou ? – Renato Xavier Jun 15 '16 at 17:11

1 Answers1

1

As you are using MySql, you have to add an attribute in the configuration file (app.config or web.config) to convert default value to NULL:

<configSections>
  <section name="Sample" type="CodeFluent.Runtime.CodeFluentConfigurationSectionHandler, CodeFluent.Runtime" />
</configSections>
<Sample persistenceTypeName="MySQL"
        connectionString="..."
        mysql-useDefaultValue="true" />
meziantou
  • 20,589
  • 7
  • 64
  • 83