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

asked 27 Jan '12, 05:54

Andre%20Hentschel's gravatar image

Andre Hentschel
1917717
accept rate: 0%

edited 15 Mar '13, 19:56

Mark%20Culp's gravatar image

Mark Culp
24.9k10139297


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.

permanent link

answered 25 Mar '13, 11:30

Nick%20Elson%20SAP%20SQL%20Anywhere's gravatar image

Nick Elson S...
7.3k35107
accept rate: 32%

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

(25 Mar '13, 12:12) Andre Hentschel

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 ....

      • And it resolved the issue seen in the test case my customer supplied! * * *

You might want to check it out if this is also an issue for you.

permanent link

answered 04 Feb '13, 21:04

Nick%20Elson%20SAP%20SQL%20Anywhere's gravatar image

Nick Elson S...
7.3k35107
accept rate: 32%

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-8a2c-84edb3d08c62 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!

permanent link

answered 04 Feb '13, 20:47

Nick%20Elson%20SAP%20SQL%20Anywhere's gravatar image

Nick Elson S...
7.3k35107
accept rate: 32%

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.

(25 Mar '13, 10:12) Andre Hentschel
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×438
×69
×39

question asked: 27 Jan '12, 05:54

question was seen: 5,574 times

last updated: 25 Mar '13, 12:12