Identity Key Match

When building an Upsert command, you have to decide which columns will be used to match entities in the passed dataset and the database table you're trying to upsert into.

There are several things to consider:

• The column(s) used can not be database generated, whether it's a database generated Guid, or an auto increment identity key. The reason for that is simple - if the value is auto generated, then you'd never be able to correctly match the values in your passed dataset, and even if you did - you wouldn't be able to insert them with these values, since the database will generate new values on insert.

  In the case where you know exactly the values of these generated columns (and they are your unique key), you probably want to run an update operation, not an upsert

• The column(s) used have to be unique in the database table - usually part of a unique index, or a primary key

• In the case of MySQL, you can't set a match expression in your query - the MySQL engine will use the unique key set on the table to perform the unique match

• If you're using a Guid/string key on your table that is not database generated, you might want to decorate your key to mark it as not database generated. EFCore will by default try to autogenerate a value for these keys, and the upsert library will throw an exception if you try to use it otherwise (see point 1 above)

  [Key, DatabaseGenerated(DatabaseGeneratedOption.None)]
  public string ID { get; set; }

Selecting which columns to use is simple. In the case when you only use a single column to match you can reference it directly:

.On(c => c.ISO)

In case you have a composite unique key, you should instantiate an anonymous object holding these keys:

.On(pv => new { pv.UserID, pv.Date })

Note: If your table has a non auto generated primary key, and you're using it to match, you can skip the .On() clause altogether, as the library will default to using it (as long as it's not auto generated).