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

Nicks

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. – Outmost

AddOrUpdate is available in EntityFramework6. May be you have to check with other overloads like mentioned #22288352 – Trilley

@Outmost 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; – Nicks

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. – Outmost

@Nicks - 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 – Disprove

Answer 1

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!

Outmost

Just so you know...this upsert is not threadsafe...as I found in production the other day...>_< – Summons

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. – Outmost

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. – Summons

I'm actually not sure at all what you're talking about... Are you saying that the AddOrUpdate method opens an additional thread? – Outmost

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. – Summons

How are you disposing your context? Isnt this a tangential conversation? – Outmost

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. entityframework.codeplex.com/SourceControl/latest#src/… – Summons

I'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. – Outmost

.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! – Summons

@Summons thankyou much! This answer really needs to notify that this is not an atomic AddOrUpdate as you would presume. – Garrotte

I'm not seeing AddOrUpdate in EF6... what namespace is it in? Is it an extension or a member? – Potluck

It's in System.Data.Entity.Migrations.. It is an extension. It is not a member of IDBSet – Potluck

I found this example but not sure about safety here either: forums.asp.net/t/1889944.aspx – Matusow

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), but it's all EF gives you. – Intended

@Intended 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. – Jupiter

@Jupiter I'm just trying to make sure people don't mix the concept of thread safety with the concept of transactional safety. – Intended

Answer 2

For UPSERT I use EFCore BulkExtensions which produces SQL MERGE statement with BulkMerge method. The method is thread safe since it produces atomic operation on the DB

await context.BulkMergeAsync(customers);

The package is here for quite a time and can be used with any version of EFCore

Shore