CODEWITHSUNDEEP
c#entity-framework-6dbcontext
Sorin Vasiliu
Sorin Vasiliu

Reputation: 217

Adding or Updating multiple entries in Entity Framework with Identity ID and constraint Primary Key on two columns

I have the following model class: 

public partial class ORD_Garanzie
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public long ID { get; set; }

    [Column(Order = 1)]
    [Key]
    public int intestatario { get; set; }

    [Column(Order = 2)]
    [Key]
    public string n_polizza { get; set; }

........other properties 

}

The method that is adding entries in the DB is as follows:

        public ORD_Garanzie[] SaveORD_Garanzie(ORD_Garanzie[] ordGaranzieItems)
    {
        using (TBSContext context = new TBSContext())
        {
            if (ordGaranzieItems.Length != 0 || ordGaranzieItems != null)
            {
                try
                {
                    foreach (var garanzieItem in ordGaranzieItems)
                    {
                        context.Entry(garanzieItem).State = garanzieItem.ID == 0 ?
                                                            EntityState.Added :
                                                            EntityState.Modified;

                    }
                    context.SaveChanges();
                }
                catch (DbUpdateException /* ex */)
                {
                    //Log the error (uncomment ex variable name and write a log.)
                    throw;
                }

                return ordGaranzieItems;
            }

            return null;
        }
    }

The problem is that if I'm updating the value of one of the properties marked with the [Key] attribute I'm receiving the following error when SaveChanges() is called: System.Data.Entity.Infrastructure.DbUpdateConcurrencyException: 'Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded.

If I am updating any of the other properties that are NOT marked with the KEY attribute in the model class OR adding a new entity completely, I receive no error.

Or if I remove the KEY attributes (and Column attribute) from the model class, the problem goes away.

Any idea how to solve this? Or a better solution to UPDATE or ADD an array of entities when I have this CONSTRAINT that the two properties must be unique. (I also need to be able to change them if need be).

Upvotes: 1

Views: 1522

Answers (1)

grek40
grek40

Reputation: 13458

In order to have your ID as key, remove the Key annotations from the other columns. To enforce a unique combination of the other columns, use the Index(name, order, IsUnique=true) annotation:

public partial class ORD_Garanzie
{
    public long ID { get; set; }

    [Column(Order = 1)] // optional, not needed for the index
    [Index("IX_SomeIndexName_Unique", 0, IsUnique = true)]
    public int intestatario { get; set; }

    [Column(Order = 2)] // optional, not needed for the index
    [Index("IX_SomeIndexName_Unique", 1, IsUnique = true)]
    public string n_polizza { get; set; }

Upvotes: 1

Related Questions