Update entity with string as PK in EntityFramework and SQL Anywhere as provider

[Former Member]

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 and added to the update query?

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 and generated for the update statement (trace output).

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:

[Breck_Carter]

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 of many "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)".

[MCMartin]

Have you tried varchar instead of nvarchar?

[Former Member]

@Martin no, but I can't see why that would make a difference. @Breck Carter, I've updated the question with further details

[VolkerBarth]

...So you might try to update Computer A to EBF 3726 to check whether the UPDATE will still work?

[Former Member]

I've updated to EBF 3726 on computer A. Result being that it also now throws an exception on update.

[Former Member · Accepted solution]

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.

[Former Member · Accepted solution]

Problem was solved by EBF 3742. Thank you, Glenn

[Former Member]

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?

[Former Member]

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

[Former Member]

OK. I suspect an issue with the .NET provider. I'll inform the interfaces team.