Upsert

These scenarios demonstrate how to perform an upsert (insert/update).

Where possible, this should be performed in a single statement.

Scenario Prototype

public interface IUpsertScenario<TDivision>
   where TDivision : class, IDivision, new()
{
    /// <summary>
    /// Gets an Division row by its primary key.
    /// </summary>
    TDivision? GetByKey(int divisionKey);

    /// <summary>
    /// Performs an upsert where it looks for a matching row by DivisionName instead of the primary key.
    /// DivisionKey may be 0.
    /// </summary>
    /// <returns>The DivisionKey of the inserted or updated row</returns>
    int UpsertByName(TDivision division);

    /// <summary>
    /// Performs an upsert where a 0 for DivisionKey means an insert.
    /// </summary>
    /// <returns>The DivisionKey of the inserted or updated row</returns>
    int UpsertByPrimaryKey(TDivision division);
}

ADO.NET

This code demonstrates the MERGE syntax.

    public class UpsertScenario : SqlServerScenarioBase, IUpsertScenario<Division>
    {
        public UpsertScenario(string connectionString) : base(connectionString)
        { }

        public Division GetByKey(int divisionKey)
        {
            const string sql = @"SELECT d.DivisionKey,
       d.DivisionId,
       d.DivisionName,
       d.CreatedDate,
       d.ModifiedDate,
       d.CreatedByEmployeeKey,
       d.ModifiedByEmployeeKey,
       d.SalaryBudget,
       d.FteBudget,
       d.SuppliesBudget,
       d.FloorSpaceBudget,
       d.MaxEmployees,
       d.LastReviewCycle,
       d.StartTime FROM HR.Division d WHERE d.DivisionKey = @DivisionKey;";

            using (var con = OpenConnection())
            using (var cmd = new SqlCommand(sql, con))
            {
                cmd.Parameters.AddWithValue("@DivisionKey", divisionKey);
                using (var reader = cmd.ExecuteReader())
                {
                    reader.Read();
                    return new Division(reader);
                }
            }
        }

        public int UpsertByName(Division division)
        {
            if (division == null)
                throw new ArgumentNullException(nameof(division), $"{nameof(division)} is null.");

            const string sql = @"MERGE INTO HR.Division target
USING
(
    VALUES
        (@DivisionKey, @DivisionId, @DivisionName, @ModifiedDate, @CreatedByEmployeeKey,
         @ModifiedByEmployeeKey, @SalaryBudget, @FteBudget, @SuppliesBudget, @FloorSpaceBudget, @MaxEmployees,
         @LastReviewCycle, @StartTime
        )
) source (DivisionKey, DivisionId, DivisionName, ModifiedDate, CreatedByEmployeeKey,
          ModifiedByEmployeeKey, SalaryBudget, FteBudget, SuppliesBudget, FloorSpaceBudget, MaxEmployees,
          LastReviewCycle, StartTime
         )
ON target.DivisionName = source.DivisionName
WHEN MATCHED THEN
    UPDATE SET DivisionId = source.DivisionId,
               ModifiedDate = source.ModifiedDate,
               ModifiedByEmployeeKey = source.ModifiedByEmployeeKey,
               SalaryBudget = source.SalaryBudget,
               FteBudget = source.FteBudget,
               SuppliesBudget = source.SuppliesBudget,
               FloorSpaceBudget = source.FloorSpaceBudget,
               MaxEmployees = source.MaxEmployees,
               LastReviewCycle = source.LastReviewCycle,
               StartTime = source.StartTime
WHEN NOT MATCHED THEN
    INSERT
    (
        DivisionId,
        DivisionName,
        CreatedByEmployeeKey,
        ModifiedByEmployeeKey,
        SalaryBudget,
        FteBudget,
        SuppliesBudget,
        FloorSpaceBudget,
        MaxEmployees,
        LastReviewCycle,
        StartTime
    )
    VALUES
    (source.DivisionId, source.DivisionName, source.CreatedByEmployeeKey, source.ModifiedByEmployeeKey,
     source.SalaryBudget, source.FteBudget, source.SuppliesBudget, source.FloorSpaceBudget, source.MaxEmployees,
     source.LastReviewCycle, source.StartTime)
OUTPUT Inserted.DivisionKey;";

            //update audit column
            division.ModifiedDate = DateTime.UtcNow;

            using (var con = OpenConnection())
            using (var cmd = new SqlCommand(sql, con))
            {
                cmd.Parameters.AddWithValue("@DivisionKey", division.DivisionKey);
                cmd.Parameters.AddWithValue("@DivisionName", division.DivisionName);
                cmd.Parameters.AddWithValue("@DivisionId", division.DivisionId);
                cmd.Parameters.AddWithValue("@ModifiedDate", division.ModifiedDate);
                cmd.Parameters.AddWithValue("@CreatedByEmployeeKey", division.CreatedByEmployeeKey);
                cmd.Parameters.AddWithValue("@ModifiedByEmployeeKey", division.ModifiedByEmployeeKey);
                cmd.Parameters.AddWithValue("@FteBudget", (object?)division.FteBudget ?? DBNull.Value);
                cmd.Parameters.AddWithValue("@SuppliesBudget", (object?)division.SuppliesBudget ?? DBNull.Value);
                cmd.Parameters.AddWithValue("@FloorSpaceBudget", (object?)division.FloorSpaceBudget ?? DBNull.Value);
                cmd.Parameters.AddWithValue("@MaxEmployees", (object?)division.MaxEmployees ?? DBNull.Value);
                cmd.Parameters.AddWithValue("@LastReviewCycle", (object?)division.LastReviewCycle ?? DBNull.Value);
                cmd.Parameters.AddWithValue("@StartTime", (object?)division.StartTime ?? DBNull.Value);
                cmd.Parameters.AddWithValue("@SalaryBudget", (object?)division.SalaryBudget ?? DBNull.Value);

                return (int)cmd.ExecuteScalar();
            }
        }

        public int UpsertByPrimaryKey(Division division)
        {
            if (division == null)
                throw new ArgumentNullException(nameof(division), $"{nameof(division)} is null.");

            const string sql = @"MERGE INTO HR.Division target
USING
(
    VALUES
        (@DivisionKey, @DivisionId, @DivisionName, @ModifiedDate, @CreatedByEmployeeKey,
         @ModifiedByEmployeeKey, @SalaryBudget, @FteBudget, @SuppliesBudget, @FloorSpaceBudget, @MaxEmployees,
         @LastReviewCycle, @StartTime
        )
) source (DivisionKey, DivisionId, DivisionName, ModifiedDate, CreatedByEmployeeKey,
          ModifiedByEmployeeKey, SalaryBudget, FteBudget, SuppliesBudget, FloorSpaceBudget, MaxEmployees,
          LastReviewCycle, StartTime
         )
ON target.DivisionKey = source.DivisionKey
WHEN MATCHED THEN
    UPDATE SET DivisionId = source.DivisionId,
               DivisionName = source.DivisionName,
               ModifiedDate = source.ModifiedDate,
               ModifiedByEmployeeKey = source.ModifiedByEmployeeKey,
               SalaryBudget = source.SalaryBudget,
               FteBudget = source.FteBudget,
               SuppliesBudget = source.SuppliesBudget,
               FloorSpaceBudget = source.FloorSpaceBudget,
               MaxEmployees = source.MaxEmployees,
               LastReviewCycle = source.LastReviewCycle,
               StartTime = source.StartTime
WHEN NOT MATCHED THEN
    INSERT
    (
        DivisionId,
        DivisionName,
        CreatedByEmployeeKey,
        ModifiedByEmployeeKey,
        SalaryBudget,
        FteBudget,
        SuppliesBudget,
        FloorSpaceBudget,
        MaxEmployees,
        LastReviewCycle,
        StartTime
    )
    VALUES
    (source.DivisionId, source.DivisionName, source.CreatedByEmployeeKey, source.ModifiedByEmployeeKey,
     source.SalaryBudget, source.FteBudget, source.SuppliesBudget, source.FloorSpaceBudget, source.MaxEmployees,
     source.LastReviewCycle, source.StartTime)
OUTPUT Inserted.DivisionKey;";

            //update audit column
            division.ModifiedDate = DateTime.UtcNow;

            using (var con = OpenConnection())
            using (var cmd = new SqlCommand(sql, con))
            {
                cmd.Parameters.AddWithValue("@DivisionKey", division.DivisionKey);
                cmd.Parameters.AddWithValue("@DivisionName", division.DivisionName);
                cmd.Parameters.AddWithValue("@DivisionId", division.DivisionId);
                cmd.Parameters.AddWithValue("@ModifiedDate", division.ModifiedDate);
                cmd.Parameters.AddWithValue("@CreatedByEmployeeKey", division.CreatedByEmployeeKey);
                cmd.Parameters.AddWithValue("@ModifiedByEmployeeKey", division.ModifiedByEmployeeKey);
                cmd.Parameters.AddWithValue("@FteBudget", (object?)division.FteBudget ?? DBNull.Value);
                cmd.Parameters.AddWithValue("@SuppliesBudget", (object?)division.SuppliesBudget ?? DBNull.Value);
                cmd.Parameters.AddWithValue("@FloorSpaceBudget", (object?)division.FloorSpaceBudget ?? DBNull.Value);
                cmd.Parameters.AddWithValue("@MaxEmployees", (object?)division.MaxEmployees ?? DBNull.Value);
                cmd.Parameters.AddWithValue("@LastReviewCycle", (object?)division.LastReviewCycle ?? DBNull.Value);
                cmd.Parameters.AddWithValue("@StartTime", (object?)division.StartTime ?? DBNull.Value);
                cmd.Parameters.AddWithValue("@SalaryBudget", (object?)division.SalaryBudget ?? DBNull.Value);

                return (int)cmd.ExecuteScalar();
            }
        }
    }

Chain

public class UpsertScenario : IUpsertScenario<Division>
{
    readonly SqlServerDataSource m_DataSource;

    public UpsertScenario(SqlServerDataSource dataSource)
    {
        if (dataSource == null)
            throw new ArgumentNullException(nameof(dataSource), $"{nameof(dataSource)} is null.");

        //Auto-populate the ModifiedDate column
        m_DataSource = dataSource.WithRules(
            new DateTimeRule("ModifiedDate", DateTimeKind.Utc, OperationTypes.Update)
            );
    }

    public Division GetByKey(int divisionKey)
    {
        return m_DataSource.GetByKey<Division>(divisionKey).ToObject().Execute()!;
    }

    public int UpsertByName(Division division)
    {
        //WithKeys indicates that we're matching on something other than the primary key
        return m_DataSource.Upsert(division).WithKeys("DivisionName").ToInt32().Execute();
    }

    public int UpsertByPrimaryKey(Division division)
    {
        return m_DataSource.Upsert(division).ToInt32().Execute();
    }
}

Dapper

    public class UpsertScenario : ScenarioBase, IUpsertScenario<Division>
    {
        public UpsertScenario(string connectionString) : base(connectionString)
        { }

        public Division GetByKey(int divisionKey)
        {
            const string sql = @"SELECT d.DivisionKey,
       d.DivisionId,
       d.DivisionName,
       d.CreatedDate,
       d.ModifiedDate,
       d.CreatedByEmployeeKey,
       d.ModifiedByEmployeeKey,
       d.SalaryBudget,
       d.FteBudget,
       d.SuppliesBudget,
       d.FloorSpaceBudget,
       d.MaxEmployees,
       d.LastReviewCycle,
       d.StartTime FROM HR.Division d WHERE d.DivisionKey = @DivisionKey;";
            using (var con = OpenConnection())
                return con.QuerySingle<Division>(sql, new { divisionKey });
        }

        public int UpsertByName(Division division)
        {
            if (division == null)
                throw new ArgumentNullException(nameof(division), $"{nameof(division)} is null.");

            const string sql = @"MERGE INTO HR.Division target
USING
(
    VALUES
        (@DivisionKey, @DivisionId, @DivisionName, @ModifiedDate, @CreatedByEmployeeKey,
         @ModifiedByEmployeeKey, @SalaryBudget, @FteBudget, @SuppliesBudget, @FloorSpaceBudget, @MaxEmployees,
         @LastReviewCycle, @StartTime
        )
) source (DivisionKey, DivisionId, DivisionName, ModifiedDate, CreatedByEmployeeKey,
          ModifiedByEmployeeKey, SalaryBudget, FteBudget, SuppliesBudget, FloorSpaceBudget, MaxEmployees,
          LastReviewCycle, StartTime
         )
ON target.DivisionName = source.DivisionName
WHEN MATCHED THEN
    UPDATE SET DivisionId = source.DivisionId,
               ModifiedDate = source.ModifiedDate,
               ModifiedByEmployeeKey = source.ModifiedByEmployeeKey,
               SalaryBudget = source.SalaryBudget,
               FteBudget = source.FteBudget,
               SuppliesBudget = source.SuppliesBudget,
               FloorSpaceBudget = source.FloorSpaceBudget,
               MaxEmployees = source.MaxEmployees,
               LastReviewCycle = source.LastReviewCycle,
               StartTime = source.StartTime
WHEN NOT MATCHED THEN
    INSERT
    (
        DivisionId,
        DivisionName,
        CreatedByEmployeeKey,
        ModifiedByEmployeeKey,
        SalaryBudget,
        FteBudget,
        SuppliesBudget,
        FloorSpaceBudget,
        MaxEmployees,
        LastReviewCycle,
        StartTime
    )
    VALUES
    (source.DivisionId, source.DivisionName, source.CreatedByEmployeeKey, source.ModifiedByEmployeeKey,
     source.SalaryBudget, source.FteBudget, source.SuppliesBudget, source.FloorSpaceBudget, source.MaxEmployees,
     source.LastReviewCycle, source.StartTime)
OUTPUT Inserted.DivisionKey;";

            //update audit column
            division.ModifiedDate = DateTime.UtcNow;

            using (var con = OpenConnection())
                return con.ExecuteScalar<int>(sql, division);
        }

        public int UpsertByPrimaryKey(Division division)
        {
            if (division == null)
                throw new ArgumentNullException(nameof(division), $"{nameof(division)} is null.");

            const string sql = @"MERGE INTO HR.Division target
USING
(
    VALUES
        (@DivisionKey, @DivisionId, @DivisionName, @ModifiedDate, @CreatedByEmployeeKey,
         @ModifiedByEmployeeKey, @SalaryBudget, @FteBudget, @SuppliesBudget, @FloorSpaceBudget, @MaxEmployees,
         @LastReviewCycle, @StartTime
        )
) source (DivisionKey, DivisionId, DivisionName, ModifiedDate, CreatedByEmployeeKey,
          ModifiedByEmployeeKey, SalaryBudget, FteBudget, SuppliesBudget, FloorSpaceBudget, MaxEmployees,
          LastReviewCycle, StartTime
         )
ON target.DivisionKey = source.DivisionKey
WHEN MATCHED THEN
    UPDATE SET DivisionId = source.DivisionId,
               DivisionName = source.DivisionName,
               ModifiedDate = source.ModifiedDate,
               ModifiedByEmployeeKey = source.ModifiedByEmployeeKey,
               SalaryBudget = source.SalaryBudget,
               FteBudget = source.FteBudget,
               SuppliesBudget = source.SuppliesBudget,
               FloorSpaceBudget = source.FloorSpaceBudget,
               MaxEmployees = source.MaxEmployees,
               LastReviewCycle = source.LastReviewCycle,
               StartTime = source.StartTime
WHEN NOT MATCHED THEN
    INSERT
    (
        DivisionId,
        DivisionName,
        CreatedByEmployeeKey,
        ModifiedByEmployeeKey,
        SalaryBudget,
        FteBudget,
        SuppliesBudget,
        FloorSpaceBudget,
        MaxEmployees,
        LastReviewCycle,
        StartTime
    )
    VALUES
    (source.DivisionId, source.DivisionName, source.CreatedByEmployeeKey, source.ModifiedByEmployeeKey,
     source.SalaryBudget, source.FteBudget, source.SuppliesBudget, source.FloorSpaceBudget, source.MaxEmployees,
     source.LastReviewCycle, source.StartTime)
OUTPUT Inserted.DivisionKey;";

            //update audit column
            division.ModifiedDate = DateTime.UtcNow;

            using (var con = OpenConnection())
                return con.ExecuteScalar<int>(sql, division);
        }
    }

DbConnector

public class UpsertScenario : ScenarioBase, IUpsertScenario<Division>
{
    public UpsertScenario(string connectionString) : base(connectionString)
    { }

    public Division GetByKey(int divisionKey)
    {
        const string sql = @"SELECT d.DivisionKey,
           d.DivisionId,
           d.DivisionName,
           d.CreatedDate,
           d.ModifiedDate,
           d.CreatedByEmployeeKey,
           d.ModifiedByEmployeeKey,
           d.SalaryBudget,
           d.FteBudget,
           d.SuppliesBudget,
           d.FloorSpaceBudget,
           d.MaxEmployees,
           d.LastReviewCycle,
           d.StartTime FROM HR.Division d WHERE d.DivisionKey = @divisionKey;";

        return DbConnector.ReadSingle<Division>(sql, new { divisionKey }).Execute();
    }

    public int UpsertByName(Division division)
    {
        if (division == null)
            throw new ArgumentNullException(nameof(division), $"{nameof(division)} is null.");

        const string sql = @"MERGE INTO HR.Division target
            USING
            (
                VALUES
                    (@DivisionKey, @DivisionId, @DivisionName, @ModifiedDate, @CreatedByEmployeeKey,
                     @ModifiedByEmployeeKey, @SalaryBudget, @FteBudget, @SuppliesBudget, @FloorSpaceBudget, @MaxEmployees,
                     @LastReviewCycle, @StartTime
                    )
            ) source (DivisionKey, DivisionId, DivisionName, ModifiedDate, CreatedByEmployeeKey,
                      ModifiedByEmployeeKey, SalaryBudget, FteBudget, SuppliesBudget, FloorSpaceBudget, MaxEmployees,
                      LastReviewCycle, StartTime
                     )
            ON target.DivisionName = source.DivisionName
            WHEN MATCHED THEN
                UPDATE SET DivisionId = source.DivisionId,
                           ModifiedDate = source.ModifiedDate,
                           ModifiedByEmployeeKey = source.ModifiedByEmployeeKey,
                           SalaryBudget = source.SalaryBudget,
                           FteBudget = source.FteBudget,
                           SuppliesBudget = source.SuppliesBudget,
                           FloorSpaceBudget = source.FloorSpaceBudget,
                           MaxEmployees = source.MaxEmployees,
                           LastReviewCycle = source.LastReviewCycle,
                           StartTime = source.StartTime
            WHEN NOT MATCHED THEN
                INSERT
                (
                    DivisionId,
                    DivisionName,
                    CreatedByEmployeeKey,
                    ModifiedByEmployeeKey,
                    SalaryBudget,
                    FteBudget,
                    SuppliesBudget,
                    FloorSpaceBudget,
                    MaxEmployees,
                    LastReviewCycle,
                    StartTime
                )
                VALUES
                (source.DivisionId, source.DivisionName, source.CreatedByEmployeeKey, source.ModifiedByEmployeeKey,
                 source.SalaryBudget, source.FteBudget, source.SuppliesBudget, source.FloorSpaceBudget, source.MaxEmployees,
                 source.LastReviewCycle, source.StartTime)
            OUTPUT Inserted.DivisionKey;";

        //update audit column
        var utcNow = DateTime.UtcNow;
        division.CreatedDate = utcNow;
        division.ModifiedDate = utcNow;

        return DbConnector.Scalar<int>(sql, division).Execute();
    }

    public int UpsertByPrimaryKey(Division division)
    {
        if (division == null)
            throw new ArgumentNullException(nameof(division), $"{nameof(division)} is null.");

        const string sql = @"MERGE INTO HR.Division target
            USING
            (
                VALUES
                    (@DivisionKey, @DivisionId, @DivisionName, @ModifiedDate, @CreatedByEmployeeKey,
                     @ModifiedByEmployeeKey, @SalaryBudget, @FteBudget, @SuppliesBudget, @FloorSpaceBudget, @MaxEmployees,
                     @LastReviewCycle, @StartTime
                    )
            ) source (DivisionKey, DivisionId, DivisionName, ModifiedDate, CreatedByEmployeeKey,
                      ModifiedByEmployeeKey, SalaryBudget, FteBudget, SuppliesBudget, FloorSpaceBudget, MaxEmployees,
                      LastReviewCycle, StartTime
                     )
            ON target.DivisionKey = source.DivisionKey
            WHEN MATCHED THEN
                UPDATE SET DivisionId = source.DivisionId,
                           DivisionName = source.DivisionName,
                           ModifiedDate = source.ModifiedDate,
                           ModifiedByEmployeeKey = source.ModifiedByEmployeeKey,
                           SalaryBudget = source.SalaryBudget,
                           FteBudget = source.FteBudget,
                           SuppliesBudget = source.SuppliesBudget,
                           FloorSpaceBudget = source.FloorSpaceBudget,
                           MaxEmployees = source.MaxEmployees,
                           LastReviewCycle = source.LastReviewCycle,
                           StartTime = source.StartTime
            WHEN NOT MATCHED THEN
                INSERT
                (
                    DivisionId,
                    DivisionName,
                    CreatedByEmployeeKey,
                    ModifiedByEmployeeKey,
                    SalaryBudget,
                    FteBudget,
                    SuppliesBudget,
                    FloorSpaceBudget,
                    MaxEmployees,
                    LastReviewCycle,
                    StartTime
                )
                VALUES
                (source.DivisionId, source.DivisionName, source.CreatedByEmployeeKey, source.ModifiedByEmployeeKey,
                 source.SalaryBudget, source.FteBudget, source.SuppliesBudget, source.FloorSpaceBudget, source.MaxEmployees,
                 source.LastReviewCycle, source.StartTime)
            OUTPUT Inserted.DivisionKey;";

        //update audit column
        var utcNow = DateTime.UtcNow;
        division.CreatedDate = utcNow;
        division.ModifiedDate = utcNow;

        return DbConnector.Scalar<int>(sql, division).Execute();
    }
}

Entity Framework 6

EF Core doesn't directly support an atomic upsert, so often a read must proceed the update.

public class UpsertScenario : IUpsertScenario<Division>
{
    private Func<OrmCookbookContext> CreateDbContext;

    public UpsertScenario(Func<OrmCookbookContext> dBContextFactory)
    {
        CreateDbContext = dBContextFactory;
    }

    public Division GetByKey(int divisionKey)
    {
        using (var context = CreateDbContext())
            return context.Division.Find(divisionKey);
    }

    public int UpsertByName(Division division)
    {
        if (division == null)
            throw new ArgumentNullException(nameof(division), $"{nameof(division)} is null.");

        //update audit column
        division.CreatedDate = DateTime.UtcNow;
        division.ModifiedDate = DateTime.UtcNow;

        using (var context = CreateDbContext())
        {
            //check to see if the row already exists
            var actual = context.Division.Where(x => x.DivisionName == division.DivisionName).SingleOrDefault();
            if (actual == null) //Insert
            {
                context.Division.Add(division);
                context.SaveChanges();
                return division.DivisionKey;
            }
            else //Update
            {
                //Copy manually so we don't overwrite CreatedBy/CreatedDate
                actual.Department = division.Department;
                actual.DivisionId = division.DivisionId;
                actual.DivisionName = division.DivisionName;
                actual.FloorSpaceBudget = division.FloorSpaceBudget;
                actual.FteBudget = division.FteBudget;
                actual.LastReviewCycle = division.LastReviewCycle;
                actual.MaxEmployees = division.MaxEmployees;
                actual.ModifiedByEmployeeKey = division.ModifiedByEmployeeKey;
                actual.ModifiedDate = division.ModifiedDate;
                actual.SalaryBudget = division.SalaryBudget;
                actual.StartTime = division.StartTime;
                actual.SuppliesBudget = division.SuppliesBudget;
                context.SaveChanges();
                return actual.DivisionKey;
            }
        }
    }

    public int UpsertByPrimaryKey(Division division)
    {
        if (division == null)
            throw new ArgumentNullException(nameof(division), $"{nameof(division)} is null.");

        //update audit column
        division.CreatedDate = DateTime.UtcNow;
        division.ModifiedDate = DateTime.UtcNow;

        using (var context = CreateDbContext())
        {
            //If DivisionKey is zero, we know this is a new row
            if (division.DivisionKey == 0) //Insert
            {
                context.Division.Add(division);
                context.SaveChanges();
                return division.DivisionKey;
            }
            else //Update
            {
                //This wouldn't be necessary if we were replacing all columns.
                var actual = context.Division.Find(division.DivisionKey);

                //Copy manually so we don't overwrite CreatedBy/CreatedDate
                actual.Department = division.Department;
                actual.DivisionId = division.DivisionId;
                actual.DivisionName = division.DivisionName;
                actual.FloorSpaceBudget = division.FloorSpaceBudget;
                actual.FteBudget = division.FteBudget;
                actual.LastReviewCycle = division.LastReviewCycle;
                actual.MaxEmployees = division.MaxEmployees;
                actual.ModifiedByEmployeeKey = division.ModifiedByEmployeeKey;
                actual.ModifiedDate = division.ModifiedDate;
                actual.SalaryBudget = division.SalaryBudget;
                actual.StartTime = division.StartTime;
                actual.SuppliesBudget = division.SuppliesBudget;
                context.SaveChanges();
                return actual.DivisionKey;
            }
        }
    }
}

Entity Framework Core

EF Core doesn't directly support an atomic upsert, so often a read must proceed the update.

public class UpsertScenario : IUpsertScenario<Division>
{
    private Func<OrmCookbookContext> CreateDbContext;

    public UpsertScenario(Func<OrmCookbookContext> dBContextFactory)
    {
        CreateDbContext = dBContextFactory;
    }

    public Division? GetByKey(int divisionKey)
    {
        using (var context = CreateDbContext())
            return context.Divisions.Find(divisionKey);
    }

    public int UpsertByName(Division division)
    {
        if (division == null)
            throw new ArgumentNullException(nameof(division), $"{nameof(division)} is null.");

        //update audit column
        division.CreatedDate = DateTime.UtcNow;
        division.ModifiedDate = DateTime.UtcNow;

        using (var context = CreateDbContext())
        {
            //check to see if the row already exists
            var actual = context.Divisions.Where(x => x.DivisionName == division.DivisionName).SingleOrDefault();
            if (actual == null) //Insert
            {
                context.Divisions.Add(division);
                context.SaveChanges();
                return division.DivisionKey;
            }
            else //Update
            {
                //Copy manually so we don't overwrite CreatedBy/CreatedDate
                actual.Departments = division.Departments;
                actual.DivisionId = division.DivisionId;
                actual.DivisionName = division.DivisionName;
                actual.FloorSpaceBudget = division.FloorSpaceBudget;
                actual.FteBudget = division.FteBudget;
                actual.LastReviewCycle = division.LastReviewCycle;
                actual.MaxEmployees = division.MaxEmployees;
                actual.ModifiedByEmployeeKey = division.ModifiedByEmployeeKey;
                actual.ModifiedDate = division.ModifiedDate;
                actual.SalaryBudget = division.SalaryBudget;
                actual.StartTime = division.StartTime;
                actual.SuppliesBudget = division.SuppliesBudget;
                context.SaveChanges();
                return actual.DivisionKey;
            }
        }
    }

    public int UpsertByPrimaryKey(Division division)
    {
        if (division == null)
            throw new ArgumentNullException(nameof(division), $"{nameof(division)} is null.");

        //update audit column
        division.CreatedDate = DateTime.UtcNow;
        division.ModifiedDate = DateTime.UtcNow;

        using (var context = CreateDbContext())
        {
            //If DivisionKey is zero, we know this is a new row
            if (division.DivisionKey == 0) //Insert
            {
                context.Divisions.Add(division);
                context.SaveChanges();
                return division.DivisionKey;
            }
            else //Update
            {
                //This wouldn't be necessary if we were replacing all columns.
                var actual = context.Divisions.Find(division.DivisionKey);
                if (actual == null)
                    throw new DataException("Record not found");

                //Copy manually so we don't overwrite CreatedBy/CreatedDate
                actual.Departments = division.Departments;
                actual.DivisionId = division.DivisionId;
                actual.DivisionName = division.DivisionName;
                actual.FloorSpaceBudget = division.FloorSpaceBudget;
                actual.FteBudget = division.FteBudget;
                actual.LastReviewCycle = division.LastReviewCycle;
                actual.MaxEmployees = division.MaxEmployees;
                actual.ModifiedByEmployeeKey = division.ModifiedByEmployeeKey;
                actual.ModifiedDate = division.ModifiedDate;
                actual.SalaryBudget = division.SalaryBudget;
                actual.StartTime = division.StartTime;
                actual.SuppliesBudget = division.SuppliesBudget;
                context.SaveChanges();
                return actual.DivisionKey;
            }
        }
    }
}

LINQ to DB

public class UpsertScenario : IUpsertScenario<Division>
{
    public Division GetByKey(int divisionKey)
    {
        using (var db = new OrmCookbook())
            return db.Division.Where(d => d.DivisionKey == divisionKey).Single();
    }

    public int UpsertByName(Division division)
    {
        if (division == null)
            throw new ArgumentNullException(nameof(division), $"{nameof(division)} is null.");

        //update audit column
        division.CreatedDate = DateTime.UtcNow;
        division.ModifiedDate = DateTime.UtcNow;

        using (var db = new OrmCookbook())
        {
            //check to see if the row already exists
            var actual = db.Division.Where(x => x.DivisionName == division.DivisionName).SingleOrDefault();
            if (actual == null) //Insert
            {
                return db.InsertWithInt32Identity(division);
            }
            else //Update
            {
                //Set fields manually so we don't overwrite CreatedBy/CreatedDate
                db.Division
                    .Where(d => d.DivisionKey == actual.DivisionKey)
                    .Set(d => d.DivisionId, division.DivisionId)
                    .Set(d => d.DivisionName, division.DivisionName)
                    .Set(d => d.FloorSpaceBudget, division.FloorSpaceBudget)
                    .Set(d => d.FteBudget, division.FteBudget)
                    .Set(d => d.LastReviewCycle, division.LastReviewCycle)
                    .Set(d => d.MaxEmployees, division.MaxEmployees)
                    .Set(d => d.ModifiedByEmployeeKey, division.ModifiedByEmployeeKey)
                    .Set(d => d.ModifiedDate, division.ModifiedDate)
                    .Set(d => d.SalaryBudget, division.SalaryBudget)
                    .Set(d => d.StartTime, division.StartTime)
                    .Set(d => d.SuppliesBudget, division.SuppliesBudget)
                    .Update();

                return actual.DivisionKey;
            }
        }
    }

    public int UpsertByPrimaryKey(Division division)
    {
        if (division == null)
            throw new ArgumentNullException(nameof(division), $"{nameof(division)} is null.");

        //update audit column
        division.CreatedDate = DateTime.UtcNow;
        division.ModifiedDate = DateTime.UtcNow;

        using (var db = new OrmCookbook())
        {
            //If DivisionKey is zero, we know this is a new row
            if (division.DivisionKey == 0) //Insert
            {
                return db.InsertWithInt32Identity(division);
            }
            else //Update
            {
                //This wouldn't be necessary if we were replacing all columns.
                var actual = db.Division.Where(x => x.DivisionKey == division.DivisionKey).Single();

                //Set fields manually so we don't overwrite CreatedBy/CreatedDate
                db.Division
                    .Where(d => d.DivisionKey == actual.DivisionKey)
                    .Set(d => d.DivisionId, division.DivisionId)
                    .Set(d => d.DivisionName, division.DivisionName)
                    .Set(d => d.FloorSpaceBudget, division.FloorSpaceBudget)
                    .Set(d => d.FteBudget, division.FteBudget)
                    .Set(d => d.LastReviewCycle, division.LastReviewCycle)
                    .Set(d => d.MaxEmployees, division.MaxEmployees)
                    .Set(d => d.ModifiedByEmployeeKey, division.ModifiedByEmployeeKey)
                    .Set(d => d.ModifiedDate, division.ModifiedDate)
                    .Set(d => d.SalaryBudget, division.SalaryBudget)
                    .Set(d => d.StartTime, division.StartTime)
                    .Set(d => d.SuppliesBudget, division.SuppliesBudget)
                    .Update();

                return actual.DivisionKey;
            }
        }
    }
}

LLBLGen Pro

LLBLGen Pro doesn't support an atomic upsert, so a read must proceed the update.

public class UpsertScenario : IUpsertScenario<DivisionEntity>
{
    public DivisionEntity? GetByKey(int divisionKey)
    {
        using(var adapter = new DataAccessAdapter())
        {
            return new LinqMetaData(adapter).Division.FirstOrDefault(d => d.DivisionKey == divisionKey);
        }
    }


    public int UpsertByName(DivisionEntity division)
    {
        if(division == null)
            throw new ArgumentNullException(nameof(division), $"{nameof(division)} is null.");

        // LLBLGen Pro doesn't support an atomic upsert statement. Instead we'll simply fetch
        // the entity with the name specified. If it's there, we'll get a filled entity instance
        // which we'll update with the new values (and only the values we then change will be updated
        // in the database), or it'll be a new one, which we'll set to new values and it'll be 
        // inserted. 

        using(var adapter = new DataAccessAdapter())
        {
            var toSave = adapter.FetchNewEntity<DivisionEntity>(
                                    new RelationPredicateBucket(DivisionFields.DivisionName.Equal(division.DivisionName)));

            // now we'll just set fields and persist it again. If the above fetch returned an existing entity
            // it'll be updated, otherwise it'll be inserted.
            // If the above fetch returned an existing entity, only the fields which changed are updated
            // in the update query.
            toSave.CreatedDate = DateTime.UtcNow;
            toSave.ModifiedDate = DateTime.UtcNow;
            toSave.CreatedByEmployeeKey = division.CreatedByEmployeeKey;
            toSave.ModifiedByEmployeeKey = division.ModifiedByEmployeeKey;
            toSave.DivisionId = division.DivisionId;
            toSave.DivisionName = division.DivisionName;
            toSave.FloorSpaceBudget = division.FloorSpaceBudget;
            toSave.FteBudget = division.FteBudget;
            toSave.LastReviewCycle = division.LastReviewCycle;
            toSave.MaxEmployees = division.MaxEmployees;
            toSave.ModifiedByEmployeeKey = division.ModifiedByEmployeeKey;
            toSave.ModifiedDate = division.ModifiedDate;
            toSave.SalaryBudget = division.SalaryBudget;
            toSave.StartTime = division.StartTime;
            toSave.SuppliesBudget = division.SuppliesBudget;
            adapter.SaveEntity(toSave);
            return toSave.DivisionKey;
        }
    }


    public int UpsertByPrimaryKey(DivisionEntity division)
    {
        if(division == null)
            throw new ArgumentNullException(nameof(division), $"{nameof(division)} is null.");

        // LLBLGen Pro doesn't support an atomic upsert statement. Instead we'll simply fetch
        // the entity with the name specified. If it's there, we'll get a filled entity instance
        // which we'll update with the new values (and only the values we then change will be updated
        // in the database), or it'll be a new one, which we'll set to new values and it'll be 
        // inserted. 

        using(var adapter = new DataAccessAdapter())
        {
            var toSave = adapter.FetchNewEntity<DivisionEntity>(
                                    new RelationPredicateBucket(DivisionFields.DivisionKey.Equal(division.DivisionKey)));

            // now we'll just set fields and persist it again. If the above fetch returned an existing entity
            // it'll be updated, otherwise it'll be inserted.
            // If the above fetch returned an existing entity, only the fields which changed are updated
            // in the update query.
            toSave.CreatedDate = DateTime.UtcNow;
            toSave.ModifiedDate = DateTime.UtcNow;
            toSave.CreatedByEmployeeKey = division.CreatedByEmployeeKey;
            toSave.ModifiedByEmployeeKey = division.ModifiedByEmployeeKey;
            toSave.DivisionId = division.DivisionId;
            toSave.DivisionName = division.DivisionName;
            toSave.FloorSpaceBudget = division.FloorSpaceBudget;
            toSave.FteBudget = division.FteBudget;
            toSave.LastReviewCycle = division.LastReviewCycle;
            toSave.MaxEmployees = division.MaxEmployees;
            toSave.ModifiedByEmployeeKey = division.ModifiedByEmployeeKey;
            toSave.ModifiedDate = division.ModifiedDate;
            toSave.SalaryBudget = division.SalaryBudget;
            toSave.StartTime = division.StartTime;
            toSave.SuppliesBudget = division.SuppliesBudget;
            adapter.SaveEntity(toSave);
            return toSave.DivisionKey;
        }
    }
}

NHibernate

NHibernate doesn't directly support an atomic upsert, so often a read must proceed the update.

public class UpsertScenario : IUpsertScenario<Division>
{
    readonly ISessionFactory m_SessionFactory;

    public UpsertScenario(ISessionFactory sessionFactory)
    {
        m_SessionFactory = sessionFactory;
    }

    public Division GetByKey(int divisionKey)
    {
        using (var session = m_SessionFactory.OpenSession())
            return session.Get<Division>(divisionKey);
    }

    public int UpsertByName(Division division)
    {
        if (division == null)
            throw new ArgumentNullException(nameof(division), $"{nameof(division)} is null.");

        //update audit column
        division.CreatedDate = DateTime.UtcNow;
        division.ModifiedDate = DateTime.UtcNow;

        using (var session = m_SessionFactory.OpenSession())
        {
            //check to see if the row already exists
            var actual = session.Query<Division>().Where(x => x.DivisionName == division.DivisionName).SingleOrDefault();
            if (actual == null) //Insert
            {
                session.Save(division);
                session.Flush();
                return division.DivisionKey;
            }
            else //Update
            {
                //Copy manually so we don't overwrite CreatedBy/CreatedDate
                actual.DivisionId = division.DivisionId;
                actual.DivisionName = division.DivisionName;
                actual.FloorSpaceBudget = division.FloorSpaceBudget;
                actual.FteBudget = division.FteBudget;
                actual.LastReviewCycle = division.LastReviewCycle;
                actual.MaxEmployees = division.MaxEmployees;
                actual.ModifiedByEmployeeKey = division.ModifiedByEmployeeKey;
                actual.ModifiedDate = division.ModifiedDate;
                actual.SalaryBudget = division.SalaryBudget;
                actual.StartTime = division.StartTime;
                actual.SuppliesBudget = division.SuppliesBudget;
                session.Flush();
                return actual.DivisionKey;
            }
        }
    }

    public int UpsertByPrimaryKey(Division division)
    {
        if (division == null)
            throw new ArgumentNullException(nameof(division), $"{nameof(division)} is null.");

        //update audit column
        division.CreatedDate = DateTime.UtcNow;
        division.ModifiedDate = DateTime.UtcNow;

        using (var session = m_SessionFactory.OpenSession())
        {
            //If DivisionKey is zero, we know this is a new row
            if (division.DivisionKey == 0) //Insert
            {
                session.Save(division);
                session.Flush();
                return division.DivisionKey;
            }
            else //Update
            {
                //This wouldn't be necessary if we were replacing all columns.
                var actual = session.Get<Division>(division.DivisionKey);

                //Copy manually so we don't overwrite CreatedBy/CreatedDate
                actual.DivisionId = division.DivisionId;
                actual.DivisionName = division.DivisionName;
                actual.FloorSpaceBudget = division.FloorSpaceBudget;
                actual.FteBudget = division.FteBudget;
                actual.LastReviewCycle = division.LastReviewCycle;
                actual.MaxEmployees = division.MaxEmployees;
                actual.ModifiedByEmployeeKey = division.ModifiedByEmployeeKey;
                actual.ModifiedDate = division.ModifiedDate;
                actual.SalaryBudget = division.SalaryBudget;
                actual.StartTime = division.StartTime;
                actual.SuppliesBudget = division.SuppliesBudget;
                session.Flush();
                return actual.DivisionKey;
            }
        }
    }
}

RepoDb

public class UpsertScenario : BaseRepository<Division, SqlConnection>,
    IUpsertScenario<Division>
{
    public UpsertScenario(string connectionString)
        : base(connectionString, RDB.Enumerations.ConnectionPersistency.Instance)
    { }

    public Division? GetByKey(int divisionKey)
    {
        return Query(e => e.DivisionKey == divisionKey).FirstOrDefault();
    }

    public int UpsertByName(Division division)
    {
        if (division == null)
            throw new ArgumentNullException(nameof(division), $"{nameof(division)} is null.");

        return Merge<int>(division, qualifiers: Field.From("DivisionName"));
    }

    public int UpsertByPrimaryKey(Division division)
    {
        if (division == null)
            throw new ArgumentNullException(nameof(division), $"{nameof(division)} is null.");

        return Merge<int>(division, qualifiers: Field.From("DivisionKey"));
    }
}

ServiceStack

public class UpsertScenario : IUpsertScenario<Division>
{
    private IDbConnectionFactory _dbConnectionFactory;

    public UpsertScenario(IDbConnectionFactory dbConnectionFactory)
    {
        this._dbConnectionFactory = dbConnectionFactory;
    }

    public Division GetByKey(int divisionKey)
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            return db.SingleById<Division>(divisionKey);
        }
    }

    public int UpsertByName(Division division)
    {
        if (division == null)
            throw new ArgumentNullException(nameof(division), $"{nameof(division)} is null.");

        if (division.CreatedDate == default(DateTime))
            division.CreatedDate = DateTime.UtcNow;
        division.ModifiedDate = DateTime.UtcNow;
        
        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            var existing = db.Single<Division>(c => c.DivisionName == division.DivisionName);
            if (existing != null)
            {
                division.PopulateWith(existing.PopulateWithNonDefaultValues(division));
            }
            db.Save(division);
        }
        
        return division.Id;
    }

    public int UpsertByPrimaryKey(Division division)
    {
        if (division == null)
            throw new ArgumentNullException(nameof(division), $"{nameof(division)} is null.");

        if (division.CreatedDate == default(DateTime))
            division.CreatedDate = DateTime.UtcNow;
        division.ModifiedDate = DateTime.UtcNow;
        
        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            db.Save(division);
        }

        return division.Id;
    }
}