Entity Framework - UPSERT on unique indexes
The AddOrUpdate
method is a member of IDBSet
and is available in EF6.
The AddOrUpdate
method is not an atomic operation, calls from multiple threads does not guarantee the second thread Update
instead of Add
ing again - so you can get duplicate records stored.
This example was tested and worked to your expectations:
Boat boat = new Boat // nullable fields omitted for brevity
{
boat_code = "HelloWorld",
id = 1,
name = "Fast Boat",
built = 1,
length = 100
};
using (BoatContext context = new BoatContext()) // or whatever your context is
{
context.Set<Boat>().AddOrUpdate(boat); // <-- IDBSet!!!
context.SaveChanges();
}
If we change boat_code
the AddOrUpdate()
method will add a new record. If the boat_code
is 'HelloWorld` it will update the existing record. I believe this is what you are looking for...
Hope this helps!
Related videos on Youtube
HellBaby
Updated on July 09, 2022Comments
-
HellBaby almost 2 years
I searched a bit regarding my problem but can't find anything that really to help.
So my problem/dilema stays like this: I know that mysql database have a unique index system that can be used for insert/update in same query using this format:
insert into t(a,b,c) values(1,1,1) on duplicate keys update b=values(b),c=values(c);
and a replace format used to replace a existing recording by that index.to be honest the only similar stuff that I saw in MSSQL is the
merge
but I really don't like it at all and verifying a query to insert or update isn't unique index based after all...So how can I emulate the mysql unique UPSERT into Entity Framework? this is my main problem...
I mean without getting the record from entity set and checking it if is null or not for a possible insert or update;
Can I get it? Or not? Any hint can be useful
I saw this but doesn't appear into version 6...
example of entity:
[Table("boats")] public class Boat { [DatabaseGenerated(DatabaseGeneratedOption.Identity)] public int id { get; set; } [MaxLength(15)] [Index("IX_ProviderBoat",1,IsUnique=true)] public string provider_code { get; set; } public string name { get; set; } [Index("IX_ProviderBoat", 3, IsUnique = true)] [MaxLength(50)] public string model { get; set; } [Index("IX_ProviderBoat", 2, IsUnique = true)] [MaxLength(15)] [Key] public string boat_code { get; set; } public string type { get; set; } public int built { get; set; } public int length { get; set; } }
So I want to update/insert based on the my IX_ProviderBoat unique index using EF
-
wahwahwah over 9 yearsYour data model isn't ideal for EF. Do you need to have a
Boat.id
as well as three unique keys in yourboats
table? Why not use theBoat.provider_code
as your primary key? Also,AddOrUpdate()
is available in EF6. -
Rama Kathare over 9 yearsAddOrUpdate is available in EntityFramework6. May be you have to check with other overloads like mentioned stackoverflow.com/questions/22287852/…
-
HellBaby over 9 years@wahwahwah 1) id is just a identifier(not a primary key); 2) AddOrUpdate is not visisble on my 6.0.0.0 EF Version; 3) provider_code is primary key and also unique index part group;
-
wahwahwah over 9 yearsOk - why do you have an 'id' column then? It seems superfluous, but whatever floats your.. boat :)? The
AddOrUpdate()
method is a memeber ofIDBSet
... I'll post an answer. -
StingyJack almost 5 years@HellBaby - If you are going to include an ID column like that, then the pattern would be to use it as the primary key as a surrogate for the natural key (the unique index), so you dont have to FK as many columns, and so a natural key can be updated without requiring cascades
-
-
Aron over 9 yearsJust so you know...this upsert is not threadsafe...as I found in production the other day...>_<
-
wahwahwah over 9 yearsIt depends on how you dispose your context... this method has nothing to do with thread safety. And when i say "nothing to do" i mean, you should be expressly calling the GC at some point in your code unless your using boilerplate EF.
-
Aron over 9 yearsTwo threads, two contexts, same command, same time. Duplicated inserts...lots of fun! I had hoped that EF would use
MERGE
atomically. But it uses a select/insert/update. -
wahwahwah over 9 yearsI'm actually not sure at all what you're talking about... Are you saying that the
AddOrUpdate
method opens an additional thread? -
Aron over 9 yearsTypically, when one asks for an Upsert, one wants an atomic (and possibly idempotent) method. My issue was that two threads (with different DbContexts) called the
.AddOrUpdate
with the same list at the same time. Without proper indices, I ended up with duplicate entries (if I had proper indices, I would have had exceptions).SQL Server
supports atomic upserts, using theMERGE
command. But EF does not use it. -
wahwahwah over 9 yearsHow are you disposing your context? Isnt this a tangential conversation?
-
Aron over 9 yearsOMG. This has nothing to do with Disposal. I am not reusing the context. Have you even ever read the source code of EF...I am TELLING YOU
.AddOrUpdate
is NOT thread safe. entityframework.codeplex.com/SourceControl/latest#src/… -
wahwahwah over 9 yearsI've never had an issue. The MS documentation is here: msdn.microsoft.com/en-us/library/… How you are implementing the code I think is causing you problems. Maybe i misread the question, but the thread safety issue has to do with scope... not a 10 line code snippet. Sorry if ive caused you any problems.
-
Aron over 9 years
.AddOrUpdate
is forMigrations
, hence the namespace. It is meant for the DbInitializer. The initializer is meant to be single threaded. The issue is thatMERGE
does something completely different. My point is for the OP to be careful with it! -
wahwahwah over 9 yearsGood to know. Thank you :)
-
yǝsʞǝla about 9 yearsThanks for heads up @Aron
-
Jimmy Hoffa over 8 years@Aron thankyou much! This answer really needs to notify that this is not an atomic
AddOrUpdate
as you would presume. -
Dave Lawrence about 8 yearsI'm not seeing AddOrUpdate in EF6... what namespace is it in? Is it an extension or a member?
-
Dave Lawrence about 8 yearsIt's in System.Data.Entity.Migrations.. It is an extension. It is not a member of IDBSet
-
Robert Hoffmann about 8 yearsI found this example but not sure about safety here either: forums.asp.net/t/1889944.aspx
-
jnm2 almost 8 yearsDbContexts are not threadsafe. You should never access the same DbContext instance from more than one thread. That's a different issue from transactions, where you're concerned with multiple DbContext instances. Wrapping this upsert in a transaction is necessary. It isn't as good as a merge (which itself is not atomic without
holdlock
), but it's all EF gives you. -
John almost 8 years@jnm2 He meant two threads and one context each. How on earth that could possibly cause a problem is beyond me though, except EF does something exceptionally retarded.
-
jnm2 almost 8 years@John I'm just trying to make sure people don't mix the concept of thread safety with the concept of transactional safety.