Efcore: Add support for INSERT IGNORE

[shravan2x]

I propose adding an API to indicate to EF Core that an insert (via Add or AddRange and variants) may fail. This is useful when adding data to a table that may already exist.

For instance, I have a program that collects analytics data from a source and inserts it all into a table. Some of these rows may be duplicates of previously inserted rows and so will fail unique constraints. These errors can be safely ignored since the rows already exist in the table.

Using individual INSERT IGNORE ... statements results in horrible performance (~15x slower than AddRangeAsync).

[ErikEJ]

What Database provider?

[ajcvickers]

EF Team Triage: This issue is not something that our team is planning to address in the EF6.x code base. This does not mean that we would not consider a community contribution to address this issue.

Moving forwards, our team will be fixing bugs, implementing small improvements, and accepting community contributions to the EF6.x code base. Larger feature work and innovation will happen in the EF Core code base (https://github.com/aspnet/EntityFramework).

Closing an issue in the EF6.x project does not exclude us addressing it in EF Core. In fact, a number of popular feature requests for EF have already been implemented in EF Core (alternate keys, batching in SaveChanges, etc.).

BTW this is a canned response and may have info or details that do not directly apply to this particular issue. While we'd like to spend the time to uniquely address every incoming issue, we get a lot traffic on the EF projects and that is not practical. To ensure we maximize the time we have to work on fixing bugs, implementing new features, etc. we use canned responses for common triage decisions.

[shravan2x]

@ErikEJ MySql.Data and MySqlConnector (I tested on both).

@ajcvickers Isn't this already the EF Core repo? I'm confused what you mean by your comment.

[ajcvickers]

@shravan2x It's a boilerplate message--see the note at the end of it. The point is we're not going to do it for EF6.

[shravan2x]

@ajcvickers Oh I see. I don't know which repo hosts the EF6 code base, but since this one is already the EF Core code base, can we leave the issue open?

EDIT: Hopefully I understood what you meant :)

[ajcvickers]

@shravan2x Are you asking for this to be implemented in EF Core or EF6?

[shravan2x]

@ajcvickers EF Core. Sorry, my original question was mis-worded. I'll fix that.

[ajcvickers]

@shravan2x We will discuss in triage.

[ajcvickers]

Notes from triage: it's not 100% clear that this is really useful in the context of an OR/M, but moving it to the backlog to consider at a later time.

[LeaFrock]

Notes from triage: it's not 100% clear that this is really useful in the context of an OR/M, but moving it to the backlog to consider at a later time.

At least for MySql, it's very useful.
I‘ve seen a lot of questions like INSERT-IGNORE-using-EF-Core about how to insert datas into a table , which already has datas and an unique index/key, without any exceptions(if the data exists, just skip it). MySql supports it by 'insert ignore into', while SqlServer is still unknown to me for the answer.
If EF Core provides the API, we'll get the best practice.

[mqudsi]

@ajcvickers can you explain what your doubts about its suitability for an ORM are? I'm not sure I see the difference between this or other SQL translations.

When attempting to insert data that may already be present in the database without insert or ignore, you basically need to either prepopulate some sort of bloom filter or hash set beforehand and pray you don't run into any coherence issues or else use fine-grained transactions and throw-and-catch lots of exceptions.

While the SQL specifics are certainly provider-dependent (just like everything else), its certainly not a concept specific to a single SQL dialect. INSERT IGNORE is very closely linked with upsert, which is quickly becoming "a thing" in the rdbms world. MySQL hase INSERT IGNORE and REPLACE INTO, SQLite has INSERT OR IGNORE for primary key conflicts and ON CONFLICT [IGNORE|REPLACE], PostgreSQL has ON CONFLICT ____ DO [NOTHING|UPDATE ...], SQL Server has IF NOT EXISTS(..) for old versions and now MERGE ....

Please correct me if I'm wrong, but we have a general concept (need to insert content that may or may not already exist in the database and either update/abort/ignore if it already does) that is available in the most popular database options supported/targeted by EF Core, but requires different syntax for each implementation, which to me makes it definitely ORM-worthy.

The only issue is that it does not map cleanly to LINQ because LINQ expressions don't really have any concept of constraints. But for the most part, updates and inserts are orthogonal to querying and I imagine this could be implemented without touching the IQueryable interface at all; if you take the most simple example of either completely replacing or completely ignoring (i.e. disregarding the more complex options afforded by the PostgreSQL and SQL Server languages) then in its most basic form this could simply be an optional parameter to the DbSet<>.Add() method specifying the conflict resolution action with ABORT or equivalent (which is basically the current behavior) being the default and the only required version to support.

[ajcvickers]

@mqudsi I don't remember the full extent of the discussion with the team. Nevertheless, this is something we have on the backlog to consider for a future release.