CRUD Operations on Multiple Objects

These scenarios demonstrate how to perform Create, Read, Update, and Delete operations on a collection of 100 objects.

If the ORM supports it, the operation should be performed with a single SQL statement.

Scenario Prototype

public interface IMultipleCrudScenario<TEmployeeSimple>
   where TEmployeeSimple : class, IEmployeeSimple, new()
{
    /// <summary>
    /// Delete a collection of Employee rows.
    /// </summary>
    void DeleteBatch(IList<TEmployeeSimple> employees);

    /// <summary>
    /// Delete a collection of Employee rows by key.
    /// </summary>
    void DeleteBatchByKey(IList<int> employeeKeys);

    /// <summary>
    /// Gets a collection of Employee rows by their name. Assume the name is not unique.
    /// </summary>
    IList<TEmployeeSimple> FindByLastName(string lastName);

    /// <summary>
    /// Insert a collection of Employee rows.
    /// </summary>
    void InsertBatch(IList<TEmployeeSimple> employees);

    /// <summary>
    /// Insert a collection of Employee rows, returning the newly created keys.
    /// </summary>
    IList<int> InsertBatchReturnKeys(IList<TEmployeeSimple> employees);

    /// <summary>
    /// Insert a collection of Employee rows, returning the newly created rows.
    /// </summary>
    /// <remarks>This MAY return the original objects or new objects.</remarks>
    IList<TEmployeeSimple> InsertBatchReturnRows(IList<TEmployeeSimple> employees);

    /// <summary>
    /// Insert a collection of Employee rows and update the original objects with the new keys.
    /// </summary>
    void InsertBatchWithRefresh(IList<TEmployeeSimple> employees);

    /// <summary>
    /// Update a collection of Employee rows.
    /// </summary>
    void UpdateBatch(IList<TEmployeeSimple> employees);
}

ADO.NET

    public class MultipleCrudScenario : SqlServerScenarioBase, IMultipleCrudScenario<EmployeeSimple>
    {
        public MultipleCrudScenario(string connectionString) : base(connectionString)
        { }

        public void DeleteBatch(IList<EmployeeSimple> employees)
        {
            if (employees == null || employees.Count == 0)
                throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

            var keyList = string.Join(", ", employees.Select(x => x.EmployeeKey));
            var sql = $"DELETE FROM HR.Employee WHERE EmployeeKey IN ({keyList});";

            using (var con = OpenConnection())
            using (var cmd = new SqlCommand(sql, con))
                cmd.ExecuteNonQuery();
        }

        public void DeleteBatchByKey(IList<int> employeeKeys)
        {
            if (employeeKeys == null || employeeKeys.Count == 0)
                throw new ArgumentException($"{nameof(employeeKeys)} is null or empty.", nameof(employeeKeys));

            var keyList = string.Join(", ", employeeKeys);
            var sql = $"DELETE FROM HR.Employee WHERE EmployeeKey IN ({keyList});";

            using (var con = OpenConnection())
            using (var cmd = new SqlCommand(sql, con))
                cmd.ExecuteNonQuery();
        }

        public IList<EmployeeSimple> FindByLastName(string lastName)
        {
            const string sql = "SELECT e.EmployeeKey, e.FirstName, e.MiddleName, e.LastName, e.Title, e.OfficePhone, e.CellPhone, e.EmployeeClassificationKey FROM HR.EmployeeDetail e WHERE e.LastName = @LastName";

            using (var con = OpenConnection())
            using (var cmd = new SqlCommand(sql, con))
            {
                cmd.Parameters.AddWithValue("@LastName", lastName);

                var results = new List<EmployeeSimple>();

                using (var reader = cmd.ExecuteReader())
                    while (reader.Read())
                        results.Add(new EmployeeSimple(reader));

                return results;
            }
        }

        public void InsertBatch(IList<EmployeeSimple> employees)
        {
            if (employees == null || employees.Count == 0)
                throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

            var sql = new StringBuilder(@"INSERT INTO HR.Employee
(FirstName, MiddleName, LastName, Title, OfficePhone, CellPhone, EmployeeClassificationKey)
VALUES ");

            for (var i = 0; i < employees.Count; i++)
            {
                if (i != 0)
                    sql.AppendLine(",");
                sql.Append($"(@FirstName_{i}, @MiddleName_{i}, @LastName_{i}, @Title_{i}, @OfficePhone_{i}, @CellPhone_{i}, @EmployeeClassificationKey_{i})");
            }
            sql.AppendLine(";");

            //No transaction is needed because a single SQL statement is used.
            using (var con = OpenConnection())
            using (var cmd = new SqlCommand(sql.ToString(), con))
            {
                for (var i = 0; i < employees.Count; i++)
                {
                    cmd.Parameters.AddWithValue($"@FirstName_{i}", employees[i].FirstName);
                    cmd.Parameters.AddWithValue($"@MiddleName_{i}", (object?)employees[i].MiddleName ?? DBNull.Value);
                    cmd.Parameters.AddWithValue($"@LastName_{i}", employees[i].LastName);
                    cmd.Parameters.AddWithValue($"@Title_{i}", (object?)employees[i].Title ?? DBNull.Value);
                    cmd.Parameters.AddWithValue($"@OfficePhone_{i}", (object?)employees[i].OfficePhone ?? DBNull.Value);
                    cmd.Parameters.AddWithValue($"@CellPhone_{i}", (object?)employees[i].CellPhone ?? DBNull.Value);
                    cmd.Parameters.AddWithValue($"@EmployeeClassificationKey_{i}", employees[i].EmployeeClassificationKey);
                }
                cmd.ExecuteNonQuery();
            }
        }

        public IList<int> InsertBatchReturnKeys(IList<EmployeeSimple> employees)
        {
            if (employees == null || employees.Count == 0)
                throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

            var sql = new StringBuilder(@"INSERT INTO HR.Employee
(FirstName, MiddleName, LastName, Title, OfficePhone, CellPhone, EmployeeClassificationKey)
OUTPUT Inserted.EmployeeKey
VALUES ");

            for (var i = 0; i < employees.Count; i++)
            {
                if (i != 0)
                    sql.AppendLine(",");
                sql.Append($"(@FirstName_{i}, @MiddleName_{i}, @LastName_{i}, @Title_{i}, @OfficePhone_{i}, @CellPhone_{i}, @EmployeeClassificationKey_{i})");
            }
            sql.AppendLine(";");

            //No transaction is needed because a single SQL statement is used.
            using (var con = OpenConnection())
            using (var cmd = new SqlCommand(sql.ToString(), con))
            {
                for (var i = 0; i < employees.Count; i++)
                {
                    cmd.Parameters.AddWithValue($"@FirstName_{i}", employees[i].FirstName);
                    cmd.Parameters.AddWithValue($"@MiddleName_{i}", (object?)employees[i].MiddleName ?? DBNull.Value);
                    cmd.Parameters.AddWithValue($"@LastName_{i}", employees[i].LastName);
                    cmd.Parameters.AddWithValue($"@Title_{i}", (object?)employees[i].Title ?? DBNull.Value);
                    cmd.Parameters.AddWithValue($"@OfficePhone_{i}", (object?)employees[i].OfficePhone ?? DBNull.Value);
                    cmd.Parameters.AddWithValue($"@CellPhone_{i}", (object?)employees[i].CellPhone ?? DBNull.Value);
                    cmd.Parameters.AddWithValue($"@EmployeeClassificationKey_{i}", employees[i].EmployeeClassificationKey);
                }
                var result = new List<int>();
                using (var reader = cmd.ExecuteReader())
                    while (reader.Read())
                        result.Add(reader.GetInt32(0));
                return result;
            }
        }

        public IList<EmployeeSimple> InsertBatchReturnRows(IList<EmployeeSimple> employees)
        {
            if (employees == null || employees.Count == 0)
                throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

            var sql = new StringBuilder(@"INSERT INTO HR.Employee
(FirstName, MiddleName, LastName, Title, OfficePhone, CellPhone, EmployeeClassificationKey)
OUTPUT Inserted.EmployeeKey, Inserted.FirstName, Inserted.MiddleName, Inserted.LastName, Inserted.Title, Inserted.OfficePhone, Inserted.CellPhone, Inserted.EmployeeClassificationKey
VALUES ");

            for (var i = 0; i < employees.Count; i++)
            {
                if (i != 0)
                    sql.AppendLine(",");
                sql.Append($"(@FirstName_{i}, @MiddleName_{i}, @LastName_{i}, @Title_{i}, @OfficePhone_{i}, @CellPhone_{i}, @EmployeeClassificationKey_{i})");
            }
            sql.AppendLine(";");

            //No transaction is needed because a single SQL statement is used.
            using (var con = OpenConnection())
            using (var cmd = new SqlCommand(sql.ToString(), con))
            {
                for (var i = 0; i < employees.Count; i++)
                {
                    cmd.Parameters.AddWithValue($"@FirstName_{i}", employees[i].FirstName);
                    cmd.Parameters.AddWithValue($"@MiddleName_{i}", (object?)employees[i].MiddleName ?? DBNull.Value);
                    cmd.Parameters.AddWithValue($"@LastName_{i}", employees[i].LastName);
                    cmd.Parameters.AddWithValue($"@Title_{i}", (object?)employees[i].Title ?? DBNull.Value);
                    cmd.Parameters.AddWithValue($"@OfficePhone_{i}", (object?)employees[i].OfficePhone ?? DBNull.Value);
                    cmd.Parameters.AddWithValue($"@CellPhone_{i}", (object?)employees[i].CellPhone ?? DBNull.Value);
                    cmd.Parameters.AddWithValue($"@EmployeeClassificationKey_{i}", employees[i].EmployeeClassificationKey);
                }
                var result = new List<EmployeeSimple>();
                using (var reader = cmd.ExecuteReader())
                    while (reader.Read())
                        result.Add(new EmployeeSimple(reader));
                return result;
            }
        }

        public void InsertBatchWithRefresh(IList<EmployeeSimple> employees)
        {
            if (employees == null || employees.Count == 0)
                throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

            var sql = new StringBuilder();

            //In order to ensure the right objects are refreshed, each object is inserted separately.
            //If we returned them all at the same time, they might not come back in the same order.
            for (var i = 0; i < employees.Count; i++)
            {
                sql.AppendLine($@"INSERT INTO HR.Employee
(FirstName, MiddleName, LastName, Title, OfficePhone, CellPhone, EmployeeClassificationKey)
OUTPUT Inserted.EmployeeKey, Inserted.FirstName, Inserted.MiddleName, Inserted.LastName, Inserted.Title, Inserted.OfficePhone, Inserted.CellPhone, Inserted.EmployeeClassificationKey
VALUES (@FirstName_{i}, @MiddleName_{i}, @LastName_{i}, @Title_{i}, @OfficePhone_{i}, @CellPhone_{i}, @EmployeeClassificationKey_{i});");
            }

            //A transaction is needed because this example uses multiple SQL statements.
            using (var con = OpenConnection())
            using (var trans = con.BeginTransaction())
            {
                using (var cmd = new SqlCommand(sql.ToString(), con, trans))
                {
                    for (var i = 0; i < employees.Count; i++)
                    {
                        cmd.Parameters.AddWithValue($"@FirstName_{i}", employees[i].FirstName);
                        cmd.Parameters.AddWithValue($"@MiddleName_{i}", (object?)employees[i].MiddleName ?? DBNull.Value);
                        cmd.Parameters.AddWithValue($"@LastName_{i}", employees[i].LastName);
                        cmd.Parameters.AddWithValue($"@Title_{i}", (object?)employees[i].Title ?? DBNull.Value);
                        cmd.Parameters.AddWithValue($"@OfficePhone_{i}", (object?)employees[i].OfficePhone ?? DBNull.Value);
                        cmd.Parameters.AddWithValue($"@CellPhone_{i}", (object?)employees[i].CellPhone ?? DBNull.Value);
                        cmd.Parameters.AddWithValue($"@EmployeeClassificationKey_{i}", employees[i].EmployeeClassificationKey);
                    }

                    using (var reader = cmd.ExecuteReader())
                    {
                        for (var i = 0; i < employees.Count; i++)
                        {
                            reader.Read();
                            employees[i].Refresh(reader);
                            reader.NextResult(); //each row is coming back as a separate result set
                        }
                    }
                }
                trans.Commit();
            }
        }

        public void UpdateBatch(IList<EmployeeSimple> employees)
        {
            if (employees == null || employees.Count == 0)
                throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

            var sql = new StringBuilder();

            for (var i = 0; i < employees.Count; i++)
            {
                sql.AppendLine($@"UPDATE HR.Employee
SET FirstName = @FirstName_{i},
    MiddleName = @MiddleName_{i},
    LastName = @LastName_{i},
    Title = @Title_{i},
    OfficePhone = @OfficePhone_{i},
    CellPhone = @CellPhone_{i},
    EmployeeClassificationKey = @EmployeeClassificationKey_{i}
WHERE EmployeeKey = @EmployeeKey_{i};");
            }

            //A transaction is needed because this example uses multiple SQL statements.
            using (var con = OpenConnection())
            using (var trans = con.BeginTransaction())
            {
                using (var cmd = new SqlCommand(sql.ToString(), con, trans))
                {
                    for (var i = 0; i < employees.Count; i++)
                    {
                        cmd.Parameters.AddWithValue($"@EmployeeKey_{i}", employees[i].EmployeeKey);
                        cmd.Parameters.AddWithValue($"@FirstName_{i}", employees[i].FirstName);
                        cmd.Parameters.AddWithValue($"@MiddleName_{i}", (object?)employees[i].MiddleName ?? DBNull.Value);
                        cmd.Parameters.AddWithValue($"@LastName_{i}", employees[i].LastName);
                        cmd.Parameters.AddWithValue($"@Title_{i}", (object?)employees[i].Title ?? DBNull.Value);
                        cmd.Parameters.AddWithValue($"@OfficePhone_{i}", (object?)employees[i].OfficePhone ?? DBNull.Value);
                        cmd.Parameters.AddWithValue($"@CellPhone_{i}", (object?)employees[i].CellPhone ?? DBNull.Value);
                        cmd.Parameters.AddWithValue($"@EmployeeClassificationKey_{i}", employees[i].EmployeeClassificationKey);
                    }
                    cmd.ExecuteNonQuery();
                }
                trans.Commit();
            }
        }
    }

Chain

public class MultipleCrudScenario : IMultipleCrudScenario<EmployeeSimple>
{
    const string EmployeeTableName = "HR.Employee";
    readonly SqlServerDataSource m_DataSource;

    public MultipleCrudScenario(SqlServerDataSource dataSource)
    {
        m_DataSource = dataSource;
    }

    public void DeleteBatch(IList<EmployeeSimple> employees)
    {
        if (employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        m_DataSource.DeleteByKeyList(EmployeeTableName, employees.Select(x => x.EmployeeKey)).Execute();
    }

    public void DeleteBatchByKey(IList<int> employeeKeys)
    {
        if (employeeKeys == null || employeeKeys.Count == 0)
            throw new ArgumentException($"{nameof(employeeKeys)} is null or empty.", nameof(employeeKeys));

        m_DataSource.DeleteByKeyList(EmployeeTableName, employeeKeys).Execute();
    }

    public IList<EmployeeSimple> FindByLastName(string lastName)
    {
        return m_DataSource.From<EmployeeSimple>(new { lastName }).ToCollection().Execute();
    }

    public void InsertBatch(IList<EmployeeSimple> employees)
    {
        if (employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        m_DataSource.InsertBatch((IReadOnlyList<EmployeeSimple>)employees).Execute();
    }

    public IList<int> InsertBatchReturnKeys(IList<EmployeeSimple> employees)
    {
        if (employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        return m_DataSource.InsertBatch((IReadOnlyList<EmployeeSimple>)employees).ToInt32List().Execute();
    }

    public IList<EmployeeSimple> InsertBatchReturnRows(IList<EmployeeSimple> employees)
    {
        if (employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        return m_DataSource.InsertBatch((IReadOnlyList<EmployeeSimple>)employees).ToCollection<EmployeeSimple>().Execute();
    }

    public void InsertBatchWithRefresh(IList<EmployeeSimple> employees)
    {
        if (employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        //Chain does not support updating multiple rows at one time from objects
        //Use the WithRefresh() link to update the original object.
        using (var trans = m_DataSource.BeginTransaction())
        {
            foreach (var item in employees)
                trans.Insert(item).WithRefresh().Execute();

            trans.Commit();
        }
    }

    public void UpdateBatch(IList<EmployeeSimple> employees)
    {
        if (employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        //Chain does not support updating multiple rows at one time from objects
        using (var trans = m_DataSource.BeginTransaction())
        {
            foreach (var item in employees)
                trans.Update(item).Execute();

            trans.Commit();
        }
    }
}

Dapper

    public class MultipleCrudScenario : ScenarioBase, IMultipleCrudScenario<EmployeeSimple>
    {
        public MultipleCrudScenario(string connectionString) : base(connectionString)
        { }

        virtual public void DeleteBatch(IList<EmployeeSimple> employees)
        {
            if (employees == null || employees.Count == 0)
                throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

            var keyList = string.Join(", ", employees.Select(x => x.EmployeeKey));
            var sql = $"DELETE FROM HR.Employee WHERE EmployeeKey IN ({keyList});";

            using (var con = OpenConnection())
                con.Execute(sql);
        }

        public void DeleteBatchByKey(IList<int> employeeKeys)
        {
            if (employeeKeys == null || employeeKeys.Count == 0)
                throw new ArgumentException($"{nameof(employeeKeys)} is null or empty.", nameof(employeeKeys));

            var keyList = string.Join(", ", employeeKeys);
            var sql = $"DELETE FROM HR.Employee WHERE EmployeeKey IN ({keyList});";

            using (var con = OpenConnection())
                con.Execute(sql);
        }

        public IList<EmployeeSimple> FindByLastName(string lastName)
        {
            const string sql = "SELECT e.EmployeeKey, e.FirstName, e.MiddleName, e.LastName, e.Title, e.OfficePhone, e.CellPhone, e.EmployeeClassificationKey FROM HR.EmployeeDetail e WHERE e.LastName = @LastName";

            using (var con = OpenConnection())
                return con.Query<EmployeeSimple>(sql, new { lastName }).ToList();
        }

        virtual public void InsertBatch(IList<EmployeeSimple> employees)
        {
            if (employees == null || employees.Count == 0)
                throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

            var sql = new StringBuilder(@"INSERT INTO HR.Employee
(FirstName, MiddleName, LastName, Title, OfficePhone, CellPhone, EmployeeClassificationKey)
VALUES ");
            var parameters = new Dictionary<string, object?>();
            for (var i = 0; i < employees.Count; i++)
            {
                if (i != 0)
                    sql.AppendLine(",");
                sql.Append($"(@FirstName_{i}, @MiddleName_{i}, @LastName_{i}, @Title_{i}, @OfficePhone_{i}, @CellPhone_{i}, @EmployeeClassificationKey_{i})");

                parameters[$"@FirstName_{i}"] = employees[i].FirstName;
                parameters[$"@MiddleName_{i}"] = employees[i].MiddleName;
                parameters[$"@LastName_{i}"] = employees[i].LastName;
                parameters[$"@Title_{i}"] = employees[i].Title;
                parameters[$"@OfficePhone_{i}"] = employees[i].OfficePhone;
                parameters[$"@CellPhone_{i}"] = employees[i].CellPhone;
                parameters[$"@EmployeeClassificationKey_{i}"] = employees[i].EmployeeClassificationKey;
            }
            sql.AppendLine(";");

            //No transaction is needed because a single SQL statement is used.
            using (var con = OpenConnection())
                con.Execute(sql.ToString(), parameters);
        }

        public IList<int> InsertBatchReturnKeys(IList<EmployeeSimple> employees)
        {
            if (employees == null || employees.Count == 0)
                throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

            var sql = new StringBuilder(@"INSERT INTO HR.Employee
(FirstName, MiddleName, LastName, Title, OfficePhone, CellPhone, EmployeeClassificationKey)
OUTPUT Inserted.EmployeeKey
VALUES ");

            var parameters = new Dictionary<string, object?>();
            for (var i = 0; i < employees.Count; i++)
            {
                if (i != 0)
                    sql.AppendLine(",");
                sql.Append($"(@FirstName_{i}, @MiddleName_{i}, @LastName_{i}, @Title_{i}, @OfficePhone_{i}, @CellPhone_{i}, @EmployeeClassificationKey_{i})");

                parameters[$"@FirstName_{i}"] = employees[i].FirstName;
                parameters[$"@MiddleName_{i}"] = employees[i].MiddleName;
                parameters[$"@LastName_{i}"] = employees[i].LastName;
                parameters[$"@Title_{i}"] = employees[i].Title;
                parameters[$"@OfficePhone_{i}"] = employees[i].OfficePhone;
                parameters[$"@CellPhone_{i}"] = employees[i].CellPhone;
                parameters[$"@EmployeeClassificationKey_{i}"] = employees[i].EmployeeClassificationKey;
            }
            sql.AppendLine(";");

            //No transaction is needed because a single SQL statement is used.
            using (var con = OpenConnection())
                return con.Query<int>(sql.ToString(), parameters).ToList();
        }

        public IList<EmployeeSimple> InsertBatchReturnRows(IList<EmployeeSimple> employees)
        {
            if (employees == null || employees.Count == 0)
                throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

            var sql = new StringBuilder(@"INSERT INTO HR.Employee
(FirstName, MiddleName, LastName, Title, OfficePhone, CellPhone, EmployeeClassificationKey)
OUTPUT Inserted.EmployeeKey, Inserted.FirstName, Inserted.MiddleName, Inserted.LastName, Inserted.Title, Inserted.OfficePhone, Inserted.CellPhone, Inserted.EmployeeClassificationKey
VALUES ");

            var parameters = new Dictionary<string, object?>();
            for (var i = 0; i < employees.Count; i++)
            {
                if (i != 0)
                    sql.AppendLine(",");
                sql.Append($"(@FirstName_{i}, @MiddleName_{i}, @LastName_{i}, @Title_{i}, @OfficePhone_{i}, @CellPhone_{i}, @EmployeeClassificationKey_{i})");

                parameters[$"@FirstName_{i}"] = employees[i].FirstName;
                parameters[$"@MiddleName_{i}"] = employees[i].MiddleName;
                parameters[$"@LastName_{i}"] = employees[i].LastName;
                parameters[$"@Title_{i}"] = employees[i].Title;
                parameters[$"@OfficePhone_{i}"] = employees[i].OfficePhone;
                parameters[$"@CellPhone_{i}"] = employees[i].CellPhone;
                parameters[$"@EmployeeClassificationKey_{i}"] = employees[i].EmployeeClassificationKey;
            }
            sql.AppendLine(";");

            //No transaction is needed because a single SQL statement is used.
            using (var con = OpenConnection())
                return con.Query<EmployeeSimple>(sql.ToString(), parameters).ToList();
        }

        public void InsertBatchWithRefresh(IList<EmployeeSimple> employees)
        {
            if (employees == null || employees.Count == 0)
                throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

            var sql = new StringBuilder();

            //In order to ensure the right objects are refreshed, each object is inserted separately.
            //If we returned them all at the same time, they might not come back in the same order.
            var parameters = new Dictionary<string, object?>();
            for (var i = 0; i < employees.Count; i++)
            {
                sql.AppendLine($@"INSERT INTO HR.Employee
(FirstName, MiddleName, LastName, Title, OfficePhone, CellPhone, EmployeeClassificationKey)
OUTPUT Inserted.EmployeeKey, Inserted.FirstName, Inserted.MiddleName, Inserted.LastName, Inserted.Title, Inserted.OfficePhone, Inserted.CellPhone, Inserted.EmployeeClassificationKey
VALUES (@FirstName_{i}, @MiddleName_{i}, @LastName_{i}, @Title_{i}, @OfficePhone_{i}, @CellPhone_{i}, @EmployeeClassificationKey_{i});");

                parameters[$"@FirstName_{i}"] = employees[i].FirstName;
                parameters[$"@MiddleName_{i}"] = employees[i].MiddleName;
                parameters[$"@LastName_{i}"] = employees[i].LastName;
                parameters[$"@Title_{i}"] = employees[i].Title;
                parameters[$"@OfficePhone_{i}"] = employees[i].OfficePhone;
                parameters[$"@CellPhone_{i}"] = employees[i].CellPhone;
                parameters[$"@EmployeeClassificationKey_{i}"] = employees[i].EmployeeClassificationKey;
            }

            //A transaction is needed because this example uses multiple SQL statements.
            using (var con = OpenConnection())
            using (var trans = con.BeginTransaction())
            {
                var results = con.QueryMultiple(sql.ToString(), parameters, transaction: trans);

                for (var i = 0; i < employees.Count; i++)
                {
                    var temp = results.ReadSingle<EmployeeSimple>(); //each row is coming back as a separate result set
                    employees[i].Refresh(temp);
                }
            }
        }

        virtual public void UpdateBatch(IList<EmployeeSimple> employees)
        {
            if (employees == null || employees.Count == 0)
                throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

            var sql = new StringBuilder();

            var parameters = new Dictionary<string, object?>();
            for (var i = 0; i < employees.Count; i++)
            {
                sql.AppendLine($@"UPDATE HR.Employee
SET FirstName = @FirstName_{i},
    MiddleName = @MiddleName_{i},
    LastName = @LastName_{i},
    Title = @Title_{i},
    OfficePhone = @OfficePhone_{i},
    CellPhone = @CellPhone_{i},
    EmployeeClassificationKey = @EmployeeClassificationKey_{i}
WHERE EmployeeKey = @EmployeeKey_{i};");

                parameters[$"@EmployeeKey_{i}"] = employees[i].EmployeeKey;
                parameters[$"@FirstName_{i}"] = employees[i].FirstName;
                parameters[$"@MiddleName_{i}"] = employees[i].MiddleName;
                parameters[$"@LastName_{i}"] = employees[i].LastName;
                parameters[$"@Title_{i}"] = employees[i].Title;
                parameters[$"@OfficePhone_{i}"] = employees[i].OfficePhone;
                parameters[$"@CellPhone_{i}"] = employees[i].CellPhone;
                parameters[$"@EmployeeClassificationKey_{i}"] = employees[i].EmployeeClassificationKey;
            }

            //A transaction is needed because this example uses multiple SQL statements.
            using (var con = OpenConnection())
            using (var trans = con.BeginTransaction())
            {
                con.Execute(sql.ToString(), parameters, transaction: trans);
                trans.Commit();
            }
        }
    }
Info

The repository methods are not normally virtual. This was done so that they could be overridden with better implementations as shown below.

DbConnector

public class MultipleCrudScenario : ScenarioBase, IMultipleCrudScenario<EmployeeSimple>
{
    public MultipleCrudScenario(string connectionString) : base(connectionString)
    { }

    public void DeleteBatch(IList<EmployeeSimple> employees)
    {
        if (employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        var keyList = string.Join(", ", employees.Select(x => x.EmployeeKey));
        var sql = $"DELETE FROM HR.Employee WHERE EmployeeKey IN ({keyList});";

        DbConnector.NonQuery(sql).Execute();
    }

    public void DeleteBatchByKey(IList<int> employeeKeys)
    {
        if (employeeKeys == null || employeeKeys.Count == 0)
            throw new ArgumentException($"{nameof(employeeKeys)} is null or empty.", nameof(employeeKeys));

        var keyList = string.Join(", ", employeeKeys);
        var sql = $"DELETE FROM HR.Employee WHERE EmployeeKey IN ({keyList});";

        DbConnector.NonQuery(sql).Execute();
    }

    public IList<EmployeeSimple> FindByLastName(string lastName)
    {
        const string sql = "SELECT e.EmployeeKey, e.FirstName, e.MiddleName, e.LastName, e.Title, e.OfficePhone, e.CellPhone, e.EmployeeClassificationKey FROM HR.EmployeeDetail e WHERE e.LastName = @lastName";

        return DbConnector.ReadToList<EmployeeSimple>(sql, new { lastName }).Execute();
    }

    public void InsertBatch(IList<EmployeeSimple> employees)
    {
        if (employees == null || !employees.Any())
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        //Best approach for unlimited inserts since SQL server has parameter amount restrictions
        //https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?redirectedfrom=MSDN&view=sql-server-ver15
        DbConnector.Build<int?>(
                sql: @$"INSERT INTO {EmployeeSimple.TableName}
                (
                    CellPhone,
                    EmployeeClassificationKey,
                    FirstName,
                    LastName,
                    MiddleName,
                    OfficePhone,
                    Title
                ) 
                VALUES (
                    @{nameof(EmployeeSimple.CellPhone)},
                    @{nameof(EmployeeSimple.EmployeeClassificationKey)},
                    @{nameof(EmployeeSimple.FirstName)},
                    @{nameof(EmployeeSimple.LastName)},
                    @{nameof(EmployeeSimple.MiddleName)},
                    @{nameof(EmployeeSimple.OfficePhone)},
                    @{nameof(EmployeeSimple.Title)}
                )",
                param: employees.First(),
                onExecute: (int? result, IDbExecutionModel em) =>
                {
                    //Set the command
                    DbCommand command = em.Command;

                    //Execute first row.
                    em.NumberOfRowsAffected = command.ExecuteNonQuery();

                    //Set and execute remaining rows.
                    foreach (var emp in employees.Skip(1))
                    {
                        command.Parameters[nameof(EmployeeSimple.CellPhone)].Value = emp.CellPhone ?? (object)DBNull.Value;
                        command.Parameters[nameof(EmployeeSimple.EmployeeClassificationKey)].Value = emp.EmployeeClassificationKey;
                        command.Parameters[nameof(EmployeeSimple.FirstName)].Value = emp.FirstName ?? (object)DBNull.Value;
                        command.Parameters[nameof(EmployeeSimple.LastName)].Value = emp.LastName ?? (object)DBNull.Value;
                        command.Parameters[nameof(EmployeeSimple.MiddleName)].Value = emp.MiddleName ?? (object)DBNull.Value;
                        command.Parameters[nameof(EmployeeSimple.OfficePhone)].Value = emp.OfficePhone ?? (object)DBNull.Value;
                        command.Parameters[nameof(EmployeeSimple.Title)].Value = emp.Title ?? (object)DBNull.Value;

                        em.NumberOfRowsAffected += command.ExecuteNonQuery();
                    }

                    return em.NumberOfRowsAffected;
                }
            )
            .WithIsolationLevel(IsolationLevel.ReadCommitted)//Use a transaction
            .Execute();
    }

    public IList<int> InsertBatchReturnKeys(IList<EmployeeSimple> employees)
    {
        if (employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        string sql = @$"INSERT INTO {EmployeeSimple.TableName}
                        (
                            CellPhone,
                            EmployeeClassificationKey,
                            FirstName,
                            LastName,
                            MiddleName,
                            OfficePhone,
                            Title
                        ) 
                        OUTPUT Inserted.EmployeeKey
                        VALUES (
                            @{nameof(EmployeeSimple.CellPhone)},
                            @{nameof(EmployeeSimple.EmployeeClassificationKey)},
                            @{nameof(EmployeeSimple.FirstName)},
                            @{nameof(EmployeeSimple.LastName)},
                            @{nameof(EmployeeSimple.MiddleName)},
                            @{nameof(EmployeeSimple.OfficePhone)},
                            @{nameof(EmployeeSimple.Title)}
                        )";

        //Best approach since SQL server has parameter amount restrictions
        //https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?redirectedfrom=MSDN&view=sql-server-ver15
        return DbConnector.ReadTo<List<int>>(
                 onInit: (cmds) =>
                 {
                     foreach (var emp in employees)
                     {
                         cmds.Enqueue(cmd =>
                         {
                             cmd.CommandText = sql;
                             cmd.CommandBehavior = CommandBehavior.SingleResult;
                             cmd.Parameters.AddFor(emp);
                         });
                     }
                 },
                 onLoad: (List<int> data, IDbExecutionModel em, DbDataReader odr) =>
                 {
                     if (data == null)
                         data = new List<int>();

                     data.Add(odr.SingleOrDefault<int>(em.Token, em.JobCommand));

                     return data;
                 }
             )
            .WithIsolationLevel(IsolationLevel.ReadCommitted)//Use a transaction
            .Execute();
    }

    public IList<EmployeeSimple> InsertBatchReturnRows(IList<EmployeeSimple> employees)
    {
        if (employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        string sql = @$"INSERT INTO {EmployeeSimple.TableName}
                        (
                            CellPhone,
                            EmployeeClassificationKey,
                            FirstName,
                            LastName,
                            MiddleName,
                            OfficePhone,
                            Title
                        ) 
                        OUTPUT Inserted.EmployeeKey, Inserted.FirstName, Inserted.MiddleName, Inserted.LastName, Inserted.Title, Inserted.OfficePhone, Inserted.CellPhone, Inserted.EmployeeClassificationKey
                        VALUES (
                            @{nameof(EmployeeSimple.CellPhone)},
                            @{nameof(EmployeeSimple.EmployeeClassificationKey)},
                            @{nameof(EmployeeSimple.FirstName)},
                            @{nameof(EmployeeSimple.LastName)},
                            @{nameof(EmployeeSimple.MiddleName)},
                            @{nameof(EmployeeSimple.OfficePhone)},
                            @{nameof(EmployeeSimple.Title)}
                        )";

        //Best approach since SQL server has parameter amount restrictions
        //https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?redirectedfrom=MSDN&view=sql-server-ver15
        return DbConnector.ReadTo<List<EmployeeSimple>>(
                 onInit: (cmds) =>
                 {
                     foreach (var emp in employees)
                     {
                         cmds.Enqueue(cmd =>
                         {
                             cmd.CommandText = sql;
                             cmd.CommandBehavior = CommandBehavior.SingleResult;
                             cmd.Parameters.AddFor(emp);
                         });
                     }
                 },
                 onLoad: (List<EmployeeSimple> data, IDbExecutionModel em, DbDataReader odr) =>
                 {
                     if (data == null)
                         data = new List<EmployeeSimple>();

                     data.Add(odr.SingleOrDefault<EmployeeSimple>(em.Token, em.JobCommand));

                     return data;
                 }
             )
            .WithIsolationLevel(IsolationLevel.ReadCommitted)//Use a transaction
            .Execute();
    }

    public void InsertBatchWithRefresh(IList<EmployeeSimple> employees)
    {
        if (employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        string sql = @$"INSERT INTO {EmployeeSimple.TableName}
                        (
                            CellPhone,
                            EmployeeClassificationKey,
                            FirstName,
                            LastName,
                            MiddleName,
                            OfficePhone,
                            Title
                        ) 
                        OUTPUT Inserted.EmployeeKey, Inserted.FirstName, Inserted.MiddleName, Inserted.LastName, Inserted.Title, Inserted.OfficePhone, Inserted.CellPhone, Inserted.EmployeeClassificationKey
                        VALUES (
                            @{nameof(EmployeeSimple.CellPhone)},
                            @{nameof(EmployeeSimple.EmployeeClassificationKey)},
                            @{nameof(EmployeeSimple.FirstName)},
                            @{nameof(EmployeeSimple.LastName)},
                            @{nameof(EmployeeSimple.MiddleName)},
                            @{nameof(EmployeeSimple.OfficePhone)},
                            @{nameof(EmployeeSimple.Title)}
                        )";

        //Best approach since SQL server has parameter amount restrictions
        //https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?redirectedfrom=MSDN&view=sql-server-ver15
        DbConnector.ReadTo<bool>(
                 onInit: (cmds) =>
                 {
                     foreach (var emp in employees)
                     {
                         cmds.Enqueue(cmd =>
                         {
                             cmd.CommandText = sql;
                             cmd.CommandBehavior = CommandBehavior.SingleResult;
                             cmd.Parameters.AddFor(emp);
                         });
                     }
                 },
                 onLoad: (bool result, IDbExecutionModel em, DbDataReader odr) =>
                 {
                     employees[em.Index].Refresh(odr.SingleOrDefault<EmployeeSimple>(em.Token, em.JobCommand));
                     return true;
                 }
             )
            .WithIsolationLevel(IsolationLevel.ReadCommitted)//Use a transaction
            .Execute();
    }

    public void UpdateBatch(IList<EmployeeSimple> employees)
    {
        if (employees == null || !employees.Any())
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        //Best approach since SQL server has parameter amount restrictions
        //https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?redirectedfrom=MSDN&view=sql-server-ver15
        DbConnector.Build<int?>(
                sql: @$"UPDATE {EmployeeSimple.TableName}
                SET
                    CellPhone = @{nameof(EmployeeSimple.CellPhone)},
                    EmployeeClassificationKey = @{nameof(EmployeeSimple.EmployeeClassificationKey)},
                    FirstName = @{nameof(EmployeeSimple.FirstName)},
                    LastName = @{nameof(EmployeeSimple.LastName)},
                    MiddleName = @{nameof(EmployeeSimple.MiddleName)},
                    OfficePhone = @{nameof(EmployeeSimple.OfficePhone)},
                    Title = @{nameof(EmployeeSimple.Title)}
                WHERE EmployeeKey = @{nameof(EmployeeSimple.EmployeeKey)}",
                param: employees.First(),
                onExecute: (int? result, IDbExecutionModel em) =>
                {
                    //Set the command
                    DbCommand command = em.Command;

                    //Execute first row.
                    em.NumberOfRowsAffected = command.ExecuteNonQuery();

                    //Set and execute remaining rows.
                    foreach (var emp in employees.Skip(1))
                    {
                        command.Parameters[nameof(EmployeeSimple.EmployeeKey)].Value = emp.EmployeeKey;
                        command.Parameters[nameof(EmployeeSimple.CellPhone)].Value = emp.CellPhone ?? (object)DBNull.Value;
                        command.Parameters[nameof(EmployeeSimple.EmployeeClassificationKey)].Value = emp.EmployeeClassificationKey;
                        command.Parameters[nameof(EmployeeSimple.FirstName)].Value = emp.FirstName ?? (object)DBNull.Value;
                        command.Parameters[nameof(EmployeeSimple.LastName)].Value = emp.LastName ?? (object)DBNull.Value;
                        command.Parameters[nameof(EmployeeSimple.MiddleName)].Value = emp.MiddleName ?? (object)DBNull.Value;
                        command.Parameters[nameof(EmployeeSimple.OfficePhone)].Value = emp.OfficePhone ?? (object)DBNull.Value;
                        command.Parameters[nameof(EmployeeSimple.Title)].Value = emp.Title ?? (object)DBNull.Value;

                        em.NumberOfRowsAffected += command.ExecuteNonQuery();
                    }

                    return em.NumberOfRowsAffected;
                }
            )
            .WithIsolationLevel(IsolationLevel.ReadCommitted)//Use a transaction
            .Execute();
    }
}

Dapper.Contrib

The Dapper.Contrib library can elimiante the boilerplate for some common scenarios.

public class MultipleCrudScenarioContrib : MultipleCrudScenario
{
    public MultipleCrudScenarioContrib(string connectionString) : base(connectionString)
    { }

    override public void DeleteBatch(IList<EmployeeSimple> employees)
    {
        if (employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        using (var con = OpenConnection())
            con.Delete(employees);
    }

    override public void InsertBatch(IList<EmployeeSimple> employees)
    {
        if (employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        using (var con = OpenConnection())
            con.Insert(employees);
    }

    override public void UpdateBatch(IList<EmployeeSimple> employees)
    {
        if (employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        using (var con = OpenConnection())
            con.Update(employees);
    }
}

Entity Framework 6

public class MultipleCrudScenario : IMultipleCrudScenario<Employee>
{
    private Func<OrmCookbookContext> CreateDbContext;

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

    public void DeleteBatch(IList<Employee> employees)
    {
        if (employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        using (var context = CreateDbContext())
        {
            foreach (var employee in employees)
                context.Entry(employee).State = EntityState.Deleted;
            context.SaveChanges();
        }
    }

    public void DeleteBatchByKey(IList<int> employeeKeys)
    {
        if (employeeKeys == null || employeeKeys.Count == 0)
            throw new ArgumentException($"{nameof(employeeKeys)} is null or empty.", nameof(employeeKeys));

        using (var context = CreateDbContext())
        {
            foreach (var key in employeeKeys)
                context.Entry(new Employee() { EmployeeKey = key }).State = EntityState.Deleted;
            context.SaveChanges();
        }
    }

    public IList<Employee> FindByLastName(string lastName)
    {
        using (var context = CreateDbContext())
            return context.Employee.Where(ec => ec.LastName == lastName).ToList();
    }

    public void InsertBatch(IList<Employee> employees)
    {
        if (employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        using (var context = CreateDbContext())
        {
            foreach (var employee in employees)
                context.Employee.Add(employee);
            context.SaveChanges();
        }
    }

    public IList<int> InsertBatchReturnKeys(IList<Employee> employees)
    {
        if (employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        using (var context = CreateDbContext())
        {
            foreach (var employee in employees)
                context.Employee.Add(employee);
            context.SaveChanges();
        }

        return employees.Select(x => x.EmployeeKey).ToList();
    }

    public IList<Employee> InsertBatchReturnRows(IList<Employee> employees)
    {
        if (employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        using (var context = CreateDbContext())
        {
            foreach (var employee in employees)
                context.Employee.Add(employee);
            context.SaveChanges();
        }

        return employees;
    }

    public void InsertBatchWithRefresh(IList<Employee> employees)
    {
        if (employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        using (var context = CreateDbContext())
        {
            foreach (var employee in employees)
                context.Employee.Add(employee);
            context.SaveChanges();
        }
    }

    public void UpdateBatch(IList<Employee> employees)
    {
        if (employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        using (var context = CreateDbContext())
        {
            foreach (var employee in employees)
                context.Entry(employee).State = EntityState.Modified;
            context.SaveChanges();
        }
    }
}

Entity Framework Core

public class MultipleCrudScenario : IMultipleCrudScenario<Employee>
{
    private Func<OrmCookbookContext> CreateDbContext;

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

    public void DeleteBatch(IList<Employee> employees)
    {
        if (employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        using (var context = CreateDbContext())
        {
            foreach (var employee in employees)
                context.Entry(employee).State = EntityState.Deleted;
            context.SaveChanges();
        }
    }

    public void DeleteBatchByKey(IList<int> employeeKeys)
    {
        if (employeeKeys == null || employeeKeys.Count == 0)
            throw new ArgumentException($"{nameof(employeeKeys)} is null or empty.", nameof(employeeKeys));

        using (var context = CreateDbContext())
        {
            foreach (var key in employeeKeys)
                context.Entry(new Employee() { EmployeeKey = key }).State = EntityState.Deleted;
            context.SaveChanges();
        }
    }

    public IList<Employee> FindByLastName(string lastName)
    {
        using (var context = CreateDbContext())
            return context.Employee.Where(ec => ec.LastName == lastName).ToList();
    }

    public void InsertBatch(IList<Employee> employees)
    {
        if (employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        using (var context = CreateDbContext())
        {
            foreach (var employee in employees)
                context.Employee.Add(employee);
            context.SaveChanges();
        }
    }

    public IList<int> InsertBatchReturnKeys(IList<Employee> employees)
    {
        if (employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        using (var context = CreateDbContext())
        {
            foreach (var employee in employees)
                context.Employee.Add(employee);
            context.SaveChanges();
        }

        return employees.Select(x => x.EmployeeKey).ToList();
    }

    public IList<Employee> InsertBatchReturnRows(IList<Employee> employees)
    {
        if (employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        using (var context = CreateDbContext())
        {
            foreach (var employee in employees)
                context.Employee.Add(employee);
            context.SaveChanges();
        }

        return employees;
    }

    public void InsertBatchWithRefresh(IList<Employee> employees)
    {
        if (employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        using (var context = CreateDbContext())
        {
            foreach (var employee in employees)
                context.Employee.Add(employee);
            context.SaveChanges();
        }
    }

    public void UpdateBatch(IList<Employee> employees)
    {
        if (employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        using (var context = CreateDbContext())
        {
            foreach (var employee in employees)
                context.Entry(employee).State = EntityState.Modified;
            context.SaveChanges();
        }
    }
}

It should be noted that performance of the batch update and delete operations is pretty low, Entity Framework Core will Update or Delete every record one by one.

There is a third-party library can implement high-performance batch operations, which is named Zack.EFCore.Batch. It can be obtained from https://github.com/yangzhongke/Zack.EFCore.Batch.

LINQ to DB

TODO

LLBLGen Pro

public class MultipleCrudScenario : IMultipleCrudScenario<EmployeeEntity>
{
    public void DeleteBatch(IList<EmployeeEntity> employees)
    {
        if(employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        using(var adapter = new DataAccessAdapter())
        {
            // Delete the entities directly using an IN (key1, key2...) predicate. 
            adapter.DeleteEntitiesDirectly(typeof(EmployeeEntity),
                                           new RelationPredicateBucket(EmployeeFields.EmployeeKey
                                                                                     .In(employees.Select(e => e.EmployeeKey).ToList())));
        }
    }


    public void DeleteBatchByKey(IList<int> employeeKeys)
    {
        if(employeeKeys == null || employeeKeys.Count == 0)
            throw new ArgumentException($"{nameof(employeeKeys)} is null or empty.", nameof(employeeKeys));
        
        using(var adapter = new DataAccessAdapter())
        {
            // Delete the entities directly using an IN (key1, key2...) predicate. 
            adapter.DeleteEntitiesDirectly(typeof(EmployeeEntity),
                                           new RelationPredicateBucket(EmployeeFields.EmployeeKey.In(employeeKeys)));
        }
    }


    public IList<EmployeeEntity> FindByLastName(string lastName)
    {
        using(var adapter = new DataAccessAdapter())
        {
            return new LinqMetaData(adapter).Employee.Where(e => e.LastName == lastName).ToList();
        }
    }


    public void InsertBatch(IList<EmployeeEntity> employees)
    {
        if(employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        // The collection is disposed as it assigns event handlers to the entities it contains. Keeping the
        // entities around would keep the collection in memory.
        using(var toInsert = new EntityCollection<EmployeeEntity>(employees))
        {
            using(var adapter = new DataAccessAdapter())
            {
                // use batching for inserts. We'll use 100 for a batch size here. 
                // This will send at most 100 inserts at one time to the database
                // in a single DbCommand
                adapter.BatchSize = 100;
                adapter.SaveEntityCollection(toInsert);
            }
        }
    }


    public IList<int> InsertBatchReturnKeys(IList<EmployeeEntity> employees)
    {
        if(employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        using(var toInsert = new EntityCollection<EmployeeEntity>(employees))
        {
            using(var adapter = new DataAccessAdapter())
            {
                // use batching for inserts. We'll use 100 for a batch size here. 
                // This will send at most 100 inserts at one time to the database
                // in a single DbCommand
                adapter.BatchSize = 100;
                adapter.SaveEntityCollection(toInsert);
            }

            return toInsert.Select(e => e.EmployeeKey).ToList();
        }
    }


    public IList<EmployeeEntity> InsertBatchReturnRows(IList<EmployeeEntity> employees)
    {
        if(employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        // Use a unit of work here. We could have created a new EntityCollection here but as well
        var uow = new UnitOfWork2();
        foreach(var e in employees)
        {
            uow.AddForSave(e);
        }

        using(var adapter = new DataAccessAdapter())
        {
            // use batching for inserts. We'll use 100 for a batch size here. 
            // This will send at most 100 inserts at one time to the database
            // in a single DbCommand
            adapter.BatchSize = 100;
            uow.Commit(adapter);
        }

        // LLBLGen Pro will update entities in-place after an insert, so we can return what we received. 
        return employees;
    }


    public void InsertBatchWithRefresh(IList<EmployeeEntity> employees)
    {
        if(employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        using(var toInsert = new EntityCollection<EmployeeEntity>(employees))
        {
            using(var adapter = new DataAccessAdapter())
            {
                // use batching for inserts. We'll use 100 for a batch size here. 
                // This will send at most 100 inserts at one time to the database
                // in a single DbCommand. LLBLGen Pro will update the entity saved
                // with the new PK.
                adapter.BatchSize = 100;
                adapter.SaveEntityCollection(toInsert);
            }
        }
    }


    public void UpdateBatch(IList<EmployeeEntity> employees)
    {
        if(employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        using(var toUpdate = new EntityCollection<EmployeeEntity>(employees))
        {
            using(var adapter = new DataAccessAdapter())
            {
                // use batching for updates. We'll use 100 for a batch size here. 
                // This will send at most 100 updates at one time to the database
                // in a single DbCommand
                adapter.BatchSize = 100;
                adapter.SaveEntityCollection(toUpdate);
            }
        }
    }
}

NHibernate

public class MultipleCrudScenario : IMultipleCrudScenario<Employee>
{
    readonly ISessionFactory m_SessionFactory;

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

    public void DeleteBatch(IList<Employee> employees)
    {
        if (employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        using (var session = m_SessionFactory.OpenSession())
        {
            foreach (var employee in employees)
                session.Delete(employee);
            session.Flush();
        }
    }

    public void DeleteBatchByKey(IList<int> employeeKeys)
    {
        if (employeeKeys == null || employeeKeys.Count == 0)
            throw new ArgumentException($"{nameof(employeeKeys)} is null or empty.", nameof(employeeKeys));

        using (var session = m_SessionFactory.OpenSession())
        {
            foreach (var key in employeeKeys)
                session.Delete(new Employee() { EmployeeKey = key });

            session.Flush();
        }
    }

    public IList<Employee> FindByLastName(string lastName)
    {
        using (var session = m_SessionFactory.OpenSession())
            return session.Query<Employee>().Where(ec => ec.LastName == lastName).ToList();
    }

    public void InsertBatch(IList<Employee> employees)
    {
        if (employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        using (var session = m_SessionFactory.OpenSession())
        {
            foreach (var employee in employees)
                session.Save(employee);
            session.Flush();
        }
    }

    public IList<int> InsertBatchReturnKeys(IList<Employee> employees)
    {
        if (employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        using (var session = m_SessionFactory.OpenSession())
        {
            foreach (var employee in employees)
                session.Save(employee);
            session.Flush();
        }

        return employees.Select(x => x.EmployeeKey).ToList();
    }

    public IList<Employee> InsertBatchReturnRows(IList<Employee> employees)
    {
        if (employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        using (var session = m_SessionFactory.OpenSession())
        {
            foreach (var employee in employees)
                session.Save(employee);
            session.Flush();
        }

        return employees;
    }

    public void InsertBatchWithRefresh(IList<Employee> employees)
    {
        if (employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        using (var session = m_SessionFactory.OpenSession())
        {
            foreach (var employee in employees)
                session.Save(employee);
            session.Flush();
        }
    }

    public void UpdateBatch(IList<Employee> employees)
    {
        if (employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        using (var session = m_SessionFactory.OpenSession())
        {
            foreach (var employee in employees)
                session.Update(employee);
            session.Flush();
        }
    }
}

RepoDb

public class MultipleCrudScenario : BaseRepository<EmployeeSimple, SqlConnection>,
    IMultipleCrudScenario<EmployeeSimple>
{
    public MultipleCrudScenario(string connectionString)
        : base(connectionString, RDB.Enumerations.ConnectionPersistency.Instance)
    { }

    public void DeleteBatch(IList<EmployeeSimple> employees)
    {
        if (employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        var keys = employees.Select(e => e.EmployeeKey).AsList();
        Delete(e => keys.Contains(e.EmployeeKey));
    }

    public void DeleteBatchByKey(IList<int> employeeKeys)
    {
        if (employeeKeys == null || employeeKeys.Count == 0)
            throw new ArgumentException($"{nameof(employeeKeys)} is null or empty.", nameof(employeeKeys));

        Delete(e => employeeKeys.Contains(e.EmployeeKey));
    }

    public IList<EmployeeSimple> FindByLastName(string lastName)
    {
        return Query(e => e.LastName == lastName).AsList();
    }

    public void InsertBatch(IList<EmployeeSimple> employees)
    {
        if (employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        InsertAll(employees);
    }

    public IList<int> InsertBatchReturnKeys(IList<EmployeeSimple> employees)
    {
        if (employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        InsertAll(employees);

        return employees.Select(e => e.EmployeeKey).AsList();
    }

    public IList<EmployeeSimple> InsertBatchReturnRows(IList<EmployeeSimple> employees)
    {
        if (employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        InsertAll(employees);

        return employees;
    }

    public void InsertBatchWithRefresh(IList<EmployeeSimple> employees)
    {
        if (employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        InsertAll(employees);
    }

    public void UpdateBatch(IList<EmployeeSimple> employees)
    {
        if (employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        UpdateAll(employees);
    }
}

ServiceStack

public class MultipleCrudScenario : IMultipleCrudScenario<Employee>
{
    private readonly IDbConnectionFactory _dbConnectionFactory;
    public MultipleCrudScenario(IDbConnectionFactory dbConnectionFactory)
    {
        _dbConnectionFactory = dbConnectionFactory;
    }

    public void DeleteBatch(IList<Employee> employees)
    {
        if (employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        using (var db = _dbConnectionFactory.OpenDbConnection())
            db.DeleteAll(employees);
    }

    public void DeleteBatchByKey(IList<int> employeeKeys)
    {
        if (employeeKeys == null || employeeKeys.Count == 0)
            throw new ArgumentException($"{nameof(employeeKeys)} is null or empty.", nameof(employeeKeys));
        
        using (var db = _dbConnectionFactory.OpenDbConnection())
            db.DeleteByIds<Employee>(employeeKeys);
    }

    public IList<Employee> FindByLastName(string lastName)
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
            return db.Select<Employee>(e => e.LastName == lastName);
    }

    public void InsertBatch(IList<Employee> employees)
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
            db.InsertAll(employees);
    }

    public IList<int> InsertBatchReturnKeys(IList<Employee> employees)
    {
        if (employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
        
        using (var db = _dbConnectionFactory.OpenDbConnection())
            db.SaveAll(employees);

        return employees.Select(e => e.Id).ToList();
    }

    public IList<Employee> InsertBatchReturnRows(IList<Employee> employees)
    {
        if (employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
        
        using (var db = _dbConnectionFactory.OpenDbConnection())
            db.SaveAll(employees);

        return employees;
    }

    public void InsertBatchWithRefresh(IList<Employee> employees)
    {
        if (employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
        
        using (var db = _dbConnectionFactory.OpenDbConnection())
            db.SaveAll(employees);
    }

    public void UpdateBatch(IList<Employee> employees)
    {
        if (employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
        
        using (var db = _dbConnectionFactory.OpenDbConnection())
            db.UpdateAll(employees);
    }
}