When updating a row that already exists in the database via an Excel file upload and EPPlus, a new row is being created instead of updating the data that already exists.

How can I update the existing row instead of getting a new row added?

Thank you for the kind assistance

public class Insured
{
    [Key]
    public int id { get; set; }

    [Required]
    public string identifier { get; set; }

    public string policyno { get; set; }

    public string firstname { get; set; }
    public string lastname { get; set; }
    public string gender { get; set; }
    public int year { get; set; }
}

// Controller method
[HttpPost]
[ValidateAntiForgeryToken]
public async Task<IActionResult> ImportExcelFile(IFormFile ExcelFile)
{
    ViewBag.Message = "";

    if (ExcelFile != null)
    {
        var list = new List<Insured>();

        using (var stream = new MemoryStream())
        {
            await ExcelFile.CopyToAsync(stream);

            using (var package = new ExcelPackage(stream))
            {
                ExcelWorksheet worksheet = package.Workbook.Worksheets ["Sheet1"];
                var rowcount = worksheet.Dimension.Rows;

                for (int row = 2; row <= rowcount; row  )
                {
                    list.Add(new Insured
                              {
                                  identifier = worksheet.Cells [row,1].Value.ToString().ToLower().Trim(),
                                  policyno = worksheet.Cells [row, 2].Value.ToString().Trim(),
                                  firstname = worksheet.Cells [row, 3].Value.ToString().Trim(),
                                  lastname = worksheet.Cells [row, 4].Value.ToString().Trim(),
                                  gender = worksheet.Cells [row, 5].Value.ToString().Trim(),
                                  gender = worksheet.Cells [row, 6].Value.ToString().Trim(),
                              }); 
                }
            }

            foreach (var item in list)
            {
                if (ModelState.IsValid)
                {
                    // check if record exist in the database
                    var query = from obj in _db.dbLifeData
                                where  obj.identifier == item.identifier.ToLower() && obj.policyno == item.policyno 
                                select obj;

                    if (query.Count() > 0)  // if row already exists
                    {
                        var query2 = from obj in _db.dbLifeData
                                     where obj.identifier == item.identifier.ToLower() && obj.policyno == item.policyno
                                           && obj.year <= item.year
                                     select obj;    //update the existing data in the database if year is greater

                        if (query2.Count() != 0)  
                        {
                            _db.dbLifeData.UpdateRange(item);
                            _db.SaveChanges();
                        }
                    }
                    else //if no record found add new row
                    {
                        _db.dbLifeData.AddRange(item);
                        _db.SaveChanges();
                    }
                }
            }
        }
    }
}

CodePudding user response：

It wa because the data in your excel does not have the primary key"Id",and the primary key in your database was set to auto-increment,so the "Update"turn to "Add" Automaticlly

I tried as below and it could work:

var list = new List<Insured>()
            {
                new Insured(){identifier="i1",policyno="p1",firstname="firstname",lastname="lastname",gender="gender",year=2022},
                new Insured(){identifier="i2",policyno="p2",firstname="firstname",lastname="lastname",gender="gender",year=2022},
                new Insured(){identifier="i3",policyno="p3",firstname="firstname",lastname="lastname",gender="gender",year=2022},
                new Insured(){identifier="i4",policyno="p4",firstname="firstname",lastname="lastname",gender="gender",year=2022},
                new Insured(){identifier="i5",policyno="p5",firstname="firstname",lastname="lastname",gender="gender",year=2022},
                new Insured(){identifier="i6",policyno="p6",firstname="firstname",lastname="lastname",gender="gender",year=2022}
            };
            var insurelistindb = _context.Insured.ToList();
            list.ForEach(x =>
            {
                var TargetinDb= insurelistindb.FirstOrDefault(y => y.identifier == x.identifier && y.policyno == x.policyno);
                if (TargetinDb != null)
                {
                    TargetinDb.year = x.year;
                    TargetinDb.gender = x.gender;
                    TargetinDb.firstname = x.firstname;
                    TargetinDb.lastname = x.lastname;
                    _context.Entry(TargetinDb).State = EntityState.Modified;
                } 
            });           
            _context.SaveChanges(); 

Result: