New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merge/Upsert/AddOrUpdate support #4526

Open
NickAb opened this Issue Feb 10, 2016 · 13 comments

Comments

Projects
None yet
@NickAb

NickAb commented Feb 10, 2016

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

This comment has been minimized.

gdoron commented Feb 11, 2016

@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 rowanmiller changed the title from Is there MERGE/UPSERT support in EF7? to Merge/Upsert/AddOrUpdate support Feb 12, 2016

@rowanmiller

This comment has been minimized.

Member

rowanmiller commented Feb 12, 2016

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

@rowanmiller rowanmiller added this to the Backlog milestone Feb 12, 2016

@NickAb

This comment has been minimized.

NickAb commented Feb 13, 2016

@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

This comment has been minimized.

philn5d commented Mar 15, 2016

Is it the same feature as this?

@NickAb

This comment has been minimized.

NickAb commented Mar 15, 2016

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

This comment has been minimized.

philn5d commented Mar 15, 2016

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

This comment has been minimized.

Ciantic commented Oct 4, 2016

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 comment has been minimized.

yosbeleg89 commented Mar 16, 2017

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

@PeteX

This comment has been minimized.

PeteX commented Apr 30, 2017

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

This comment has been minimized.

Contributor

roji commented Nov 24, 2017

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

This comment has been minimized.

Member

ajcvickers commented Nov 27, 2017

@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

This comment has been minimized.

vovikdrg commented Jan 3, 2018

@artiomchi

This comment has been minimized.

artiomchi commented Feb 19, 2018

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 :)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment