Database update/insert fails

Question

I have the following Model class:

public partial class EmployeeInfo
{
    
    public int EmployeeInfoId { get; set; }
    public int PolicyYear { get; set; }= DateTime.Now.Year; 
    public DateTime DateFormFilled { get; set; } = DateTime.Now;
    public string EmployeeNumber { get; set; } = null!;
    public string LastName { get; set; } = null!;
    public string FirstName { get; set; } = null!;
    public virtual JobTitleLookup? JobTitleLookup { get; set; }
    public virtual LocationLookup? LocationLookup { get; set; }
}
  
public partial class JobTitleLookup
{
   
    public int JobTitleLookupId { get; set; }

    public string Title { get; set; } = null!;

    public virtual List<EmployeeInfo> EmployeeInfos { get; } = new List<EmployeeInfo>();
}
public partial class LocationLookup
{
   
    public int LocationLookupId { get; set; }

    public string Location { get; set; } = null!;

    public virtual List<EmployeeInfo> EmployeeInfos { get; } = new List<EmployeeInfo>();
}

I want to insert the row in the table if the employeeNumber and policyYear does not exists in the table, but if policy Year and employee number does exists then I want to update the table. 
When user fills out an employee form. I have the following code in my controller:

public async Task<IActionResult> Create(EmployeeInfo employeeInfo)
{
 await _employeeService.UpdateEmployee(employeeInfo);
}

   public async Task UpdateEmployee(EmployeeInfo employee)
        {
          _ackContext.Entry(employee).State= !_ackContext.EmployeeInfos.Any(x=>x.EmployeeNumber== employee.EmployeeNumber && employee.PolicyYear==DateTime.Now.Year)? EntityState.Added : EntityState.Modified
           await _ackContext.SaveChangesAsync();

        }

As soon as I run the above code, I get an error saying:

DbUpdateConcurrencyException: The database operation was expected to affect 1 row(s), but actually affected 0 row(s); data may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.

Nor sure what am I doing wrong, I also tried this statement and this statement inserts a new row in the table and does not do any update

 public async Task UpdateEmployee(EmployeeInfo employee)
          {  
            _ackContext.EmployeeInfos.Update(employee);
             await _ackContext.SaveChangesAsync();

	}

The above code does not throw any error, but inserts a new row in the table. I can loop through and 
update each column at a time and that works, but I have several properties in my employeeInfo class.
I have several collections inside the class too. I displayed just few in my question above. updating each property will take lot of time and code will be huge. I want to update the entire employeeInfo with one update statement.

any help will be appreciated

Answer 1

Hi @Anjali Agarwal

DbUpdateConcurrencyException: The database operation was expected to affect 1 row(s), but actually affected 0 row(s); data may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.

The issue relates the EmployeeInfo primary key, you can try to set breakpoint to check the employeeInfo's EmployeeInfoId property, it might be the default value "0". When use the EntityState.Modified to modify the entity, the entity should exist in the database and have the primary key, once the item doesn't exist or have the primary key, it will show the above error.

To solve this issue, you can try to modify your code as below: In this sample, I just find the employee according to the employee number and the policy year, you can find a better way and based on your application to set the primary key when update the employeeinfo.

        public async Task UpdateEmployee(EmployeeInfo employee)
        {
            //Query the database and get the primary key.
            var empitem = _ackContext.EmployeeInfos.Where(x => x.EmployeeNumber== employee.EmployeeNumber && employee.PolicyYear==DateTime.Now.Year).AsNoTracking().FirstOrDefault();
            if (empitem!=null)
            {
                employee.EmployeeInfoId = empitem.EmployeeInfoId; //based on the exist entity primary key to update the data.
            }
            else
            {
                employee.EmployeeInfoId = 0; //insert new entity.
            }
            _ackContext.Entry(employee).State= !_ackContext.EmployeeInfos.Any(x => x.EmployeeNumber== employee.EmployeeNumber && employee.PolicyYear==DateTime.Now.Year) ? EntityState.Added : EntityState.Modified
            await _ackContext.SaveChangesAsync();

        }

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best regards,

Dillion