on 2012 Jan 27 5:54 AM
Hello,
I am using the SQL Anywhere ADO.NET Provider to connect to a SQL Anywhere Database using .NET Entity Framework 4.2 and I encountered following problem:
I want to make use of the optimistic concurrency check features of EF (i.e. I define a row version column on the entities for which I want to check concurrent changes, and in the EDM I set this column to ConcurrencyMode=Fixed). When I save changes to an entity object the entity framework includes the rowversion in the where clause of the update statement (with the value of rowversion, that was retrieved, when loading the entity object). So the update return zero affected rows if the rowversion changed due to an concurrent change of the entity object. This works very well.
But if I have a parent-child association of two or more entities, and I make changes to a child entity object, then the entity framework concurrency check functionality also checks, if the parent entity has changed. To do this a fake update statement is issued to the parent table before the changes of the child entity is persisted.
This fake update uses a locally declared variable in the set-clause of the update statement (so no actual fields of the parent table are changed), and uses the rowversion of the parent entity in the where-clause. And again the affected rows are checked to determine, if there were any changes to the parent entity.
My problem now is: the fake update statement to the parent entities table causes a syntax error thrown by SQL Anywhere.
Here is the fake update statement (i took it from the request-log):
=,<,28,PREPARE,update [PARENT_TABLE] set :p0 = 0 where (([ID] = :p1) and ([ROW_VERSION] = :p2)); select [ROW_VERSION] from [PARENT_TABLE] where @@ROWCOUNT > 0 +1,E,28,-131,Syntaxfehler bei 'p0' in Zeile 2
The problem seems to be, that the statement is trying to set the hostvariable ":p0" (which is the aformentioned fake update), but that kind of syntax is obviously not supported by the sql anywhere.
So the question is: Is that a bug in the ADO.NET Provider of SA or am I doing something wrong here?
Thanks and Greetings, Andre Hentschel
Request clarification before answering.
So I didn't try out the MS hotfix, because it doesn't seem to apply to my problem.
You should at least try EF 4.5 out since there were subtle
aspects (not all of which I could follow) about when this
crops up in practice ...
... and the fix (one that is completely undocumented AFAIK)
may be more complete and the framework more stable/functional
with it.
I indeed want the "fake-update" to be issued, and my problem is just that the syntax of that update causes the Sql Anywhere Server to throw an error.
The fake update is illegal SQL on any platform it is generated (including the Microsoft ones) and the fix in EF 4.5 addresses that. That also means the bug was in the EF 4.0 source code and not in the SQL Anywhere ADO.Net driver/provider.
And finally, in my best estimation, an update was not required since there is no requirement to have the ROW or the contained ROW_VERSION value change for the SELECT to pull back the current value; when such a column is present. {This too was also mentioned on the thread ...}
WARNING: Caveat, this bug and fix and descriptions of it
are in the Microsoft domain and I am only connecting dots
to their expertise and software technology where it seems
to be applicable.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
It is good to know that this issue was resolved in .NET 4.5. Unfortunately, I cannot benefit from this, since I am still developing for .NET 4.0, and this will not change any time soon. It's a pity that MS seems to abandon the development (even bug-fixing) for the EF in .NET 4.0, but that's another story ...
Anyways, if the generation of this "fake-update" is not a provider-specific thing, but part of the EF, and obviously not even a desired behavior, my original question is answered. Thanks for that.
I have no idea which part of the query generation is provider independent and which isn't, so I think this was still a legitimate question (at the time I posted it I thought all query generation is implemented by the DBMS specific data provider).
And as a final thought: even if the automatic row-version-check on a parent entity was not an intended feature, I would find it quite useful in certain situations
Another reference I found indicates that this is resolved in the 4.5 Framework ... which is available for download from http://www.microsoft.com/en-us/download/details.aspx?id=30653 and comes with Windows 8 and Server 2012.
I just downloaded that and tried it ....
You might want to check it out if this is also an issue for you.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
There have been a number of viewers on this one but no answers.
I do suspect others may have been affected by this since I can find other hits on this 'fake' update statement causing issues for MS SQL Server and other database technologies as well in this scenario.
Here are a couple of crumbs I've found out related to a support issue just opened up on this.
One thread I found that indicates this is an issue of Entity Framework 4.x
http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/4ec6844f-3a12-43c0-8a2...
and I suspect that is the one that lead MS to release this Hotfix
http://support.microsoft.com/kb/2390624
Unfortunately to gain access to that HotFix requires one to contact MS Support to access the download.
Has anyone had a chance to try this HotFix out yet?
Do Please let us know thanks!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I read through the MSDN forum thread you posted, and it seems to me, that the thread-starter wants to completely avoid the "fake-update" to the parent-entity, while the update itself seems to make no trouble (i.e. no command execution error is mentioned).
On the contrary, I indeed want the "fake-update" to be issued, and my problem is just that the syntax of that update causes the Sql Anywhere Server to throw an error.
So I didn't try out the MS hotfix, because it doesn't seem to apply to my problem.
User | Count |
---|---|
74 | |
30 | |
9 | |
7 | |
7 | |
6 | |
6 | |
5 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.