Generate and auto increment the Id in Entity Framework database first

Question

I have a table CampaignLanguage. The primary key is Id. It should be auto increment. So I have the code:

 public partial class CampaignLanguage
{
    public CampaignLanguage()
    {
        this.CampaignLanguageQuestions = new HashSet<CampaignLanguageQuestion>();
    }

    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

Then in the controller, I want to save the generated object.

 [HttpPost]
    public ActionResult Save(int clientId, int campaignId)
    {
        var campaign = CampaignService.GetCampaignById(campaignId);
        var campaignLanguage = campaign.CampaignLanguages.Where(x => x.CampaignId == campaignId).FirstOrDefault();
        if (campaignLanguage != null)
        {
            campaignLanguage.WelcomeMessage = message;
            CampaignService.Save(campaignLanguage);
        }
        else
        {
            campaignLanguage = new CampaignLanguage();
            campaignLanguage.Id = 1;

            CampaignService.Save(campaignLanguage);
        }
        return Redirect("/Campaign/Index/" + clientId);
    }

However, I get the 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. Refresh ObjectStateManager entries."}

I don't want to change my CampaignService.Save method. So how to fix it?

EDIT

    public void Save(CampaignLanguage campaignLanguage)
    {
        _campaignLanguageRepository.Update(campaignLanguage);
        _unitOfWork.Commit();
    }

EDIT 1

   public virtual void Add(T entity)
   {
       dbset.Add(entity);           
   }
   public virtual void Update(T entity)
   {
       dbset.Attach(entity);
       dataContext.Entry(entity).State = EntityState.Modified;
   }

You setup the Id as auto increment and then you're assigning a value to it. Try removing this line: `campaignLanguage.Id = 1;` — Bruno Avelar, Aug 16 '17 at 16:52
you have these two similar questions https://stackoverflow.com/questions/1836173/entity-framework-store-update-insert-or-delete-statement-affected-an-unexpec https://stackoverflow.com/questions/6819813/solution-for-store-update-insert-or-delete-statement-affected-an-unexpected-n — Victor Hugo Terceros, Aug 16 '17 at 16:52
Why this? `campaign.CampaignLanguages.Where(x => x.CampaignId == campaignId).FirstOrDefault();` ? The associated collection to Campaign should already be filtered on campaignId otherwise you are breaking referential integrity. If that is not the case you need to add a FK constraint. Also if the Id increments in the DB then do not assign a value. If you want more help show the code in your CampaignService, a wild guess would be that the Service does not add the entity instance to the underlying DbContext. — Igor, Aug 16 '17 at 16:54
@Bigeyes Could you post the code of your Service? Where are you getting this error? When updating the campaign Language or creating a new one? Also, you could query it this way: `var campaignLanguage = campaign.CampaignLanguages.FirstOrDefault(x => x.CampaignId == campaignId);` — Bruno Avelar, Aug 16 '17 at 16:57
@Igor, I edited it. I try to get existing object first. If it is not existing, then create new one to store it. — Bigeyes, Aug 16 '17 at 16:59
I am not sure what `_campaignLanguageRepository` is but that should probably call `Add` instead of `Update`. Showing calls to your custom types does not help unless you provide code all the way down the call stack so everyone can see what is eventually/actually called. — Igor, Aug 16 '17 at 17:05
@Igor Agreed. The problem seems to be that the save method is trying to update when you actually want to insert. — Guilherme, Aug 16 '17 at 17:08

Igor · Accepted Answer · 2017-08-16T17:14:59.683

You should be calling Add instead of Update as this is a new instance you want to insert into the data store. Your Save method should check if the primary (auto incremented key) has a value greater than 0 to see if it is new or not. Alternatively you can also see if it is already attached. There is also no need to call Update, setting the entity to state modified does nothing except ensure that all properties will be written back to the DB but the DbContext implements change tracking on entities so this will already happen (unless you are working in a detached state).

public void Save(CampaignLanguage campaignLanguage)
{
    if(campaignLanguage.Id == 0)
        _campaignLanguageRepository.Add(campaignLanguage);
    else
        _campaignLanguageRepository.Update(campaignLanguage);

    _unitOfWork.Commit();
}

On a side note: The type DbContext already implements a Unit of Work pattern and DbSet<T> is an implementation of a Repository pattern. There should not be any need to add another customer Unit of work and repository pattern around EF, you are just creating a whole abstraction layer for no reason that will problems with readability as well as issues later when you want to perform more complex operations like joining multiple tables together in a query.

score 0 · Answer 2 · answered Aug 16 '17 at 17:11

Unfortunately, you need to change your CampaignService.Save. You are trying to update an inexistent campaignLanguage object.

The other problem is you are trying to force a key into an Identity column. You cannot do it with out first set insert_identy to the table.

Maybe, you need to ask for the correct method of CampaignService.

Generate and auto increment the Id in Entity Framework database first

2 Answers2