CRUD Operations on Model with Integer-Based Foreign Key

This scenario demonstrates performing Create, Read, Update, and Delete operations on an object that has a foreign key reference to a lookup table. The FK reference is represented as an integer.

Scenario Prototype

public interface IEmployeeSimple
{
    string? CellPhone { get; set; }
    int EmployeeClassificationKey { get; set; }
    int EmployeeKey { get; set; }
    string? FirstName { get; set; }
    string? LastName { get; set; }
    string? MiddleName { get; set; }
    string? OfficePhone { get; set; }
    string? Title { get; set; }
}

public interface IModelWithLookupSimpleScenario<TEmployee>
   where TEmployee : class, IEmployeeSimple, new()
{
    /// <summary>
    /// Create a new Employee row, returning the new primary key.
    /// </summary>
    int Create(TEmployee employee);

    /// <summary>
    /// Delete a Employee row using an object.
    /// </summary>
    /// <remarks>Behavior when row doesn't exist is not defined.</remarks>
    void Delete(TEmployee employee);

    /// <summary>
    /// Delete a Employee row using its primary key.
    /// </summary>
    /// <remarks>Behavior when row doesn't exist is not defined.</remarks>
    void DeleteByKey(int employeeKey);

    /// <summary>
    /// Gets an Employee row by its name.
    /// </summary>
    IList<TEmployee> FindByLastName(string lastName);

    /// <summary>
    /// Gets all Employee rows.
    /// </summary>
    IList<TEmployee> GetAll();

    /// <summary>
    /// Gets an Employee row by its primary key.
    /// </summary>
    TEmployee? GetByKey(int employeeKey);

    /// <summary>
    /// Get an employee classification by key.
    /// </summary>
    /// <param name="employeeClassificationKey">The employee classification key.</param>
    IEmployeeClassification? GetClassification(int employeeClassificationKey);

    /// <summary>
    /// Update a Employee row.
    /// </summary>
    /// <remarks>Behavior when row doesn't exist is not defined.</remarks>
    void Update(TEmployee employee);
}

ADO.NET

In order to promote code reuse, object population has been moved into the model's constructor.

public class EmployeeSimple : IEmployeeSimple
{
    public EmployeeSimple()
    {
    }

    public EmployeeSimple(IDataReader reader)
    {
        Refresh(reader);
    }

    public string? CellPhone { get; set; }

    public int EmployeeClassificationKey { get; set; }

    public int EmployeeKey { get; set; }

    public string? FirstName { get; set; }

    public string? LastName { get; set; }

    public string? MiddleName { get; set; }

    public string? OfficePhone { get; set; }

    public string? Title { get; set; }

    /// <summary>
    /// Refreshes the object, replacing all fields with values from the IDataReader.
    /// </summary>
    /// <param name="reader">The reader.</param>
    /// <exception cref="System.ArgumentNullException">reader</exception>
    public void Refresh(IDataReader reader)
    {
        if (reader == null)
            throw new ArgumentNullException(nameof(reader), $"{nameof(reader)} is null.");

        EmployeeKey = reader.GetInt32(reader.GetOrdinal("EmployeeKey"));
        FirstName = reader.GetString(reader.GetOrdinal("FirstName"));
        if (!reader.IsDBNull(reader.GetOrdinal("MiddleName")))
            MiddleName = reader.GetString(reader.GetOrdinal("MiddleName"));
        LastName = reader.GetString(reader.GetOrdinal("LastName"));
        if (!reader.IsDBNull(reader.GetOrdinal("Title")))
            Title = reader.GetString(reader.GetOrdinal("Title"));
        if (!reader.IsDBNull(reader.GetOrdinal("OfficePhone")))
            OfficePhone = reader.GetString(reader.GetOrdinal("OfficePhone"));
        if (!reader.IsDBNull(reader.GetOrdinal("CellPhone")))
            CellPhone = reader.GetString(reader.GetOrdinal("CellPhone"));
        EmployeeClassificationKey = reader.GetInt32(reader.GetOrdinal("EmployeeClassificationKey"));
    }
}

    public class ModelWithLookupSimpleScenario : SqlServerScenarioBase, IModelWithLookupSimpleScenario<EmployeeSimple>
    {
        public ModelWithLookupSimpleScenario(string connectionString) : base(connectionString)
        { }

        public int Create(EmployeeSimple employee)
        {
            if (employee == null)
                throw new ArgumentNullException(nameof(employee), $"{nameof(employee)} is null.");

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

            using (var con = OpenConnection())
            using (var cmd = new SqlCommand(sql, con))
            {
                cmd.Parameters.AddWithValue("@FirstName", employee.FirstName);
                cmd.Parameters.AddWithValue("@MiddleName", (object?)employee.MiddleName ?? DBNull.Value);
                cmd.Parameters.AddWithValue("@LastName", employee.LastName);
                cmd.Parameters.AddWithValue("@Title", (object?)employee.Title ?? DBNull.Value);
                cmd.Parameters.AddWithValue("@OfficePhone", (object?)employee.OfficePhone ?? DBNull.Value);
                cmd.Parameters.AddWithValue("@CellPhone", (object?)employee.CellPhone ?? DBNull.Value);
                cmd.Parameters.AddWithValue("@EmployeeClassificationKey", employee.EmployeeClassificationKey);

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

        public void Delete(EmployeeSimple employee)
        {
            if (employee == null)
                throw new ArgumentNullException(nameof(employee), $"{nameof(employee)} is null.");

            const string sql = @"DELETE HR.Employee WHERE EmployeeKey = @EmployeeKey;";

            using (var con = OpenConnection())
            using (var cmd = new SqlCommand(sql, con))
            {
                cmd.Parameters.AddWithValue("@EmployeeKey", employee.EmployeeKey);
                cmd.ExecuteNonQuery();
            }
        }

        public void DeleteByKey(int employeeKey)
        {
            const string sql = @"DELETE HR.Employee WHERE EmployeeKey = @EmployeeKey;";

            using (var con = OpenConnection())
            using (var cmd = new SqlCommand(sql, con))
            {
                cmd.Parameters.AddWithValue("@EmployeeKey", employeeKey);
                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.Employee e WHERE e.LastName = @LastName";

            var result = new List<EmployeeSimple>();

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

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        result.Add(new EmployeeSimple(reader));
                    }
                    return result;
                }
            }
        }

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

            var result = new List<EmployeeSimple>();

            using (var con = OpenConnection())
            using (var cmd = new SqlCommand(sql, con))
            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    result.Add(new EmployeeSimple(reader));
                }
                return result;
            }
        }

        public EmployeeSimple? GetByKey(int employeeKey)
        {
            const string sql = @"SELECT e.EmployeeKey, e.FirstName, e.MiddleName, e.LastName, e.Title, e.OfficePhone, e.CellPhone,        e.EmployeeClassificationKey FROM HR.Employee e WHERE e.EmployeeKey = @EmployeeKey";

            using (var con = OpenConnection())
            using (var cmd = new SqlCommand(sql, con))
            {
                cmd.Parameters.AddWithValue("@EmployeeKey", employeeKey);
                using (var reader = cmd.ExecuteReader())
                {
                    if (!reader.Read())
                        return null;

                    return new EmployeeSimple(reader);
                }
            }
        }

        public IEmployeeClassification? GetClassification(int employeeClassificationKey)
        {
            const string sql = @"SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName, ec.IsExempt, ec.IsEmployee
                        FROM HR.EmployeeClassification ec
                        WHERE ec.EmployeeClassificationKey = @EmployeeClassificationKey;";

            using (var con = OpenConnection())
            using (var cmd = new SqlCommand(sql, con))
            {
                cmd.Parameters.AddWithValue("@EmployeeClassificationKey", employeeClassificationKey);
                using (var reader = cmd.ExecuteReader())
                {
                    if (!reader.Read())
                        return null;

                    return new EmployeeClassification()
                    {
                        EmployeeClassificationKey = reader.GetInt32(reader.GetOrdinal("EmployeeClassificationKey")),
                        EmployeeClassificationName = reader.GetString(reader.GetOrdinal("EmployeeClassificationName")),
                        IsExempt = reader.GetBoolean(reader.GetOrdinal("IsExempt")),
                        IsEmployee = reader.GetBoolean(reader.GetOrdinal("IsEmployee"))
                    };
                }
            }
        }

        public void Update(EmployeeSimple employee)
        {
            if (employee == null)
                throw new ArgumentNullException(nameof(employee), $"{nameof(employee)} is null.");

            const string sql = @"UPDATE HR.Employee
SET FirstName = @FirstName,
    MiddleName = @MiddleName,
    LastName = @LastName,
    Title = @Title,
    OfficePhone = @OfficePhone,
    CellPhone = @CellPhone,
    EmployeeClassificationKey = @EmployeeClassificationKey
WHERE EmployeeKey = @EmployeeKey;";

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

                cmd.Parameters.AddWithValue("@FirstName", employee.FirstName);
                cmd.Parameters.AddWithValue("@MiddleName", (object?)employee.MiddleName ?? DBNull.Value);
                cmd.Parameters.AddWithValue("@LastName", employee.LastName);
                cmd.Parameters.AddWithValue("@Title", (object?)employee.Title ?? DBNull.Value);
                cmd.Parameters.AddWithValue("@OfficePhone", (object?)employee.OfficePhone ?? DBNull.Value);
                cmd.Parameters.AddWithValue("@CellPhone", (object?)employee.CellPhone ?? DBNull.Value);
                cmd.Parameters.AddWithValue("@EmployeeClassificationKey", employee.EmployeeClassificationKey);

                cmd.ExecuteNonQuery();
            }
        }
    }

Chain

public class ModelWithLookupSimpleScenario : IModelWithLookupSimpleScenario<EmployeeSimple>
{
    readonly SqlServerDataSource m_DataSource;

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

    public int Create(EmployeeSimple employee)
    {
        if (employee == null)
            throw new ArgumentNullException(nameof(employee), $"{nameof(employee)} is null.");

        return m_DataSource.Insert(employee).ToInt32().Execute();
    }

    public void Delete(EmployeeSimple employee)
    {
        if (employee == null)
            throw new ArgumentNullException(nameof(employee), $"{nameof(employee)} is null.");

        m_DataSource.Delete(employee).Execute();
    }

    public void DeleteByKey(int employeeKey)
    {
        m_DataSource.DeleteByKey<EmployeeSimple>(employeeKey).Execute();
    }

    public IList<EmployeeSimple> FindByLastName(string lastName)
    {
        throw new NotImplementedException();
    }

    public IList<EmployeeSimple> GetAll()
    {
        return m_DataSource.From<EmployeeSimple>().ToCollection().Execute();
    }

    public EmployeeSimple? GetByKey(int employeeKey)
    {
        return m_DataSource.GetByKey<EmployeeSimple>(employeeKey).ToObjectOrNull().Execute();
    }

    public IEmployeeClassification? GetClassification(int employeeClassificationKey)
    {
        return m_DataSource.GetByKey<EmployeeClassification>(employeeClassificationKey).ToObject().Execute();
    }

    public void Update(EmployeeSimple employee)
    {
        if (employee == null)
            throw new ArgumentNullException(nameof(employee), $"{nameof(employee)} is null.");

        m_DataSource.Update(employee).Execute();
    }
}

Dapper

    public class ModelWithLookupSimpleScenario : ScenarioBase, IModelWithLookupSimpleScenario<EmployeeSimple>
    {
        public ModelWithLookupSimpleScenario(string connectionString) : base(connectionString)
        {
        }

        public int Create(EmployeeSimple employee)
        {
            if (employee == null)
                throw new ArgumentNullException(nameof(employee), $"{nameof(employee)} is null.");

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

            using (var con = OpenConnection())
                return (int)con.ExecuteScalar(sql, employee);
        }

        public void Delete(EmployeeSimple employee)
        {
            if (employee == null)
                throw new ArgumentNullException(nameof(employee), $"{nameof(employee)} is null.");

            const string sql = @"DELETE HR.Employee WHERE EmployeeKey = @EmployeeKey;";

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

        public void DeleteByKey(int employeeKey)
        {
            const string sql = @"DELETE HR.Employee WHERE EmployeeKey = @EmployeeKey;";

            using (var con = OpenConnection())
                con.Execute(sql, new { employeeKey });
        }

        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.Employee e WHERE e.LastName = @LastName";

            var result = new List<EmployeeSimple>();

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

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

            var result = new List<EmployeeSimple>();

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

        public EmployeeSimple? GetByKey(int employeeKey)
        {
            const string sql = @"SELECT e.EmployeeKey, e.FirstName, e.MiddleName, e.LastName, e.Title, e.OfficePhone, e.CellPhone,        e.EmployeeClassificationKey FROM HR.Employee e WHERE e.EmployeeKey = @EmployeeKey";

            using (var con = OpenConnection())
                return con.QuerySingleOrDefault<EmployeeSimple>(sql, new { employeeKey });
        }

        public IEmployeeClassification? GetClassification(int employeeClassificationKey)
        {
            const string sql = @"SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName, ec.IsExempt, ec.IsEmployee
                        FROM HR.EmployeeClassification ec
                        WHERE ec.EmployeeClassificationKey = @EmployeeClassificationKey;";

            using (var con = OpenConnection())
                return con.QuerySingle<EmployeeClassification>(sql, new { EmployeeClassificationKey = employeeClassificationKey });
        }

        public void Update(EmployeeSimple employee)
        {
            if (employee == null)
                throw new ArgumentNullException(nameof(employee), $"{nameof(employee)} is null.");

            const string sql = @"UPDATE HR.Employee
SET FirstName = @FirstName,
    MiddleName = @MiddleName,
    LastName = @LastName,
    Title = @Title,
    OfficePhone = @OfficePhone,
    CellPhone = @CellPhone,
    EmployeeClassificationKey = @EmployeeClassificationKey
WHERE EmployeeKey = @EmployeeKey;";

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

DbConnector

public class ModelWithLookupSimpleScenario : ScenarioBase, IModelWithLookupSimpleScenario<EmployeeSimple>
{
    public ModelWithLookupSimpleScenario(string connectionString) : base(connectionString)
    {
    }

    public int Create(EmployeeSimple employee)
    {
        if (employee == null)
            throw new ArgumentNullException(nameof(employee), $"{nameof(employee)} is null.");

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


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

    public void Delete(EmployeeSimple employee)
    {
        if (employee == null)
            throw new ArgumentNullException(nameof(employee), $"{nameof(employee)} is null.");

        const string sql = @"DELETE HR.Employee WHERE EmployeeKey = @EmployeeKey;";


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

    public void DeleteByKey(int employeeKey)
    {
        const string sql = @"DELETE HR.Employee WHERE EmployeeKey = @employeeKey;";


        DbConnector.NonQuery(sql, new { employeeKey }).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.Employee e WHERE e.LastName = @lastName";

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

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

        return DbConnector.ReadToList<EmployeeSimple>(sql).Execute();
    }

    public EmployeeSimple? GetByKey(int employeeKey)
    {
        const string sql = @"SELECT e.EmployeeKey, e.FirstName, e.MiddleName, e.LastName, e.Title, e.OfficePhone, e.CellPhone, e.EmployeeClassificationKey FROM HR.Employee e WHERE e.EmployeeKey = @employeeKey";

        return DbConnector.ReadSingleOrDefault<EmployeeSimple>(sql, new { employeeKey }).Execute();
    }

    public IEmployeeClassification? GetClassification(int employeeClassificationKey)
    {
        const string sql = @"SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName, ec.IsExempt, ec.IsEmployee
                    FROM HR.EmployeeClassification ec
                    WHERE ec.EmployeeClassificationKey = @employeeClassificationKey;";

        return DbConnector.ReadSingle<EmployeeClassification>(sql, new { employeeClassificationKey }).Execute();
    }

    public void Update(EmployeeSimple employee)
    {
        if (employee == null)
            throw new ArgumentNullException(nameof(employee), $"{nameof(employee)} is null.");

        const string sql = @"UPDATE HR.Employee
        SET FirstName = @FirstName,
            MiddleName = @MiddleName,
            LastName = @LastName,
            Title = @Title,
            OfficePhone = @OfficePhone,
            CellPhone = @CellPhone,
            EmployeeClassificationKey = @EmployeeClassificationKey
        WHERE EmployeeKey = @EmployeeKey;";

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

Entity Framework 6

public class ModelWithLookupSimpleScenario : IModelWithLookupSimpleScenario<Employee>
{
    private Func<OrmCookbookContext> CreateDbContext;

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

    public int Create(Employee employee)
    {
        if (employee == null)
            throw new ArgumentNullException(nameof(employee), $"{nameof(employee)} is null.");

        using (var context = CreateDbContext())
        {
            context.Employee.Add(employee);
            context.SaveChanges();
            return employee.EmployeeKey;
        }
    }

    public void Delete(Employee employee)
    {
        if (employee == null)
            throw new ArgumentNullException(nameof(employee), $"{nameof(employee)} is null.");

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

    public void DeleteByKey(int employeeKey)
    {
        using (var context = CreateDbContext())
        {
            //Find the row you wish to delete
            var temp = context.Employee.Find(employeeKey);
            if (temp != null)
            {
                context.Employee.Remove(temp);
                context.SaveChanges();
            }
        }
    }

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

    public IList<Employee> GetAll()
    {
        using (var context = CreateDbContext())
            return context.Employee.ToList();
    }

    public Employee? GetByKey(int employeeKey)
    {
        using (var context = CreateDbContext())
            return context.Employee.Find(employeeKey);
    }

    public IEmployeeClassification? GetClassification(int employeeClassificationKey)
    {
        using (var context = CreateDbContext())
            return context.EmployeeClassification.Find(employeeClassificationKey);
    }

    /// <summary>
    /// Updates the specified employee.
    /// </summary>
    /// <param name="employee">The employee.</param>
    /// <exception cref="ArgumentNullException">employee</exception>
    public void Update(Employee employee)
    {
        if (employee == null)
            throw new ArgumentNullException(nameof(employee), $"{nameof(employee)} is null.");

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

Entity Framework Core

public class ModelWithLookupSimpleScenario : IModelWithLookupSimpleScenario<Employee>
{
    private Func<OrmCookbookContext> CreateDbContext;

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

    public int Create(Employee employee)
    {
        if (employee == null)
            throw new ArgumentNullException(nameof(employee), $"{nameof(employee)} is null.");

        using (var context = CreateDbContext())
        {
            context.Employees.Add(employee);
            context.SaveChanges();
            return employee.EmployeeKey;
        }
    }

    public void Delete(Employee employee)
    {
        if (employee == null)
            throw new ArgumentNullException(nameof(employee), $"{nameof(employee)} is null.");

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

    public void DeleteByKey(int employeeKey)
    {
        using (var context = CreateDbContext())
        {
            //Find the row you wish to delete
            var temp = context.Employees.Find(employeeKey);
            if (temp != null)
            {
                context.Employees.Remove(temp);
                context.SaveChanges();
            }
        }
    }

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

    public IList<Employee> GetAll()
    {
        using (var context = CreateDbContext())
            return context.Employees.ToList();
    }

    public Employee? GetByKey(int employeeKey)
    {
        using (var context = CreateDbContext())
            return context.Employees.Find(employeeKey);
    }

    public IEmployeeClassification? GetClassification(int employeeClassificationKey)
    {
        using (var context = CreateDbContext())
            return context.EmployeeClassifications.Find(employeeClassificationKey);
    }

    /// <summary>
    /// Updates the specified employee.
    /// </summary>
    /// <param name="employee">The employee.</param>
    /// <exception cref="ArgumentNullException">employee</exception>
    public void Update(Employee employee)
    {
        if (employee == null)
            throw new ArgumentNullException(nameof(employee), $"{nameof(employee)} is null.");

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

LINQ to DB

TODO

LLBLGen Pro

public class ModelWithLookupSimpleScenario : IModelWithLookupSimpleScenario<EmployeeEntity>
{
    public int Create(EmployeeEntity employee)
    {
        if (employee == null)
            throw new ArgumentNullException(nameof(employee), $"{nameof(employee)} is null.");

        using (var adapter = new DataAccessAdapter())
        {
            adapter.SaveEntity(employee, true, recurse: false);
            return employee.EmployeeKey;
        }
    }

    public void Delete(EmployeeEntity employee)
    {
        if (employee == null)
            throw new ArgumentNullException(nameof(employee), $"{nameof(employee)} is null.");

        using (var adapter = new DataAccessAdapter())
        {
            employee.IsNew = false;
            adapter.DeleteEntity(employee);
        }
    }

    public void DeleteByKey(int employeeKey)
    {
        using (var adapter = new DataAccessAdapter())
        {
            adapter.DeleteEntitiesDirectly(typeof(EmployeeEntity), new RelationPredicateBucket(EmployeeFields.EmployeeKey.Equal(employeeKey)));
        }
    }

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

    public IList<EmployeeEntity> GetAll()
    {
        using (var adapter = new DataAccessAdapter())
        {
            return new LinqMetaData(adapter).Employee.ToList();
        }
    }

    public EmployeeEntity? GetByKey(int employeeKey)
    {
        using (var adapter = new DataAccessAdapter())
        {
            return adapter.FetchFirst(new QueryFactory().Employee.Where(EmployeeFields.EmployeeKey.Equal(employeeKey)));
        }
    }

    public IEmployeeClassification? GetClassification(int employeeClassificationKey)
    {
        using (var adapter = new DataAccessAdapter())
        {
            return new LinqMetaData(adapter).EmployeeClassification.FirstOrDefault(ec => ec.EmployeeClassificationKey == employeeClassificationKey);
        }
    }

    /// <summary>
    /// Updates the specified employee.
    /// </summary>
    /// <param name="employee">The employee.</param>
    /// <exception cref="ArgumentNullException">employee</exception>
    public void Update(EmployeeEntity employee)
    {
        if (employee == null)
            throw new ArgumentNullException(nameof(employee), $"{nameof(employee)} is null.");

        using (var adapter = new DataAccessAdapter())
        {
            EmployeeEntity toPersist = employee;
            if (toPersist.IsNew)
            {
                toPersist = new EmployeeEntity(employee.EmployeeKey);
                adapter.FetchEntity(toPersist);
                //Copy the changed fields
                toPersist.FirstName = employee.FirstName;
                toPersist.MiddleName = employee.MiddleName;
                toPersist.LastName = employee.LastName;
                toPersist.CellPhone = employee.CellPhone;
                toPersist.OfficePhone = employee.OfficePhone;
                toPersist.Title = employee.Title;
                toPersist.EmployeeClassificationKey = employee.EmployeeClassificationKey;
            }
            if (!toPersist.IsNew)
            {
                adapter.SaveEntity(toPersist, refetchAfterSave: false, recurse: false);
            }
        }
    }
}

NHibernate

TODO

RepoDb

public class ModelWithLookupSimpleScenario : DbRepository<SqlConnection>,
    IModelWithLookupSimpleScenario<EmployeeSimple>
{
    public ModelWithLookupSimpleScenario(string connectionString)
        : base(connectionString, RDB.Enumerations.ConnectionPersistency.Instance)
    { }

    public int Create(EmployeeSimple employee)
    {
        if (employee == null)
            throw new ArgumentNullException(nameof(employee), $"{nameof(employee)} is null.");

        return Insert<EmployeeSimple, int>(employee);
    }

    public void Delete(EmployeeSimple employee)
    {
        if (employee == null)
            throw new ArgumentNullException(nameof(employee), $"{nameof(employee)} is null.");

        base.Delete(employee);
    }

    public void DeleteByKey(int employeeKey)
    {
        Delete<EmployeeSimple>(employeeKey);
    }

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

    public IList<EmployeeSimple> GetAll()
    {
        return QueryAll<EmployeeSimple>().AsList();
    }

    public EmployeeSimple? GetByKey(int employeeKey)
    {
        return Query<EmployeeSimple>(employeeKey).FirstOrDefault();
    }

    public IEmployeeClassification? GetClassification(int employeeClassificationKey)
    {
        return Query<EmployeeClassification>(employeeClassificationKey).FirstOrDefault();
    }

    public void Update(EmployeeSimple employee)
    {
        if (employee == null)
            throw new ArgumentNullException(nameof(employee), $"{nameof(employee)} is null.");

        base.Update(employee);
    }
}

ServiceStack

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

    public int Create(Employee employee)
    {
        if (employee == null)
            throw new ArgumentNullException(nameof(employee), $"{nameof(employee)} is null.");
        
        using (var db = _dbConnectionFactory.OpenDbConnection())
            return (int) db.Insert(employee, true);
    }

    public void Delete(Employee employee)
    {
        if (employee == null)
            throw new ArgumentNullException(nameof(employee), $"{nameof(employee)} is null.");
        
        using (var db = _dbConnectionFactory.OpenDbConnection())
            db.Delete(employee);
    }

    public void DeleteByKey(int employeeKey)
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
            db.DeleteById<Employee>(employeeKey);
    }

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

    public IList<Employee> GetAll()
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
            return db.Select<Employee>();
    }

    public Employee? GetByKey(int employeeKey)
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
            return db.SingleById<Employee>(employeeKey);
    }

    public IEmployeeClassification? GetClassification(int employeeClassificationKey)
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
            return db.SingleById<EmployeeClassification>(employeeClassificationKey);
    }

    public void Update(Employee employee)
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
            db.Update(employee);
    }
}