1

I want to update a mediate table's primary keys. This post suggests to take another primary key and don't change it, but we're using our tables in other non-EF projects, I didn't designed them and I'm not able to change them. Do I have any other option? Anything? Even deleting the old record and inserting a new one. I just don't know how to retrieve the old values.

This is my class:

public class ZChangeUnits : User
{
    [Key]
    [Column(TypeName = "VARCHAR", Order = 0), StringLength(4)]
    public string CCode1 { get; set; }

    [ForeignKey("CCode1")]
    public virtual ZUnits ZUnits1 { get; set; }

    [Key]
    [Column(TypeName = "VARCHAR", Order = 1), StringLength(4)]
    public string CCode2 { get; set; }

    [ForeignKey("CCode2")]
    public virtual ZUnits ZUnits2 { get; set; }

    [Column(TypeName = "NUMERIC")]
    [DecimalPrecision(18, 5)]
    public decimal NZarib { get; set; }
}

UPDATE

I've posted the schema below, the left table is used for unit conversion. This is actually the suggested method in this answer.

I can't think of any other way to implement that. Whether I should update my table's design or some EF code will do the job. Any help and solution is welcome. :)

Table's relationships

Community
  • 1
  • 1
Akbari
  • 2,369
  • 7
  • 45
  • 85
  • 3
    Updateable primary keys are a *very bad database smell*. The guidance is *not* related to Entity Framework but database design in general. It looks like you are using as primary key a field that has business significance - another *very bad DB design* practice. Even with plain SQL it's hard to cover up such a bug. The only realistic solution is to fix your database. Otherwise, forget ORMs (any ORM, not just EF) and write a stored procedure to explicitly execute an `UPDATE` statement. You *may* be able to overcome this by loading an entity and changing its key using the same DbContext. – Panagiotis Kanavos Apr 27 '15 at 09:34
  • How should I implement a many-to-many relation then? – Akbari Jun 01 '15 at 07:04
  • Many-to-many relations are implemented using an intermediary table, whether you use an ORM or not, eg StudentCourses links Students and Courses. ORMs don't deal with M-N relations very well though so it's better to create a separate class to represent the relation, as shown [here](https://lostechies.com/jimmybogard/2014/03/12/avoid-many-to-many-mappings-in-orms/) – Panagiotis Kanavos Jun 01 '15 at 07:10
  • `ZChangeUnit` is that intermediary table! :) Please consider the above schema. – Akbari Jun 01 '15 at 07:18
  • What is the question then? Did you use one row to merge two actual rows? Just don't, store each individual row. I suspect you are trying to solve a *different* problem and having trouble with the attempted solutions. You don't need to violate proper design simply to create an M-N relation, nor does EF force you to. What is your *actual* problem? – Panagiotis Kanavos Jun 01 '15 at 07:35
  • Thanks for your time and attention Panagiotis, my actual problem is that I can't update that table's primary key! That's a table for unit conversion, so users might simply decide to change the relation, and update `CCode1`, or `CCode2`, or both fields. If I try to update the model, I'll receive a nice `Store update, insert, or delete statement affected an unexpected number of rows (0)` exception. – Akbari Jun 01 '15 at 07:44
  • That's not a real problem - that's a bad design. Don't use the business columns as keys, add a [surrogate key](http://en.wikipedia.org/wiki/Surrogate_key), eg an IDENTITY column called `Id`. You already posted a link to this – Panagiotis Kanavos Jun 01 '15 at 07:52
  • As I said I didn't designed them, is this the only solution? – Akbari Jun 01 '15 at 07:53
  • ORMs in general don't deal well with magically changing keys. In fact, the design has issues even from an ER perspective - if the relation rows contains actual data, it probably should be handled as a separate entity. And changing columns are always a bad choice of primary keys. In fact, the guideline against using business keys is precisely because they change – Panagiotis Kanavos Jun 01 '15 at 07:57
  • Moreover, business users *don't* like disappearing relations - they want relations that are marked as inactive, deleted or whatever, but they really dislike the idea that data can disappear without a trace – Panagiotis Kanavos Jun 01 '15 at 07:58

0 Answers0