Entity Framework - UPSERT on unique indexes

Question

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

enter image description here

Your data model isn't ideal for EF. Do you need to have a `Boat.id` as well as three unique keys in your `boats` table? Why not use the `Boat.provider_code` as your primary key? Also, `AddOrUpdate()` is available in EF6. — wahwahwah, Nov 20 '14 at 23:00
AddOrUpdate is available in EntityFramework6. May be you have to check with other overloads like mentioned http://stackoverflow.com/questions/22287852/entityframework-6-addorupdate-not-working-with-compound-or-composite-primary-key — Rama Kathare, Nov 21 '14 at 07:58
@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; — HellBaby, Nov 21 '14 at 08:58
Ok - why do you have an 'id' column then? It seems superfluous, but whatever floats your.. boat :)? The `AddOrUpdate()` method is a memeber of `IDBSet`... I'll post an answer. — wahwahwah, Nov 21 '14 at 16:18
@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 — StingyJack, Jul 26 '19 at 14:08

score 22 · Accepted Answer · edited Sep 15 '15 at 14:43

22

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 Adding 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!

edited Sep 15 '15 at 14:43

Jimmy Hoffa

5,909
30
53

answered Nov 20 '14 at 22:02

wahwahwah

3,254
1
21
40

24

Just so you know...this upsert is not threadsafe...as I found in production the other day...>_ – Aron Nov 24 '14 at 06:01
It 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. – wahwahwah Nov 24 '14 at 06:03
9

Two 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. – Aron Nov 24 '14 at 06:04
I'm actually not sure at all what you're talking about... Are you saying that the `AddOrUpdate` method opens an additional thread? – wahwahwah Nov 24 '14 at 06:14
7

Typically, 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 the `MERGE` command. But EF does not use it. – Aron Nov 24 '14 at 06:18
How are you disposing your context? Isnt this a tangential conversation? – wahwahwah Nov 24 '14 at 06:26
6

OMG. 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. https://entityframework.codeplex.com/SourceControl/latest#src/EntityFramework/Migrations/DbSetMigrationsExtensions.cs – Aron Nov 24 '14 at 06:37
I've never had an issue. The MS documentation is here: http://msdn.microsoft.com/en-us/library/system.data.entity.migrations.idbsetextensions%28v=vs.103%29.aspx 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. – wahwahwah Nov 24 '14 at 06:50
13

`.AddOrUpdate` is for `Migrations`, hence the namespace. It is meant for the DbInitializer. The initializer is meant to be single threaded. The issue is that `MERGE` does something completely different. My point is for the OP to be careful with it! – Aron Nov 24 '14 at 07:01
1

@Aron thankyou much! This answer really needs to notify that this is not an atomic `AddOrUpdate` as you would presume. – Jimmy Hoffa Sep 15 '15 at 14:41
I'm not seeing AddOrUpdate in EF6... what namespace is it in? Is it an extension or a member? – Dave Lawrence Apr 05 '16 at 10:30
1

It's in System.Data.Entity.Migrations.. It is an extension. It is not a member of IDBSet – Dave Lawrence Apr 05 '16 at 10:34
I found this example but not sure about safety here either: http://forums.asp.net/t/1889944.aspx – Robert Hoffmann Apr 07 '16 at 16:06
1

DbContexts 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`](http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx)), but it's all EF gives you. – jnm2 Jun 13 '16 at 15:55
1

@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. – John Jun 21 '16 at 12:33
1

@John I'm just trying to make sure people don't mix the concept of thread safety with the concept of transactional safety. – jnm2 Jun 21 '16 at 13:17

Entity Framework - UPSERT on unique indexes

1 Answers1

Linked