Postgres - attributes inserted in wrong order?

[Ondrej]

Hello Telerik,
when evaluating ORM, I found this issue:
I define diagram with four entitites, three of them pointing to one, I create the DB with ORM. Then, when inserting the object referencint to three other types of objects, I get exception complaining about foreign key constraints. When removing the constraints, the values are entered into wrong columns:(
This code should illustrate it:
      EntitiesModel model = new EntitiesModel();
      Industry industry1 = new Industry() { Name = "Industry 1" };
      ProjectType projectType1 = new ProjectType() { Name = "Project Type 1" };
      ProjectType projectType2 = new ProjectType() { Name = "Project Type 2" };
      Customer customer1 = new Customer() { Name = "Customer 1" }; 
      Customer customer2 = new Customer() { Name = "Customer 2" };
      Customer customer3 = new Customer() { Name = "Customer 3" };
      model.Add(industry1);
      model.Add(projectType1);
      model.Add(projectType2);
      model.Add(customer1);
      model.Add(customer2);
      model.Add(customer3);
      model.SaveChanges();
      Project project = new Project() { Name = "Test Project", Industry = industry1, ProjectType = projectType2, Customer = customer3 };
      model.Add(project);
      model.SaveChanges();//THIS IS WHERE PROBLEM OCCURS
      model.Delete(industry1);
      model.Delete(projectType1);
      model.Delete(projectType2);
      model.Delete(customer1);
      model.Delete(customer2);
      model.Delete(customer3);
      model.SaveChanges();
I have created an example project illustrating this issue and can send it.
Please help, this seems to be a serious issue.
Thank you
Best Regards
Ondrej

[Ondrej]

Hello,
more information on this issue: This behavior occurs, when the DB table Identity Mechanism is set to DatabaseServerCalculated, otherwise not. IMHO this is a serious issue.
BR
Ondrej

[Ady]

Hi Ondrej,

 I created 3 classes (ProjectType,Industry,Customer) which are referenced by a single class (Project) . All classes use 'DatabaseServerCalculated' identity mechanism. I was successfully able to store instances of 'Project' with references set to all 3 classes.
Can you provide me with the exact exception message that you get? If you have prepared a sample application where the exception can be reproduced, can you please send that? This would help in resolving the issue sooner.

Looking forward to assisting you further.

Kind regards,
Ady
the Telerik team

[Ondrej]

Hello Ady,
here is the exception message:
<code>
{"Insert of '1893964241-' failed: Telerik.OpenAccess.RT.sql.SQLException: ERROR: 23503: insert or update on table \"project\" violates foreign key constraint \"ref_project_industry\"\r\n   at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.execute()\r\n   at OpenAccessRuntime.Relational.conn.PooledPreparedStatement.execute()\r\n   at OpenAccessRuntime.Relational.RelationalStorageManager.generateInserts(NewObjectOID oid, Int32 index, ClassMetaData cmd, PersistGraph graph, Int32[] fieldNos, CharBuf s, Object[] oidData, IntArray toUpdateIndexes)\nINSERT INTO \"project\" (\"id4\", \"id2\", \"nme\", \"id3\") VALUES (?, ?, ?, ?)\n(set event logging to all to see parameter values) Telerik.OpenAccess.RT.sql.SQLException: ERROR: 23503: insert or update on table \"project\" violates foreign key constraint \"ref_project_industry\"\r\n   at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.execute()\r\n   at OpenAccessRuntime.Relational.conn.PooledPreparedStatement.execute()\r\n   at OpenAccessRuntime.Relational.RelationalStorageManager.generateInserts(NewObjectOID oid, Int32 index, ClassMetaData cmd, PersistGraph graph, Int32[] fieldNos, CharBuf s, Object[] oidData, IntArray toUpdateIndexes)"}
</code>

Here is the demo project uploaded (just download TelerikTest.zip on the page): http://files.uloziste.com/101c914cb52fea1d/
Please just update the database from model.

Important: I have just discovered, that changing the primary key generation of the Project to HighLow solves the problem (but you have to have voa_keygen table in your database and therefore use other mechanism). But what is really tricky: changing the behavior back to DatabaseServerCalculated causes the mechanism to behave correctly:)
So I had to delete the model file and create the model again and the bug is really still there. 
BR
Ondrej

[Ondrej]

Hello,
after further observations, not even changing the Identity Mechanism helps. Foreign keys are stored into wrong fields - it's somehow mixed. This makes use of relations together with postgres impossible for me.
BR
Ondrej

[Ady]

Hello Ondrej,

 Thank you for the sample project. I was able to reproduce the error. The problem occurs when using the 'Model First' approach. I'm afraid I can't provide you a workaround for the problem right now. We will fix this problem as soon as possible and notify you accordingly.

Your Telerik points have been updated.

Regards,
Ady
the Telerik team

[Ady]

Hello Ondrej,

 I did look into the problem further and could manage to provide a workaround after all. I have explicitly specified the mapping for the Foreign key columns for all reference fields in the 'Project' class. Additionally I have added int properties for each reference field that share the same underlying FK column. I am attaching the modified project.
Hope this helps.

Greetings,
Ady
the Telerik team