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

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!

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