Merge/Upsert/AddOrUpdate support

[NickAb]

In my project I need to insert entity if it does not exist yet, or update it otherwise (somewhat like UPSERT in mongodb). As I understand in SQL it can be accomplished using MERGE, but I was not able to find MERGE in EF Core.

[gdoron]

@NickAb I'm not sure Merge is even applicable to ORMs.
Can you write a little snippet showing how you would have use it?

[rowanmiller]

There isn't anything at the moment, though we could potentially do something nice (but not for 1.0.0).

[NickAb]

@gdoron Actually, I am only interested in merge-as-upsert analog (upsert as in mongo upsert), not full-blown MERGE syntax, so it might look like:

var newFeatureForRegionSetting = new FeatureForRegionSetting();
entity.Region = region;
entity.Feature = feature;
entity.Settings = settings;

dbCtx. FeatureForRegionSettings.Upsert(x => x.region == entity.region && x.feature == entity.feature, newFeatureForRegionSetting);

which will insert newFeatureForRegionSetting if condition is not matched, or update entity if condition is matched.

I can not provide usage example for full-blown merge, as I am not that familiar with other MERGE uses.

[philn5d]

Is it the same feature as this?

[NickAb]

No, I don't think so. As I understand, the issue you are referring to is about "merging disconnected graphs", so it is about interworking of EF change tracking, etc.
What I am referring to is SQL MERGE statement, see docs here https://msdn.microsoft.com/en-us/library/bb510625.aspx

[philn5d]

Ah, I've done this with a home brewed extension method but it needs to get the entity before doing the upsert. Needed to define the key value and didn't support multiple keys. Would be useful to have since it would be more performant.

There's a pattern described https://msdn.microsoft.com/en-us/data/jj592676.aspx
Which is essentially what I wrapped in the extension, would be much better if the framework did this instead. Especially if it can be made to determine if the keys are default or not - without necessarily loading the entity from the data store.

Perhaps it could leverage the MERGE statement as you alluded to. Match on entity keys. I could see folks wanting the results of the MERGE returned which would complicate the operation. The simple case would be useful enough to warrant only Insert or Update with no additional complexity. Also, depends on whether or not the data store supports MERGE if its to be used to implement the feature.

[Ciantic]

There is a very handy document in PostgreSQL wiki about UPSERT in various SQL dialects. It may come handy if someone is trying to implement it in the EF.

(In practice it seems there is no agreed way how it should work, especially with unique fields, and would be a messy thing to do in EF because of it)

[yosbeleg89]

This is an implementation of MERGE to MSSQL than can give ideas. It is an extension method to EF6.

[PeteX]

I know there are lots of things it would be nice to support, and limited developer time, but it's a shame this one is missing. Apart from this, my code is entirely database-independent, but it's hard to implement upsert without database support.

The usual pattern is that you try to insert, and whether it succeeds or fails, you know the relevant row is in the database. You can therefore pull it out and work with it. There are problems with this approach, though:

• As far as I can tell, you have to discard the DbContext after the insert fails, because otherwise the next SaveChanges() will retry the insert.
• EF Core seems to write an unwanted exception message into the log when the insert fails.
• Failure usually aborts transactions, so the insert attempt and the following update can't be atomic. This creates problems with races where the insert succeeds, but the row gets updated before the following update.

[roji]

Just pinging this to make sure it's not totally forgotten. With the seemingly wide support of UPSERT/MERGE across databases, this could be a pretty valuable feature (although of course the specific implementations/variations would have to be investigated (see this comparative doc cited above by @Ciantic)

[ajcvickers]

@roji Thanks--there's definitely a lot of value in doing this. the comparative doc is very useful--we would have to figure out what to do for SQL Server.

[vovikdrg]

@ajcvickers https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql it seems to be "upsert"

[artiomchi]

I liked the idea of an Upsert command for EF Core, so I thought to make a simple extension that could be used in some of the more simple scenarios.

Considering the example above, it could be expressed like this:

dbCtx.Upsert(new FeatureForRegionSetting
  {
    Region = region,
    Feature = feature,
    Settings = settings
  })
  .On(x => new { x.Region, x.Feature })
  .RunAsync();

But it can also handle more interesting scenarios:

DataContext.Upsert(new DailyVisits
  {
    UserID = userID,
    Date = DateTime.UtcNow.Date,
    Visits = 1,
  })
  .On(v => new { v.UserID, v.Date })
  .UpdateColumns(v => new DailyVisits
  {
    Visits = v.Visits + 1,
  })
  .RunAsync();

I've posted the project here: https://github.com/artiomchi/FlexLabs.Upsert
I also described a bit more about it in a blog post. It's a simple extension, and can't be directly merged into EF, but I think the syntax is pretty good, and it might be useful enough for some people :)

[DmitrijOkeanij]

I have situation where Update very needed too.
I ask this situation on StackOverflow, link here.

[Nonary]

Apparently, the update method in Entity functions as an Add or Update by default.
https://docs.microsoft.com/en-us/ef/core/saving/disconnected-entities#saving-single-entities

So this might be able to be closed now?

[vovikdrg]

@Nonary Add/Update <> Upsert

[Nonary]

@vovikdrg Care to define what's different?
https://en.wiktionary.org/wiki/upsert defines it as a way to update or insert in databases.

[vovikdrg]

@Nonary I did few comments before. Link you provided has nothing to do with technical stuff if you are asking about words definition and differences probably its wrong thread. If you really care about technical differences of upsert in sql world please check here https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-2017

[PeteX]

@vovikdrg are you saying that EF doesn't generate the database-specific upsert command? That is important because as I said above, trying to emulate upsert with insert and update tends to lead to odd effects.

[vovikdrg]

@PeteX EF will generate only update or insert commands which are different from upsert. Also what do you mean about emulate? Upsert is supported almost in all engines.At some point due to complexity maybe it make sense even to be as extension EF.Core.Extension.SqlServer, EF.Core.Extension.MySql

[PeteX]

You don't need to emulate upsert, I was just pointing out that it doesn't work very well if you try!

[artiomchi]

@Nonary in the MS docs you linked to, it never tells you that it does an upsert:

The Update method normally marks the entity for update, not insert. However, if the entity has a auto-generated key, and no key value has been set, then the entity is instead automatically marked for insert.

So, it'll either generate an INSERT or an UPDATE sql statement, depending on the state of the entity passed to the Update() method.

@Nonary @vovikdrg @PeteX

At this point in time, there is no native Upsert functionality in Microsoft's EF Core package. If you need upsert support in EF Core, you can either manually construct the correct SQL statement for the DB engine you're using, use an SP.

Another alternative is use the FlexLabs.EntityFrameworkCore.Upsert package that I created. 😄 At the moment, it will generate the SQL statement for SQL Server, MySQL, Postgres and Sqlite, and run it immediately.

I'm continuously working on improving it, and am planning to extend it to use the EF's object state in the future, but even without that - it works quite well, and has been tested in several projects currently in production.

[vovikdrg]

@PeteX any examples or arguments when it is not working? I did use it many times work like a charm. Maybe my cases were simple or I was doing something "wrong"

[PeteX]

@vovikdrg when what didn't work sorry? I'm not sure if you're talking about EF now, EF as it was when I wrote my first comment, or emulating upsert with INSERT and UPDATE.

In general the problems are races that occur when access to data is contended, so most of the time things will work correctly.

[TanvirArjel]

@ajcvickers Any update about this feature please?

[ajcvickers]

@TanvirArjel This issue is in the Backlog milestone. This means that it is not going to happen for the 3.0 release. We will re-assess the backlog following the 3.0 release and consider this item at that time. However, keep in mind that there are many other high priority features with which it will be competing for resources.

[borisdj]

At the moment can be achieved with authored lib. EFCore.BulkExtensions, using method:
context.BulkInsertOrUpdate(entities);
(has config option for custom unique column(s))
Free for most users, paid only for companies with annual revenue $ 1 million+ (small fee for yearly license).

[allesandrogallo]

@borisdj if you promote your library, be transparent and put a disclaimer in all your post saying it's not free + link to your pricing borisdj/EFCore.BulkExtensions#1079 or website https://www.codis.tech/efcorebulk/. I lost my time by trying your solution... I had to undo my code because our company doesn't want to pay $1000 for your license!!!

[Ali-alshreef]

If it does not affect performance, I hope so ^_^ .

[markusschaber]

@borisdj if you promote your library, be transparent and put a disclaimer in all your post saying it's not free + link to your pricing borisdj/EFCore.BulkExtensions#1079 or website https://www.codis.tech/efcorebulk/. I lost my time by trying your solution... I had to undo my code because our company doesn't want to pay $1000 for your license!!!

Thats why the license is the first thing I check when evaluating a third party dependency. Both free and commercial products may have license terms not suitable for your project.

[xamir82]

Hey there. Any traction on this issue? Any chance it's considered for v8?

[ajcvickers]

This issue is in the Backlog milestone. This means that it is not planned for the next release (EF Core 8.0). We will re-assess the backlog following the this release and consider this item at that time. However, keep in mind that there are many other high priority features with which it will be competing for resources. Make sure to vote (👍) for this issue if it is important to you.

[0xced]

Well, this very issue is already the most upvoted one. 😉

[ErikEJ]

@0xced Every vote counts!

[videokojot]

At the moment can be achieved with authored lib. EFCore.BulkExtensions, using method:
context.BulkInsertOrUpdate(entities);
(has config option for custom unique column(s))
Free for most users, paid only for companies with annual revenue $ 1 million+ (small fee for yearly license).

@markusschaber @allesandrogallo

Shameless plug:

I created MIT fork (from point when it was still MIT (18.1.2023)) of repository above (where I solved also multiple issues not fixed in original):

EFCore.BulkExtensions.MIT

https://www.nuget.org/packages/EFCore.BulkExtensions.MIT/

So give it a try.

[IanKemp]

Over 8 years later and still not implemented despite consistently being one of the top three highest-voted issues in this repo... @roji @ajcvickers can't you get the SQL Server team to add an equivalent construct to Postgres's on conflict?

[roji]

@IanKemp we don't control what features SQL Server implements, but they're definitely aware of the need. In any case, this issue isn't about adding anything to SQL Server, but implementing whatever does make sense within EF (and a MERGE-based implementation may make sense, we'd need to study this more).

I do hope we get around to tackling this in EF 10.

[ewhitmore]

At the moment can be achieved with authored lib. EFCore.BulkExtensions, using method:
context.BulkInsertOrUpdate(entities);
(has config option for custom unique column(s))
Free for most users, paid only for companies with annual revenue $ 1 million+ (small fee for yearly license).

@markusschaber @allesandrogallo

Shameless plug:

I created MIT fork (from point when it was still MIT (18.1.2023)) of repository above (where I solved also multiple issues not fixed in original):

EFCore.BulkExtensions.MIT

https://www.nuget.org/packages/EFCore.BulkExtensions.MIT/

So give it a try.

I was using EFCore.BulkExtensions and sometime a couple months ago it started jumbling my IDs during bulk imports. Spent over 10 hours debugging the issue. The https://github.com/videokojot/EFCore.BulkExtensions.MIT package dropped right in and cleared everything up. A big thank you to @videokojot. I agree with the rest of the folks here though, this should be supported out of the box functionality from Microsoft for EF.

[bdcberuni]

While waiting for the feature, I've created a custom DbContextOptionsExtension that replaces the IUpdateSqlGenerator to one that overrides the AppendInsertCommandHeader method to write "REPLACE INTO" instead of "INSERT INTO" for MySQL and "INSERT OR REPLACE INTO" for Sqlite when needed. Since the classes are singleton, I've created an accessor (similar to the HttpContextAccessor) to be able to get my saveOptions. The end results looks like this

var options = new DbContextOptionsBuilder<AppDbContext>()
                    .UseSqlite()
                    .UseSqliteSaveOptions()
                    .Options;

var dbContext = new AppDbContext(options);

dbContext.Entities.Add(new Entity { Id = Guid.NewGuid(), Name = "An entity" });
await dbContext.SaveChangesAsync(options => options.UpsertOnInsert = true);

I would have prefered to be able to do something like

dbContext.Entities.Upsert(new Entity { Id = Guid.NewGuid(), Name = "An entity" });

but the only possible way I found to extend it that way was to create a temp shadow property to flag the entityEntry. It felt very dirty. With the Extension types it would probably have been possible, but sadly it's not in the next release of dotnet.

[gulbanana]

Can you write a little snippet showing how you would have use it?

Revisiting this question in light of the Execute APIs added in EF Core 7, it would be nice to have a method similar to this for the standardised MERGE statement.

static Task MergeIntoAsync<TSource, TTarget, TKey>(
        this DbSet<TTarget> target,
        IQueryable<TSource> source,
        Expression<Func<TTarget, TKey>> targetKeySelector,
        Expression<Func<TSource, TKey>> sourceKeySelector,
        Expression<Func<SetPropertyCalls<TSource>, SetPropertyCalls<TSource>>>? whenMatchedThenUpdate = null,
        Expression<Func<TTarget, TSource, TTarget>>? whenNotMatchedThenInsert = null) where TTarget : class

Easier said than done, of course.