MVC中SQL数据更新报错：归还书籍时无法更新IsDeleted字段

用户租书时，会在Table_RentedBooks中插入一条IsDeleted=0的记录（代表书籍处于出租状态）。当用户归还书籍时，尝试将该字段改为IsDeleted=1，却出现以下错误：

Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded

需要修改表结构和代码的哪些部分？

现有表结构 #

Table_Books #

BookID  int Unchecked,
CategoryID int  Unchecked,
BookName varchar(100)   Unchecked,
Writer varchar(50)  Unchecked,
Page int    Unchecked,
Recorddate date Unchecked,
UpdateDate date Checked,
Image varchar(300)  Unchecked,
Subject varchar(500)    Unchecked,
NumberofBooks int   Unchecked,
IsDeleted int   Checked
(BookID is the primary key)

Table_RentedBooks #

RentID  int Unchecked,
BookID  int Unchecked,
UserID  int Unchecked,
Recorddate date Unchecked,
Deliverydate    Unchecked,
IsDeleted   int Unchecked,
(Recorddate is the primary key) 

现有代码 #

租书逻辑（RENT BOOK） #

[HttpPost]
public ActionResult RentBook(Table_Books book)
{
        User data = Session["useriz"] as User ;
    
        Book bookdata= Session["bookdata"] as Book;

        Table_RentedBooks z = new Table_RentedBooks ();
        var tbookid = book.BookID;
        var numberofbooks1=(int)bookdata.NumberofBooks -1;

        book.CategoryID = kitapdata.KategoriID;
        book.Image= kitapdata.KitapGorsel;
        book.NumberofBooks = numberofbooks1;

        bookrepo.Update(book);
            
        DateTime date = DateTime.Now;
        DateTime Recorddate = date;
        DateTime Deliverydate = date.AddDays(14);
        z.IsDeleted = 0;
        z.Deliverydate = Deliverydate   ;
        z.Recorddate = Recorddate ;
        z.UserID = data.UserID ;
        z.BookID = tbookid;

        Random rnd = new Random();
        int randomint = rnd.Next();
        z.RentID= randomint;

        rentedrepo.Add(z);
      
        return RedirectToAction("index", "Home");
    }
}

还书逻辑（DELIVER BOOK，存在问题） #

[HttpPost]
public ActionResult KitapTeslimEt(Table_Book book,Table_RentedBooks rentedbook)
{
        User data = Session["useriz"] as User ;
        
    
        Book bookdata= Session["bookdata"] as Book;

              Table_RentedBooks z = new Table_RentedBooks ();
             var tbookid = book.BookID;
            var numberofbooks1=(int)bookdata.NumberofBooks -1;
            book.CategoryID = kitapdata.KategoriID;
            book.Image= kitapdata.KitapGorsel;
            book.NumberofBooks = numberofbooks1;
            bookrepo.Update(book);
            
        DateTime date= DateTime.Now;
        DateTime Recorddate = date;
        DateTime Deliverydate   = date.AddDays(14);
            z.IsDeleted = 1;
            z.Deliverydate   = Deliverydate ;
            z.Recorddate = Recorddate ;
            z.UserID = data.UserID ;
            z.BookID= tbookid;
            Random rnd = new Random();
            int randomint = rnd.Next();
            z.RentID= randomint;
           rentedrepo.Update(z);
            
            return RedirectToAction("index", "Home");
        
    }

通用Update方法 #

public void Update(T par)
{
        try
        {
            db.SaveChanges();
        }
        catch (System.Data.Entity.Validation.DbEntityValidationException dbEx)
        {
            Exception raise = dbEx;
            foreach (var validationErrors in dbEx.EntityValidationErrors)
            {
                foreach (var validationError in validationErrors.ValidationErrors)
                {
                    string message = string.Format("{0}:{1}",
                        validationErrors.Entry.Entity.ToString(),
                        validationError.ErrorMessage);
                    // raise a new exception nesting
                    // the current instance as InnerException
                    raise = new InvalidOperationException(message, raise);
                }
            }
            throw raise;
        }
    }

一、表结构修改 #

1. 修复Table_RentedBooks的主键问题 #

当前Table_RentedBooks用Recorddate作为主键完全不合理：同一时间可能有多个用户租书，会导致主键冲突；还书时也无法定位到具体的租书记录。

修改方案：
将RentID设为自增主键，删除Recorddate的主键设置，同时补全Deliverydate的数据类型：

RentID  int IDENTITY(1,1) PRIMARY KEY, -- 设为自增主键
BookID  int Unchecked,
UserID  int Unchecked,
Recorddate date Unchecked,
Deliverydate date Unchecked, -- 补全数据类型
IsDeleted   int Unchecked

2. 补充外键关联（可选但推荐） #

为Table_RentedBooks的BookID添加外键关联到Table_Books.BookID，UserID关联到用户表的主键，保证数据一致性。

二、代码修改 #

1. 修复通用Update方法的逻辑错误 #

当前Update方法未将传入实体附加到上下文，直接调用SaveChanges()不会产生任何更新操作，这是核心问题之一：

public void Update(T par)
{
    try
    {
        // 关键：将实体附加到上下文并标记为修改状态
        db.Entry(par).State = EntityState.Modified;
        db.SaveChanges();
    }
    catch (System.Data.Entity.Validation.DbEntityValidationException dbEx)
    {
        Exception raise = dbEx;
        foreach (var validationErrors in dbEx.EntityValidationErrors)
        {
            foreach (var validationError in validationErrors.ValidationErrors)
            {
                string message = string.Format("{0}:{1}",
                    validationErrors.Entry.Entity.ToString(),
                    validationError.ErrorMessage);
                raise = new InvalidOperationException(message, raise);
            }
        }
        throw raise;
    }
}

2. 重写还书逻辑（KitapTeslimEt） #

现有还书代码存在新建对象而非查找已有记录、随机生成RentID、库存计算错误等问题，修改后代码如下：

[HttpPost]
public ActionResult KitapTeslimEt(int rentId) // 传入要归还的RentID
{
    User data = Session["useriz"] as User;
    if (data == null)
    {
        return RedirectToAction("Login", "Account"); // 未登录处理
    }

    // 1. 找到对应的有效出租记录
    var rentedBook = rentedrepo.GetById(rentId);
    if (rentedBook == null || rentedBook.IsDeleted == 1 || rentedBook.UserID != data.UserID)
    {
        TempData["Error"] = "无效的还书请求";
        return RedirectToAction("MyRents", "Home");
    }

    // 2. 标记为已归还
    rentedBook.IsDeleted = 1;
    rentedrepo.Update(rentedBook);

    // 3. 更新书籍库存（加1）
    var book = bookrepo.GetById(rentedBook.BookID);
    if (book != null)
    {
        book.NumberofBooks += 1;
        book.UpdateDate = DateTime.Now; // 更新修改时间
        bookrepo.Update(book);
    }

    return RedirectToAction("index", "Home");
}

3. 优化租书逻辑（RentBook） #

优化随机生成RentID、依赖Session数据等问题：

[HttpPost]
public ActionResult RentBook(int bookId) // 直接传入BookID更合理
{
    User data = Session["useriz"] as User;
    if (data == null)
    {
        return RedirectToAction("Login", "Account");
    }

    // 1. 获取最新书籍数据
    var book = bookrepo.GetById(bookId);
    if (book == null || book.IsDeleted == 1 || book.NumberofBooks <= 0)
    {
        TempData["Error"] = "书籍不可租";
        return RedirectToAction("BookDetail", new { id = bookId });
    }

    // 2. 减少库存
    book.NumberofBooks -= 1;
    book.UpdateDate = DateTime.Now;
    bookrepo.Update(book);

    // 3. 添加出租记录（RentID由数据库自增）
    Table_RentedBooks rentedBook = new Table_RentedBooks();
    rentedBook.BookID = bookId;
    rentedBook.UserID = data.UserID;
    rentedBook.Recorddate = DateTime.Now;
    rentedBook.Deliverydate = DateTime.Now.AddDays(14);
    rentedBook.IsDeleted = 0;
    rentedrepo.Add(rentedBook);

    return RedirectToAction("index", "Home");
}

4. 实现仓库层GetById方法 #

需要在仓库类中添加根据主键获取实体的方法：

public T GetById(int id)
{
    return db.Set<T>().Find(id);
}

内容的提问来源于stack exchange，提问作者user17847724