如何添加条件以使用实体框架linq-to-entities更新条目 [英] How to add conditions to update an entry with entity framework linq-to-entities

问题描述

假设我具有以下实体：

 公共类库
 {
 public int ID {得到;组; } 
 
公共ICollection< Book>书{组; } 
 
 public Library（）
 {
 Books = new HashSet< Book>（）; 
} 
} 
公共类图书
 {
 public int ID {get;组; } 
 public int LibraryID {get;组; } 
公用字串Title {get;组; } 
公共字符串Author {get;组; } 
 public DateTime PublicationDate {get;组; } 
 public bool CheckedOut {get;组; } 
} 
  

有一堆图书馆，每个图书馆都有一堆书。图书馆中ID = 1的某人签出了ID = 42的书，因此我想将 CheckedOut 属性更新为true。假设由于某种原因，我已经知道图书馆ID和书籍ID，但不知道其他信息。我可以相当容易地更新 CheckedOut 属性，而无需从数据库中获取所有图书数据：

  Book book = new Book（）{
 ID = 42，
 LibraryID = 1，
 CheckedOut = true 
} 
 context.Books。附件（书）； 
 var entry = context.Entry（book）; 
 entry.Property（b => b.CheckedOut）.IsModified = true; 
 context.SaveChanges（）; 
  

这是我的问题。如果数据库中的 LibraryID 不是本书的 1 ，我又如何强制它失败？在普通的SQL中，我可以写

 更新书籍SET CheckedOut = 1 WHERE ID = 42 AND LibraryID = 1 
  

我如何使用实体框架做同样的事情？

An很明显，这里的用例是增加安全性-例如，如果用户无权从1以外的任何库中检出书籍。

解决方案

使用 .Attach（book）时，实际上是在告诉EF：嘿，这些是原始数据库中现有记录的值。因此，在您的情况下，EF会认为现有的 book.LibraryID 是 1 。您可以利用这一事实并通过配置 LibraryID 属性以用作乐观并发令牌（通过任一数据注释）：

  public class Book 
 {
 // ... 
 [ConcurrencyCheck] 
 public int LibraryID {get;组; } 
} 
  

或Fluent API：

  modelBuilder.Entity< Book>（）
 .Property（e => e.LibraryID）.IsConcurrencyToken（）; 
  

现在，如果您打开EF登录并执行代码段，您将看到类似以下的内容：

在14/10/2016 21:35:32 +03：00打开连接

  UPDATE [dbo]。[图书] 
 SET [CheckedOut] = @ 0 
 WHERE（（[[ID] = @ 1）AND（[LibraryID] = @ 2））
 
-@ 0：'True'（类型=布尔值）
 
-@ 1：'42'（类型= Int32）
 
- @ 2： 1（类型= Int32）
  

与您所追求的类似。 / p>

此方法的唯一潜在问题是，当 LibraryID出现时，您将得到 DbUpdateConcurrencyException 数据库中的不是 1 。

Suppose I have the following entities:

public class Library
{
    public int ID { get; set; }

    public ICollection<Book> Books { get; set; }

    public Library()
    {
        Books = new HashSet<Book>();
    }
}
public class Book
{
    public int ID { get; set; }
    public int LibraryID { get; set; }
    public string Title { get; set; }
    public string Author { get; set; }
    public DateTime PublicationDate { get; set; }
    public bool CheckedOut { get; set; }
}

There are a bunch of libraries, and each library has a bunch of books. Someone at the library with ID=1 checks out the book with ID=42, so I want to update the CheckedOut property to true. Suppose for whatever reason I already know the library ID and book ID but not the other info. I can update just the CheckedOut property fairly easily without fetching all the book data from the database:

Book book=new Book(){
    ID=42,
    LibraryID=1,
    CheckedOut=true
}
context.Books.Attach(book);
var entry=context.Entry(book);
entry.Property(b=>b.CheckedOut).IsModified=true;
context.SaveChanges();

Here's my question. How can I also force it to fail if the LibraryID in the database is not 1 for this book? In plain SQL I could write

UPDATE Books SET CheckedOut=1 WHERE ID=42 AND LibraryID=1

How do I do the same thing with entity framework?

An obvious use case here is to add more security--for example, if the user does not have the authority to check out a book from any library other than 1.

解决方案

When you use .Attach(book), you are actually telling EF: hey, these are the original values of the existing record in the database. So in your case EF will think that the existing book.LibraryID is 1. You can utilize that fact and force the EF to perform the desired check by configuring LibraryID property to be used as Optimistic Concurrency Token by either Data Annotations:

public class Book
{
    // ...
    [ConcurrencyCheck]
    public int LibraryID { get; set; }
}

or Fluent API:

modelBuilder.Entity<Book>()
    .Property(e => e.LibraryID).IsConcurrencyToken();

Now if you turn EF logging on and execute you snippet, you'll see something like this:

Opened connection at 14/10/2016 21:35:32 +03:00

UPDATE [dbo].[Books]
SET [CheckedOut] = @0
WHERE (([ID] = @1) AND ([LibraryID] = @2))

-- @0: 'True' (Type = Boolean)

-- @1: '42' (Type = Int32)

-- @2: '1' (Type = Int32)

which is similar to what you are after.

The only potential issue with this approach is that you'll get DbUpdateConcurrencyException when the LibraryID in the database is not 1.

这篇关于如何添加条件以使用实体框架linq-to-entities更新条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！