I have simple table in Sybase -- Creating table 'SimpleText' CREATE TABLE [dbo].[SimpleText] ( [Id] nvarchar(10) NOT NULL, [SimpleValue] nvarchar(120) NULL ); GO -- Creating primary key on [Id] in table 'SimpleText' ALTER TABLE [dbo].[SimpleText] ADD CONSTRAINT [PK_SimpleText] PRIMARY KEY CLUSTERED ([Id] ASC); GO Based on this table it is created an model in entity framework (using 4.3). Insert and delete is not a problem. However when updating the table it generates an error when primary key is '[char][number]' i.e. a1 where it says that column a1 it not found. By using a simple trace tool this is the sql generated : update [dbo].[SimpleText] set [SimpleValue] = :p0 where ([Id] = :p1) and (Id = a1) p0 = simpleTextValueUpdateda1 p1 = a1 Update code is as follow in C#: using (var model = new Entities()) { var entString = model.SimpleText.Select(t => t); foreach (var simpleText in entString) { simpleText.SimpleValue = "simpleTextValueUpdated" + simpleText.Id; } model.SaveChanges(); } There's not an issue if the same key is only a number. Using SqlAnywhere12, Microsoft EF4.3, C# on .NET 4 Has anyone experienced the same? Is there a fix to ensure that text is apostrophed correctly in the sql? Update: Running the same project on another computer, everything works fine. The code is identical.
So tracing the update it shows a quite important difference.
Why is the additional Computer A - works like a charm (trace output) update [dba].[SimpleText] set [SimpleValue] = :p0 where ([Id] = :p1) p0 = simpleTextValueUpdateda-1 p1 = a-1 Computer B - additional update [dba].[SimpleText] set [SimpleValue] = :p0 where ([Id] = :p1) and (Id = a-1) p0 = simpleTextValueUpdateda-1 p1 = a-1 Computer A: SQL Anywhere 12.01.3152 Computer B: SQL Anywhere 12.01.3726 I don't think the difference in SQL Anywhere is causing this, but somehow the generated SQL is different. UPDATE Update to EBF 3726 on Computer A is causing an exception on .SaveChanges(). InnerException: iAnywhere.Data.SQLAnywhere.SAException Message=Column 'a' not found Source=SQL Anywhere .NET Data Provider ErrorCode=-2147467259 NativeError=-143 StackTrace: at iAnywhere.Data.SQLAnywhere.SACommand.ExecuteNonQuery() at System.Data.Mapping.Update.Internal.DynamicUpdateCommand.Execute(UpdateTranslator translator, EntityConnection connection, Dictionary`2 identifierValues, List`1 generatedValues) at System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter) InnerException: |
This problem was introduced in build 3719. It is fixed with QTS 707922 (12.0.1 build 3732). Thanks to Minghai of the interfaces team for the info. Problem was solved by EBF 3742. Thank you, Glenn
(18 Jun '12, 02:11)
OBR
|
I assume Computer A and Computer B have complete installs with different .NET providers? If, on Computer B, you rename the existing SQL Anywhere .NET provider and copy the .NET provider from Computer A (the 3152 build), does your application now work correctly? I updated "Computer A" before I read your posting. However, I am now able to verify that I get the same exception on both computers having installed EBF3726
(15 Jun '12, 07:30)
OBR
Replies hidden
OK. I suspect an issue with the .NET provider. I'll inform the interfaces team.
(15 Jun '12, 07:35)
Glenn Paulley
|
Please show us the exact text of the error message you are getting... that may help with searches for clues.
I assume you are talking about the Microsoft Entity Framework 4.3 (there are
hundreds, if not thousands ofmany "entity frameworks" in the world)I would guess the framework thinks that SimpleText.Id is numeric... perhaps that is a default when no type is given. That would explain why the correct code "and (Id = 'a1')" is not being generated.
The update works if the value is numeric because SQL Anywhere treats 999 as a numeric literal (and automatically converts it to a string '999' because SQL Anywhere knows that Id is a string) when processing "and (Id = 999)",
... but it thinks that xxx is a column or variable name when processing "and (Id = xxx)".
Have you tried varchar instead of nvarchar?
@Martin no, but I can't see why that would make a difference. @Breck Carter, I've updated the question with further details
...So you might try to update Computer A to EBF 3726 to check whether the UPDATE will still work?
I've updated to EBF 3726 on computer A. Result being that it also now throws an exception on update.