C# Entity Update Table Failing

Question

I've a table that has composite primary key as provided below. I'm trying to add/update/delete functionality using C# MVC. Add and Delete works fine but Update for EffectiveDate Column is failing as there are multiple rows with same ClientName and Portfolio exists. Table Stucture and Entity/Service code is provided below. Can you please take a look what I'm missing in code?

RunTime Error:

Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=472540 for information on understanding and handling optimistic concurrency exceptions.

 CREATE TABLE [dbo].[CustomAUM](
[Client] [varchar](80) NOT NULL,
[Portfolio] [varchar](100) NOT NULL,
[AUM] [numeric](30, 6) NOT NULL,
[EffectiveDate] [datetime] NOT NULL,
[IsStatic] [char](1) NULL,
[sysDate] [datetime] NOT NULL,
[ModifiedBy] [varchar](80) NOT NULL,
 CONSTRAINT [PK_CustomAUM] PRIMARY KEY CLUSTERED 
(
    [Portfolio] ASC,
    [Client] ASC,
    [EffectiveDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

Entity Code

public class Custom
{

    [Key]
    [Column(Order = 1)]
    public virtual string Client { get; set; }

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

    [Required]
    public virtual decimal AUM { get; set; }

    [Key]
    [Column(Order = 3)]
    public DateTime EffectiveDate { get; set; }

    [StringLength(50)]
    [Column(TypeName = "char")]
    public string IsStatic { get; set; }

    [Required]
    public DateTime sysDate { get; set; }

    [StringLength(500)]
    public virtual string ModifiedBy { get; set; }

}

public class CustomerMap : EntityTypeConfiguration<Custom>
{
    public CustomerAUMMap()
    {
        //Primary Key
        this.HasKey(k => new { k.Client, k.Portfolio, k.EffectiveDate });

        this.ToTable("CustomAUM");

        this.Property(x => x.Client).HasColumnName("Client");
        this.Property(x => x.Portfolio).HasColumnName("Portfolio");
        this.Property(x => x.AUM).HasColumnName("AUM");
        this.Property(x => x.EffectiveDate).HasColumnName("EffectiveDate");
        this.Property(x => x.IsStatic).HasColumnName("IsStatic");
        this.Property(x => x.sysDate).HasColumnName("sysDate");
        this.Property(x => x.ModifiedBy).HasColumnName("ModifiedBy");
    }
}

Service Method

public void CustomAUM_Update(RiskReportDataViewModel riskReportDataViewModel, string userName)
    {
            var entity = new Custom();

            entity.Client = riskReportDataViewModel.Client;
            entity.Portfolio = riskReportDataViewModel.Portfolio;
            entity.AUM = riskReportDataViewModel.AUM;
            entity.EffectiveDate = DateTime.Parse(riskReportDataViewModel.EffectiveDate.ToShortDateString());
            entity.IsStatic = riskReportDataViewModel.IsStatic;
            entity.sysDate = DateTime.Now;
            entity.ModifiedBy = userName;

            riskContext.Custom.Attach(entity);
            riskContext.Entry(entity).State = EntityState.Modified;
            riskContext.SaveChanges();
    }

// View Model

public class RiskReportDataViewModel
{

    [Key]
    public virtual string Client { get; set; }

    [Key]
    [StringLength(500)]
    public virtual string Portfolio { get; set; }

    public virtual decimal AUM { get; set; }

    [Key]
    public virtual DateTime EffectiveDate { get; set; }

    public virtual String IsStatic { get; set; }

    public virtual DateTime sysDate { get; set; }

    [StringLength(500)]
    public virtual string ModifiedBy { get; set; }

}

I've updated with RunTime Error I'm getting. I think it means not able to save any Row in DB. - Thanks. — Partha

Jun An Jun An · Accepted Answer · 2017-01-24T16:58:57

I think you updated with RiskReportDataViewModel, did you use Custom to update ? show me the method for updating.

actually, you need to select to get "Custom" from database first, then change values of "Custom" and save.

and why you have three keys in custom model as below ?

public class Custom
{

[Key]
[Column(Order = 1)]
public virtual string Client { get; set; }

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

[Required]
public virtual decimal AUM { get; set; }

[Key]
[Column(Order = 3)]
public DateTime EffectiveDate { get; set; }

[StringLength(50)]
[Column(TypeName = "char")]
public string IsStatic { get; set; }

[Required]
public DateTime sysDate { get; set; }

[StringLength(500)]
public virtual string ModifiedBy { get; set; }

}

add one of integer value for key.

C# Entity Update Table Failing

2 Answers