How do I do a simple "Update [table] where [anyfield] = [anyvalue]

Question

I know how to write the code in SQL, but in the EF, I'm stumped:

SQL query:

UPDATE Queue
SET Status = 'Locked'
WHERE ID = 1 AND Status = 'New'

What I've tried, but know is wrong in EF's DbContext is this.

var queueEntities = new QueueEntities();

var queueItem = queueEntities.SingleOrDefault<Queue>(q => q.id == 1 && q.Status = "New");

queueItem.Status = "Locked";

queueEntities.Entry<Queue>(queueItem).State = System.Data.Entity.EntityState.Modified;

queueEntities.SaveChanges();

For obvious reasons, these are not the same as:

The SQL method locks the record while updating it

The DbContext method gets it, then updates it. In concurrency, this allows Thread A to get the item and before it calls the Update, Thread B will have gotten the item, resulting in both items processing the QueueItem

Please let me know if you need something more specific.

Unlike forum sites, we don't use "Thanks", or "Any help appreciated", or signatures on [so]. See "[Should 'Hi', 'thanks,' taglines, and salutations be removed from posts?](http://meta.stackexchange.com/questions/2950/should-hi-thanks-taglines-and-salutations-be-removed-from-posts). — John Saunders, Dec 19 '14 at 23:45
Why are you referring to threading? Are you trying to update a single entity each on a thread? Why not just update all the entities and then call SaveChanges once? One thread, one transaction. — Greg Ennis, Dec 19 '14 at 23:50
No, I want only one Thread to update the record so that if two threads hit it at the same time, the SQL update statement locks the record and set it to Processed so when the next thread comes in (they're both launched simultaneously) it sees that it's already processed so it doesn't process it. — Saturn K, Dec 19 '14 at 23:53
I see. So its not at all a "simple update anyfield=anyvalue". And it's not really an EF question. You need to start a transaction that blocks readers and does the read/update right? — Greg Ennis, Dec 19 '14 at 23:59
You realize that the SQL statement is not necessarily atomic? Multiple requests could execute this statement and find Status = 'New', then all could go ahead and set it to 'Locked'. That doesn't seem to be a tragedy in this case, but if you were trying to do `Set Cost = Cost * 0.75` then you'd have a problem. — John Saunders, Dec 20 '14 at 00:07
@JohnSaunders, Yes, both selects could execute and find a status of new, but when I'm updating, I'm only updating if the status is new, and since updates are blocked, the first query to set it to "in progress" will do so, while the second simultaneous one will try to update it ONLY if it's new and it'll see that it's "In Progress" and won't do so. — Saturn K, Dec 20 '14 at 00:33
Years of programming :). And by blocked, I mean that two simultaneous updates can't happen at the same exact time. With select statements, it's different, select statements don't lock the row while selecting (although you could configure them to do so), but Updates lock the row while doing their business (for obvious reasons). See my answer below... it is established that LINQ or Entity Framework are not capable of doing that... weird and odd :( — Saturn K, Dec 20 '14 at 00:38
@GregEnnis, I simply need to do an update [table] where [field1] = 'test' and [field2] = 'test2', let's forget the whole select part of things because it gets people confused. — Saturn K, Dec 20 '14 at 00:41
In my case, decades. I just read [UPDATE (Transact-SQL)](http://technet.microsoft.com/en-us/library/ms177523.aspx) and didn't immediately see any guarantees, unless you use, for example `WITH(TABLOCK)`. — John Saunders, Dec 20 '14 at 00:41
The guarantee is never there with programming. But I can tell you that the default behavior of updates is to lock the row. I just tested it a week ago. Also, why does the EntityFramework.Extended package exist? To take care of this very problem. — Saturn K, Dec 20 '14 at 00:42
@JohnSaunders, also I just read the same link you posted and I found the following text, "The WITH keyword and the parentheses are required. NOLOCK and READUNCOMMITTED are not allowed. For information about table hints, see Table Hints (Transact-SQL)." it says that you can't do a nolock or readuncommitted with updates — Saturn K, Dec 20 '14 at 00:46
[SET TRANSACTION ISLOATION LEVEL](http://technet.microsoft.com/en-us/library/ms173763.aspx), so what transaction isolation level is being used, and can you change it if necessary? Perhaps by using a transaction? — John Saunders, Dec 20 '14 at 00:48
Transactions are a whole different topic. They exist when you're doing multiple actions. A simple update statement doesn't need to be in a transaction since it's an atomic call — Saturn K, Dec 20 '14 at 00:49
here you may get your answer http://stackoverflow.com/questions/707371/sql-update-set-one-column-to-be-equal-to-a-value-in-a-related-table-referenced-b — varsha, Dec 20 '14 at 09:38
I think I finally understand the question. You are simply asking how to get EF to emit the "AND x=y" clause on your UPDATE statement. Yes? — Greg Ennis, Dec 20 '14 at 17:02
Emit as in "include" yes :). My boss didn't like using the entity framework extensions and told me not to worry about using EF for that specific task. — Saturn K, Dec 21 '14 at 10:07

score 0 · Accepted Answer · answered Dec 20 '14 at 00:36

I found the answer, this is what we call a batch update. In order to be able to have a completely customizable where clause in to install the Nuget package: PM> Install-Package EntityFramework.Extended. The referenced website is: http://weblogs.asp.net/pwelter34/entity-framework-batch-update-and-future-queries

So that's kind of upsetting isn't it? Entity framework has no way to define multiple/customized where clauses?

How do I do a simple "Update [table] where [anyfield] = [anyvalue]

1 Answers1