Error retrieving database generated values on inserts
Posted: 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/#S ... lection.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:
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:
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.
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/#S ... lection.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 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"It would be greatly appreciated if this issue could be addressed.
I have a small program that shows this issue in action if needed.