CODEWITHSUNDEEP
c#entity-framework
Matthew
Matthew

Reputation: 4339

How to add conditions to update an entry with entity framework linq-to-entities

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.

Upvotes: 1

Views: 2472

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205629

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.

Upvotes: 2

Related Questions