Error retrieving database generated values on inserts

Error retrieving database generated values on inserts

Postby nsidawy » Wed 14 May 2014 16:50

I have been hitting an issue with DotConnect for PostgreSql's EF integration when inserting a new row into the database to a table with a column that has database generated value (like a serial column or default value) with "PgSqlEntityProviderConfig.Instance.Workarounds.DisableQuoting = true;".

Whenever the code attempts to insert a row into the database, it is hitting an error trying to retrieve any database generated values when it tries to update the entity object. The error is being hit within System.Data.Metadata.Edm.MemberCollection (http://referencesource.microsoft.com/#System.Data.Entity/DataEntity/System/Data/Metadata/Edm/MemberCollection.cs) but is directly related to the effects of the workaround mentioned above. The insert query generated by DotConnect (with that workaround set to true) looks like the following:

Code: Select all
/*Note: the "Data" and "Id" columns are not quoted and are capitalized the same way the properties are in the entity object i.e. standard c# Pascal casing (http://c2.com/cgi/wiki?PascalCase)*/
INSERT INTO public.identitytest(Data)
VALUES ('New Data')
RETURNING Id


When the entity framework attempts to pull the value of the serial column "Id", it fails to find it in the result set of the query because even though the query references "Id" it gets returned as "id" (because it is unquoted) and the MemberCollection method "this[string identity]" is case sensitive (there is no option to change this, unfortunately).

The current workaround for this issue is to just rename all database generated entity columns to all lowercase, however this renaming is what we are trying to avoid by using "DisableQuoting" in the first place.

I believe the issue can be resolved by modifying the code that generates the returning statement to add an 'AS "[PROPERTY_NAME]"' after each returned value. So the SQL above would be changed to:

Code: Select all
INSERT INTO public.identitytest(Data)
VALUES ('New Data')
RETURNING Id AS "Id"


where the PROPERTY_NAME is how the column is named in the entity framework model. Perhaps this should be default behavior for the DisableQuoting = true option or it should be its own workaround configuration option.

It would be greatly appreciated if this issue could be addressed.

I have a small program that shows this issue in action if needed.
nsidawy
 
Posts: 6
Joined: Thu 08 May 2014 21:12

Re: Error retrieving database generated values on inserts

Postby MariiaI » Fri 16 May 2014 09:40

Thank you for the report on this. We will investigate this issue in more details and inform you about the results as soon as possible.

I have a small program that shows this issue in action if needed.
You can send it us via our contact form .
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Error retrieving database generated values on inserts

Postby nsidawy » Tue 20 May 2014 14:37

I submitted an request on that page last Wednesday (May 14) around 5PM. The program is attached as a ZIP.

The title of that request was the same as this forum post's title.
nsidawy
 
Posts: 6
Joined: Thu 08 May 2014 21:12

Re: Error retrieving database generated values on inserts

Postby MariiaI » Wed 21 May 2014 07:36

Thank you for the additional information. We have fixed the issue related to the "column "Id" does not exist" error on inserts for columns with the database generated values when using Workarounds.DisableQuoting = true. We will inform you when the corresponding build of dotConnect for PostgreSQL is available for download.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Error retrieving database generated values on inserts

Postby nsidawy » Wed 21 May 2014 15:32

Thanks for the fast turnaround!
nsidawy
 
Posts: 6
Joined: Thu 08 May 2014 21:12

Re: Error retrieving database generated values on inserts

Postby nsidawy » Thu 22 May 2014 17:52

When do you think the next release will come out? We have just purchased a site license and we are eager to test out this fix.

Thanks!
nsidawy
 
Posts: 6
Joined: Thu 08 May 2014 21:12

Re: Error retrieving database generated values on inserts

Postby MariiaI » Mon 26 May 2014 05:07

We plan to release the new build of dotConnect for PostgreSQL this week. We will definitely inform you when it is available for download.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Error retrieving database generated values on inserts

Postby MariiaI » Fri 30 May 2014 05:25

New build of dotConnect for PostgreSQL 7.3.171 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/postgresql/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?f=3&t=29685.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17


Return to dotConnect for PostgreSQL