CRUD Operations on Model with Object-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 object.

Scenario Prototype

public interface IEmployeeComplex
{
    string? CellPhone { get; set; }
    IReadOnlyEmployeeClassification? EmployeeClassification { 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 IModelWithLookupComplexScenario<TEmployee>
   where TEmployee : class, IEmployeeComplex, new()
{
    /// <summary>
    /// Create a new Employee row, returning the new primary key.
    /// </summary>
    /// <remarks>This may NOT modify the EmployeeClassification record.</remarks>
    int Create(TEmployee employee);

    /// <summary>
    /// Delete a Employee row using an object.
    /// </summary>
    /// <remarks>Behavior when row doesn't exist is not defined. This may NOT modify or delete the EmployeeClassification record.</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. This may NOT modify the EmployeeClassification record.</remarks>
    void Update(TEmployee employee);
}

Database Views

CREATE VIEW HR.EmployeeDetail
WITH SCHEMABINDING
AS
SELECT e.EmployeeKey,
       e.FirstName,
       e.MiddleName,
       e.LastName,
       e.Title,
       e.OfficePhone,
       e.CellPhone,
       e.EmployeeClassificationKey,
       ec.EmployeeClassificationName,
       ec.IsExempt,
       ec.IsEmployee
FROM HR.Employee e
    INNER JOIN HR.EmployeeClassification ec
        ON e.EmployeeClassificationKey = ec.EmployeeClassificationKey;

ADO.NET

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

public class EmployeeComplex : IEmployeeComplex
{
    public EmployeeComplex()
    {
    }

    public EmployeeComplex(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"));

        EmployeeClassification = new EmployeeClassification(reader);
    }

    public string? CellPhone { get; set; }
    public IReadOnlyEmployeeClassification? EmployeeClassification { 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; }
}

Likewise, a database view was used to join the Employee table with its lookup table(s).

    public class ModelWithLookupComplexScenario : SqlServerScenarioBase, IModelWithLookupComplexScenario<EmployeeComplex>
    {
        public ModelWithLookupComplexScenario(string connectionString) : base(connectionString)
        { }

        public int Create(EmployeeComplex employee)
        {
            if (employee == null)
                throw new ArgumentNullException(nameof(employee), $"{nameof(employee)} is null.");
            if (employee.EmployeeClassification == null)
                throw new ArgumentNullException(nameof(employee), $"{nameof(employee.EmployeeClassification)} 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.EmployeeClassification.EmployeeClassificationKey);

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

        public void Delete(EmployeeComplex 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<EmployeeComplex> FindByLastName(string lastName)
        {
            const string sql = @"SELECT ed.EmployeeKey, ed.FirstName, ed.MiddleName, ed.LastName, ed.Title, ed.OfficePhone, ed.CellPhone, ed.EmployeeClassificationKey, ed.EmployeeClassificationName, ed.IsExempt, ed.IsEmployee FROM HR.EmployeeDetail ed WHERE ed.LastName = @LastName";

            var result = new List<EmployeeComplex>();

            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 EmployeeComplex(reader));
                    }
                    return result;
                }
            }
        }

        public IList<EmployeeComplex> GetAll()
        {
            const string sql = @"SELECT ed.EmployeeKey, ed.FirstName, ed.MiddleName, ed.LastName, ed.Title, ed.OfficePhone, ed.CellPhone, ed.EmployeeClassificationKey, ed.EmployeeClassificationName, ed.IsExempt, ed.IsEmployee FROM HR.EmployeeDetail ed";

            var result = new List<EmployeeComplex>();

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

        public EmployeeComplex? GetByKey(int employeeKey)
        {
            const string sql = @"SELECT ed.EmployeeKey, ed.FirstName, ed.MiddleName, ed.LastName, ed.Title, ed.OfficePhone, ed.CellPhone, ed.EmployeeClassificationKey, ed.EmployeeClassificationName, ed.IsExempt, ed.IsEmployee FROM HR.EmployeeDetail ed WHERE ed.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 EmployeeComplex(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(EmployeeComplex employee)
        {
            if (employee == null)
                throw new ArgumentNullException(nameof(employee), $"{nameof(employee)} is null.");
            if (employee.EmployeeClassification == null)
                throw new ArgumentNullException(nameof(employee), $"{nameof(employee.EmployeeClassification)} 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.EmployeeClassification.EmployeeClassificationKey);

                cmd.ExecuteNonQuery();
            }
        }
    }

Chain

Chain does not support representing FK's as child objects for create/update operations. The FK must be exposed via the parent object.

Read operations must occur against a database view in order to get the properties from the child object. The Decompose attribute indicates that the child should be populated from the same view.

[Table("HR.Employee")]
[View("HR.EmployeeDetail")]
public partial class EmployeeComplex
{
    public string? CellPhone { get; set; }

    [Decompose] //Used for Read operations
    public EmployeeClassification? EmployeeClassification { get; set; }

    //Used for Insert/Update operations
    public int EmployeeClassificationKey => EmployeeClassification?.EmployeeClassificationKey ?? 0;

    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; }
}

    //Used for linking the entity to the test framework. Not part of the recipe.
    partial class EmployeeComplex : IEmployeeComplex
    {
        IReadOnlyEmployeeClassification? IEmployeeComplex.EmployeeClassification
        {
            get => EmployeeClassification;
            set => EmployeeClassification = (EmployeeClassification?)value;
        }
    }
}

public class ModelWithLookupComplexScenario : IModelWithLookupComplexScenario<EmployeeComplex>
{
    readonly SqlServerDataSource m_DataSource;

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

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

        //The object is mapped to the view, so we need to override the table we write to.
        return m_DataSource.Insert(employee).ToInt32().Execute();
    }

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

        //The object is mapped to the view, so we need to override the table we write to.
        m_DataSource.Delete(employee).Execute();
    }

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

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

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

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

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

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

        //The object is mapped to the view, so we need to override the table we write to.
        m_DataSource.Update(employee).Execute();
    }
}

Dapper

Dapper does not support representing FK's as child objects for create/update operations. The FK must be exposed via the parent object.

Read operations must occur against a database view in order to get the properties from the child object. The Multi Mapping overload indicates that the child should be populated from the same view. Use the splitOn parameter to indicate the primary key of the second object.

public partial class EmployeeComplex : IEmployeeComplex
{
    public string? CellPhone { get; set; }
    public EmployeeClassification? EmployeeClassification { get; set; }

    //Used for Insert/Update operations
    public int EmployeeClassificationKey => EmployeeClassification?.EmployeeClassificationKey ?? 0;

    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; }
}

    //Used for linking the entity to the test framework. Not part of the recipe.
    partial class EmployeeComplex : IEmployeeComplex
    {
        IReadOnlyEmployeeClassification? IEmployeeComplex.EmployeeClassification
        {
            get => EmployeeClassification;
            set => EmployeeClassification = (EmployeeClassification?)value;
        }
    }
}

    public class ModelWithLookupComplexScenario : ScenarioBase, IModelWithLookupComplexScenario<EmployeeComplex>
    {
        public ModelWithLookupComplexScenario(string connectionString) : base(connectionString)
        {
        }

        public int Create(EmployeeComplex employee)
        {
            if (employee == null)
                throw new ArgumentNullException(nameof(employee), $"{nameof(employee)} is null.");
            if (employee.EmployeeClassification == null)
                throw new ArgumentNullException(nameof(employee), $"{nameof(employee.EmployeeClassification)} 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(EmployeeComplex 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<EmployeeComplex> FindByLastName(string lastName)
        {
            const string sql = @"SELECT ed.EmployeeKey, ed.FirstName, ed.MiddleName, ed.LastName, ed.Title, ed.OfficePhone, ed.CellPhone, ed.EmployeeClassificationKey, ed.EmployeeClassificationName, ed.IsExempt, ed.IsEmployee FROM HR.EmployeeDetail ed WHERE ed.LastName = @LastName";

            var result = new List<EmployeeComplex>();

            using (var con = OpenConnection())
                return con.Query<EmployeeComplex, EmployeeClassification, EmployeeComplex>(sql,
                    (e, ec) => { e.EmployeeClassification = ec; return e; },
                    new { LastName = lastName },
                    splitOn: "EmployeeClassificationKey")
                    .ToList();
        }

        public IList<EmployeeComplex> GetAll()
        {
            const string sql = @"SELECT ed.EmployeeKey, ed.FirstName, ed.MiddleName, ed.LastName, ed.Title, ed.OfficePhone, ed.CellPhone, ed.EmployeeClassificationKey, ed.EmployeeClassificationName, ed.IsExempt, ed.IsEmployee FROM HR.EmployeeDetail ed";

            var result = new List<EmployeeComplex>();

            using (var con = OpenConnection())
                return con.Query<EmployeeComplex, EmployeeClassification, EmployeeComplex>(sql,
                    (e, ec) => { e.EmployeeClassification = ec; return e; },
                    splitOn: "EmployeeClassificationKey")
                    .ToList();
        }

        public EmployeeComplex? GetByKey(int employeeKey)
        {
            const string sql = @"SELECT ed.EmployeeKey, ed.FirstName, ed.MiddleName, ed.LastName, ed.Title, ed.OfficePhone, ed.CellPhone, ed.EmployeeClassificationKey, ed.EmployeeClassificationName, ed.IsExempt, ed.IsEmployee FROM HR.EmployeeDetail ed WHERE ed.EmployeeKey = @EmployeeKey";

            using (var con = OpenConnection())
                return con.Query<EmployeeComplex, EmployeeClassification, EmployeeComplex>(sql,
                    (e, ec) => { e.EmployeeClassification = ec; return e; },
                    new { EmployeeKey = employeeKey },
                    splitOn: "EmployeeClassificationKey")
                    .SingleOrDefault();
        }

        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 });
        }

        public void Update(EmployeeComplex employee)
        {
            if (employee == null)
                throw new ArgumentNullException(nameof(employee), $"{nameof(employee)} is null.");
            if (employee.EmployeeClassification == null)
                throw new ArgumentNullException(nameof(employee), $"{nameof(employee.EmployeeClassification)} 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 ModelWithLookupComplexScenario : ScenarioBase, IModelWithLookupComplexScenario<EmployeeComplex>
{
    public ModelWithLookupComplexScenario(string connectionString) : base(connectionString)
    {
    }

    public int Create(EmployeeComplex employee)
    {
        if (employee == null)
            throw new ArgumentNullException(nameof(employee), $"{nameof(employee)} is null.");
        if (employee.EmployeeClassification == null)
            throw new ArgumentNullException(nameof(employee), $"{nameof(employee.EmployeeClassification)} 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,
            new
            {
                employee.FirstName,
                employee.MiddleName,
                employee.LastName,
                employee.Title,
                employee.OfficePhone,
                employee.CellPhone,
                employee.EmployeeClassificationKey
            }).Execute();
    }

    public void Delete(EmployeeComplex 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, new { employee.EmployeeKey }).Execute();
    }

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

        DbConnector.NonQuery(sql, new { employeeKey }).Execute();
    }

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

        var settings = new ColumnMapSetting().WithSplitOnFor<EmployeeClassification>(e => e.EmployeeClassificationKey);

        return DbConnector.ReadToList<EmployeeComplex>(settings, sql, new { LastName = lastName }).Execute();
    }

    public IList<EmployeeComplex> GetAll()
    {
        const string sql = @"SELECT ed.EmployeeKey, ed.FirstName, ed.MiddleName, ed.LastName, ed.Title, ed.OfficePhone, ed.CellPhone, ed.EmployeeClassificationKey, ed.EmployeeClassificationName, ed.IsExempt, ed.IsEmployee FROM HR.EmployeeDetail ed";

        //Configure Split map settings
        var settings = new ColumnMapSetting().WithSplitOnFor<EmployeeClassification>(e => e.EmployeeClassificationKey);

        return DbConnector.ReadToList<EmployeeComplex>(settings, sql).Execute();
    }

    public EmployeeComplex? GetByKey(int employeeKey)
    {
        const string sql = @"SELECT ed.EmployeeKey, ed.FirstName, ed.MiddleName, ed.LastName, ed.Title, ed.OfficePhone, ed.CellPhone, ed.EmployeeClassificationKey, ed.EmployeeClassificationName, ed.IsExempt, ed.IsEmployee FROM HR.EmployeeDetail ed WHERE ed.EmployeeKey = @EmployeeKey";

        //Configure Split map settings
        var settings = new ColumnMapSetting().WithSplitOnFor<EmployeeClassification>(e => e.EmployeeClassificationKey);

        return DbConnector.ReadSingleOrDefault<EmployeeComplex>(settings, sql, new { EmployeeKey = 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(EmployeeComplex employee)
    {
        if (employee == null)
            throw new ArgumentNullException(nameof(employee), $"{nameof(employee)} is null.");
        if (employee.EmployeeClassification == null)
            throw new ArgumentNullException(nameof(employee), $"{nameof(employee.EmployeeClassification)} 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,
            new
            {
                employee.EmployeeKey,
                employee.FirstName,
                employee.MiddleName,
                employee.LastName,
                employee.Title,
                employee.OfficePhone,
                employee.CellPhone,
                employee.EmployeeClassificationKey
            }).Execute();
    }
}

Entity Framework 6

TODO

Entity Framework Core

Child objects outside of the DBContext (e.g. from a REST call) need to be mapped to an object created by the DBContext.

This provides a layer of safety, as otherwise clients could override data in the lookup table.

public class ModelWithLookupComplexScenario : IModelWithLookupComplexScenario<Employee>
{
    private Func<OrmCookbookContext> CreateDbContext;

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

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

        using (var context = CreateDbContext())
        {
            //Prevent updates to the lookup table
            context.Entry(employee.EmployeeClassificationKeyNavigation).State = EntityState.Unchanged;

            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)
                .Include(e => e.EmployeeClassificationKeyNavigation)
                .ToList();
    }

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

    public Employee? GetByKey(int employeeKey)
    {
        using (var context = CreateDbContext())
            return context.Employees
                .Include(e => e.EmployeeClassificationKeyNavigation)
                .SingleOrDefault(e => e.EmployeeKey == 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.");
        if (employee.EmployeeClassificationKeyNavigation == null)
            throw new ArgumentNullException(nameof(employee), $"{nameof(employee.EmployeeClassificationKeyNavigation)} is null.");

        using (var context = CreateDbContext())
        {
            //Prevent updates to the lookup table
            context.Entry(employee.EmployeeClassificationKeyNavigation).State = EntityState.Unchanged;

            context.Entry(employee).State = EntityState.Modified;
            context.SaveChanges();
        }
    }
}

LINQ to DB

TODO

LLBLGen Pro

As LLBLGen Pro supports change tracking in the entities it doesn't have to refetch an entity that's been updated. The repository code illustrates this. As it also by default persists all reachable entities in a graph, recursive saves are disabled here to make sure only the entity that's passed in is persisted.

Entity classes are always derived from a known base class so types created by the user aren't taken into account when traversing the graph, only entity classes.

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

        using (var adapter = new DataAccessAdapter())
        {
            // the test FoulLookup will alter the associated lookup entity and if we persist things recursively we'll save this record too, so we don't use any
            // recursive persistence here.
            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)
                                            .WithPath(p => p.Prefetch(e => e.EmployeeClassification))
                                            .ToList();
        }
    }

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

    public EmployeeEntity? GetByKey(int employeeKey)
    {
        using (var adapter = new DataAccessAdapter())
        {
            return new LinqMetaData(adapter).Employee
                                            .WithPath(p => p.Prefetch(e => e.EmployeeClassification))
                                            .SingleOrDefault(e => e.EmployeeKey == 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.");
        if (employee.EmployeeClassification == null)
            throw new ArgumentNullException(nameof(employee), $"{nameof(employee.EmployeeClassification)} 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)
            {
                // By default the whole graph is persisted in-order, FKs are synced etc. but there's a test in this system
                // which makes it fail if we do so, so we have to disable the recursive saves.
                adapter.SaveEntity(toPersist, refetchAfterSave: false, recurse: false);
            }
        }
    }
}

NHibernate

TODO

RepoDb

RepoDb does not support representing FK's as child objects for create/update operations. The FK must be exposed via the parent object.

RepoDb supports ExecuteQueryMultiple which can cater a much more optimal solution for fetching parent-children related data entities.

Read operations must occur against a database view in order to get the properties from the child object. The Decompose attribute indicates that the child should be populated from the same view.

[Map("[HR].[Employee]")]
public partial class EmployeeComplex : IEmployeeComplex
{
    public string? CellPhone { get; set; }

    public EmployeeClassification? EmployeeClassification { 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; }
}

    //Used for linking the entity to the test framework. Not part of the recipe.
    partial class EmployeeComplex : IEmployeeComplex
    {
        IReadOnlyEmployeeClassification? IEmployeeComplex.EmployeeClassification
        {
            get => EmployeeClassification;
            set
            {
                EmployeeClassification = (EmployeeClassification?)value;
                EmployeeClassificationKey = (value?.EmployeeClassificationKey).GetValueOrDefault();
            }
        }
    }
}

public class ModelWithLookupComplexScenario : DbRepository<SqlConnection>,
    IModelWithLookupComplexScenario<EmployeeComplex>
{
    public ModelWithLookupComplexScenario(string connectionString)
        : base(connectionString, RDB.Enumerations.ConnectionPersistency.Instance)
    { }

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

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

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

        base.Delete(employee);
    }

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

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

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

    public EmployeeComplex? GetByKey(int employeeKey)
    {
        var employee = Query<EmployeeComplex>(employeeKey).FirstOrDefault();
        if (employee != null)
        {
            employee.EmployeeClassification = Query<EmployeeClassification>(employee.EmployeeClassificationKey).FirstOrDefault();
        }
        return employee;
    }

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

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

        base.Update(employee);
    }
}

ServiceStack

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

    public int Create(Employee employee)
    {
        if (employee == null)
            throw new ArgumentNullException(nameof(employee), $"{nameof(employee)} is null.");
        if (employee.EmployeeClassification == null)
            throw new ArgumentNullException(nameof(employee), $"{nameof(employee.EmployeeClassification)} is null.");
        
        employee.EmployeeClassificationId = employee.EmployeeClassification.Id;
        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            db.Save(employee);
        }

        return employee.Id;
    }

    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.LoadSelect<Employee>(e => e.LastName == lastName);
    }

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

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

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

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

        if (employee.EmployeeClassification != null)
            employee.EmployeeClassificationId = employee.EmployeeClassification.Id;

        using (var db = _dbConnectionFactory.OpenDbConnection())
            db.Update(employee);
    }
}