CODEWITHSUNDEEP
c#sql-serverentity-framework
Royi Namir
Royi Namir

Reputation: 148514

C# Entity Framework: Update Query Does not work

I have this simple code : (update value)

I'm trying to update column "c"

 using (MaxEntities ctx = new MaxEntities())
            {
                aa orders = (from order in ctx.aa
                             select order).First();
                orders.c = 22;
                ctx.SaveChanges();
            }

this is the table : 

CREATE TABLE [dbo].[aa](
    [a] [int] NULL,
    [b] [int] NOT NULL,
    [c] [int] NOT NULL
) ON [PRIMARY]

and values inside : enter image description here

but i get an exception :

The property 'c' is part of the object's key information and cannot be modified.

enter image description here

I'm new to EF.

any help will be much appreciated.

Upvotes: 0

Views: 2486

Answers (4)

user1166147
user1166147

Reputation: 1610

I believe if there's no PK at all, EF uses all of the fields/columns as part of the key info.Here's a nice explanation: by @SteveWilkes of why. But what do your entities look like? The other possibility is that it doesn't have a property because the association is inside a different entity, if this is a foreign key.

EDIT

This got me thinking. There are just going to be situations where you have to work with legacy tables having no PK, even if you would never create such a thing. What about views? EF is a mapper - it has to uniquely identify that record so it infers and defines this key. Yes, you could use stored procedures, but could you also hack the XML and remove the keys from the table definition?

AND EDIT AGAIN After posting this, I see @Ladislav Mrnka already said a similar idea (cheating EF and updating its database description), so it has been done (WARNING: Consume at your own risk - never tried). Quick google got me this blog with clear instructions:

Close the model designer in Visual Studio if it is still open and re-open the .edmx file in an XML editor
Find the edmx:StorageModels -> Schema -> Entity Container -> EntitySet element that refers to the      table in question
On the EntitySet element, rename the store:Schema attribute to Schema
Remove the store:Name attribute altogether
Remove the opening and closing DefiningQuery tags and everything in between them
Save and close the .edmx file

But really, who doesn't like a PK? Can you not add an id?

Upvotes: 1

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364249

As explained in another answer EF must uniquely identify every entity. If you don't have PK in the database, EF will infer some key. Key is considered as fixed so if EF inferred c as part of the key (and it did it because it uses all non-nullable non-binary columns) you cannot change its value. Moreover EF takes all tables without primary key as readonly so even if you remove c from the key in the designer and modify c value you will get another exception when you execute SaveChanges.

The reason for the second exception is in the way how EF describes model and the database. When EF inferred key, it did it only for description of your entities and for context's internal needs but not for description of the database. When EF tries to save changes it builds UPDATE statement from database description and without information about real database PK columns it will not be able to identify correct record for update (every update in EF can affect only single record - EF checks ROWCOUNT). This can be solved by cheating EF and updating its database description = by describing some column in the table description as primary key. This leads to multiple problems:

  • You must have some unique column in the database otherwise this method will not work.
  • You must edit EDMX manually (as XML) to add this change
  • You must not use default MS EDMX designer for updating your model from database because it will delete your change

Simple advice: Either use database tables with primary keys or don't use Entity framework.

Upvotes: 2

Sunny
Sunny

Reputation: 3295

Primary key missing here. Add primary key in table and it work.

Upvotes: 1

Darj
Darj

Reputation: 1403

The property 'c' is part of the object's key information and cannot be modified.

That's why you can't edit it. Maybe you need to add id column as a key with identity specified

Upvotes: 6

Related Questions