Database update/insert fails - Microsoft Q&A

相关文章推荐

深沉的筷子 · 基因编辑婴儿的罗生门：伦理审查申请书系伪造？ ...· 1 月前 ·

健身的小笼包 · mysql 13位时间戳转换日期格式 ...· 2 月前 ·

满身肌肉的炒饭 · fatal: write error: ...· 6 月前 ·

深情的馒头 · 2023年我省高职单招各考试类考试实施方案· 1 年前 ·

没人理的瀑布 · 走遍京都（大相国寺－上御灵神社－下鸭神社－河 ...· 1 年前 ·

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 EmployeeInfos { get; } = new List(); public partial class LocationLookup public int LocationLookupId { get; set; } public string Location { get; set; } = null!; public virtual List EmployeeInfos { get; } = new List(); 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 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

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.
                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