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

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