1

I'm having an exception that says The property 'itemId' is part of the object's key information and cannot be modified. This happened when I changed my raw sql query into entity framework. Here is the code for the raw sql query, which is working by the way:

            con1.Open();
            cmd1.CommandText = "UPDATE [stringInstrumentItem] SET [itemId] = @newId WHERE itemId = @oldId";
            cmd1.Connection = con1;
            cmd1.Parameters.Add(new SqlParameter("@newId", id));
            cmd1.Parameters.Add(new SqlParameter("@oldId", plusId));
            a = cmd1.ExecuteNonQuery();
            con1.Close();
            cmd1.Parameters.Clear();

And then, I changed that code into entity framework:

using (var context = new MusicStoreDBEntities())
    {
        var bay = (from s in context.stringInstrumentItems where s.itemId == plusId select s).FirstOrDefault();
        bay.itemId = id;
        context.SaveChanges();
    }

It says that I cannot modify the itemId. How come I can modify itemId using the raw sql query version while the entity framework version doesn't? I want to know what is wrong with the entity framework version and How to solve this?

LogicalDesk
  • 1,237
  • 4
  • 16
  • 46
RockStar
  • 87
  • 1
  • 12
  • 1
    You can't modify primary key. In your case you need to delete old one and create new with same data – Anton Jul 14 '17 at 14:35
  • 2
    You can't modify the value of a primary key in EF: https://stackoverflow.com/questions/1367751/update-primary-key-value-using-entity-framework – mm8 Jul 14 '17 at 14:36
  • @Anton - yes, because whenever i delete an item it creates a gap in the itemId like for example i have id 42 43 44, and then i remove 43, it will be like this 42 44. What i did was, i want it to be like this 42 43. The id's are in sequence. – RockStar Jul 14 '17 at 14:53
  • 1
    @mm8 - so does that mean that i should create an auto-generated id(which is the PK) and another id that can be modified? is that correct? – RockStar Jul 14 '17 at 14:57
  • See surrogate keys [here](https://stackoverflow.com/questions/1367751/update-primary-key-value-using-entity-framework) – Steve Greene Jul 14 '17 at 14:58
  • @RockStar - I wanted the same thing in my project, and I searched a lot. Finally I had to create a new itemID property, and keep the primary key id auto-generated. EF doesn't allow to do it, but I think sql server can do it. – Aryan Firouzian Jul 14 '17 at 21:54
  • guys i have just found out that you can apply this in your gridview if you don't want to add another column in database just for sequence purposes. Is this an okay solution? <%# Container.DataItemIndex + 1 %> – RockStar Jul 15 '17 at 13:59

1 Answers1

0

You can't do that in EF. What's your reason for wanting to update the keys so they stay sequential? There shouldn't be any reason for that, but if you do need a sequential ID for some reason you could put it in another column.

The primary key should really be arbitrary. It's only requirement is that it should be unique. I'm thinking you might be taking the wrong approach.

Christian Boyer
  • 48
  • 5
  • yup, thats the purpose. I've decided to create a separate column for the auto-generated id and the modified one(which will make it stay in sequence. – RockStar Jul 15 '17 at 13:41