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:

asked 13 Jun '12, 08:42

OBR's gravatar image

OBR
291101122
accept rate: 0%

edited 15 Mar '13, 19:46

Mark%20Culp's gravatar image

Mark Culp
24.9k10141297

1

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

(13 Jun '12, 11:21) Breck Carter

Have you tried varchar instead of nvarchar?

(13 Jun '12, 12:57) Martin

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

(15 Jun '12, 05:20) OBR
Replies hidden

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

(15 Jun '12, 05:41) Volker Barth
Comment Text Removed
Comment Text Removed

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

(15 Jun '12, 07:35) OBR

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.

permanent link

answered 15 Jun '12, 18:44

Glenn%20Paulley's gravatar image

Glenn Paulley
10.8k576106
accept rate: 43%

edited 18 Jun '12, 11:32

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819

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?

permanent link

answered 15 Jun '12, 07:01

Glenn%20Paulley's gravatar image

Glenn Paulley
10.8k576106
accept rate: 43%

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

×95
×69

question asked: 13 Jun '12, 08:42

question was seen: 4,198 times

last updated: 15 Mar '13, 19:46