Reading from Views

These scenarios demonstrate how to read from a view.

Note: While writing to views is supported by some databases, that capability is outside the scope of this scenario.

Scenario Prototype

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

    /// <summary>
    /// Gets an EmployeeDetail row by its primary key.
    /// </summary>
    IList<TEmployeeDetail> FindByEmployeeClassificationKey(int employeeClassificationKey);

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

    /// <summary>
    /// Gets an EmployeeDetail row by its primary key.
    /// </summary>
    TEmployeeDetail? GetByEmployeeKey(int employeeKey);

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

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

    public class ViewsScenario : SqlServerScenarioBase, IViewsScenario<EmployeeDetail, EmployeeSimple>
    {
        public ViewsScenario(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 IList<EmployeeDetail> FindByEmployeeClassificationKey(int employeeClassificationKey)
        {
            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.EmployeeClassificationKey = @EmployeeClassificationKey";

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

                var results = new List<EmployeeDetail>();

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

                return results;
            }
        }

        public IList<EmployeeDetail> 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";

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

                var results = new List<EmployeeDetail>();

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

                return results;
            }
        }

        public EmployeeDetail? GetByEmployeeKey(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 new EmployeeDetail(reader);
                    else
                        return null;
                }
            }
        }

        public IEmployeeClassification? GetClassification(int employeeClassificationKey)
        {
            const string sql = "SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName, ec.IsExempt, ec.IsEmployee FROM HR.EmployeeClassification ec WHERE 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 new EmployeeClassification(reader);
                    else
                        return null;
                }
            }
        }
    }

Chain

public class ViewsScenario : IViewsScenario<EmployeeDetail, EmployeeSimple>
{
    readonly SqlServerDataSource m_DataSource;

    public ViewsScenario(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 IList<EmployeeDetail> FindByEmployeeClassificationKey(int employeeClassificationKey)
    {
        return m_DataSource.From<EmployeeDetail>(new { employeeClassificationKey }).ToCollection().Execute();
    }

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

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

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

Dapper

public class ViewsScenario : ScenarioBase, IViewsScenario<EmployeeDetail, EmployeeSimple>
{
    public ViewsScenario(string connectionString) : base(connectionString)
    {
    }

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

        using (var con = OpenConnection())
            return (int)con.Insert(employee);
    }

    public IList<EmployeeDetail> FindByEmployeeClassificationKey(int employeeClassificationKey)
    {
        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.EmployeeClassificationKey = @EmployeeClassificationKey";

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

    public IList<EmployeeDetail> 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";

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

    public EmployeeDetail? GetByEmployeeKey(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.QuerySingleOrDefault<EmployeeDetail>(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 EmployeeClassificationKey = @EmployeeClassificationKey";

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

DbConnector

public class ViewsScenario : ScenarioBase, IViewsScenario<EmployeeDetail, EmployeeSimple>
{
    public ViewsScenario(string connectionString) : base(connectionString)
    {
    }

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

        return DbConnector.Scalar<int>(
            @$"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)}
                )"
            , employee)
            .Execute();
    }

    public IList<EmployeeDetail> FindByEmployeeClassificationKey(int employeeClassificationKey)
    {
        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.EmployeeClassificationKey = @employeeClassificationKey";

        return DbConnector.ReadToList<EmployeeDetail>(sql, new { employeeClassificationKey }).Execute();
    }

    public IList<EmployeeDetail> 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";

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

    public EmployeeDetail? GetByEmployeeKey(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";

        return DbConnector.ReadSingleOrDefault<EmployeeDetail>(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 EmployeeClassificationKey = @employeeClassificationKey";

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

Entity Framework 6

public class ViewsScenario : IViewsScenario<EmployeeDetail, Employee>
{
    private Func<OrmCookbookContext> CreateDbContext;

    public ViewsScenario(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 IList<EmployeeDetail> FindByEmployeeClassificationKey(int employeeClassificationKey)
    {
        using (var context = CreateDbContext())
            return context.EmployeeDetail.Where(x => x.EmployeeClassificationKey == employeeClassificationKey).ToList();
    }

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

    public EmployeeDetail? GetByEmployeeKey(int employeeKey)
    {
        using (var context = CreateDbContext())
            return context.EmployeeDetail.Where(x => x.EmployeeKey == employeeKey).SingleOrDefault();
    }

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

Entity Framework Core

Starting with EF Core 3.0, views are treated like tables, but with two additional requirements in the modelBuilder configuration:

• entity.HasNoKey();
• entity.ToView(name, schema);

public class ViewsScenario : IViewsScenario<EmployeeDetail, Employee>
{
    private Func<OrmCookbookContext> CreateDbContext;

    public ViewsScenario(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 IList<EmployeeDetail> FindByEmployeeClassificationKey(int employeeClassificationKey)
    {
        using (var context = CreateDbContext())
            return context.EmployeeDetails.Where(x => x.EmployeeClassificationKey == employeeClassificationKey).ToList();
    }

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

    public EmployeeDetail? GetByEmployeeKey(int employeeKey)
    {
        using (var context = CreateDbContext())
            return context.EmployeeDetails.Where(x => x.EmployeeKey == employeeKey).SingleOrDefault();
    }

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

LINQ to DB

public class ViewsScenario : IViewsScenario<EmployeeDetail, Employee>
{
    public int Create(Employee employee)
    {
        if (employee == null)
            throw new ArgumentNullException(nameof(employee), $"{nameof(employee)} is null.");

        using (var db = new OrmCookbook())
        {
            return db.InsertWithInt32Identity(employee);
        }
    }

    public IList<EmployeeDetail> FindByEmployeeClassificationKey(int employeeClassificationKey)
    {
        using (var db = new OrmCookbook())
            return db.EmployeeDetail.Where(x => x.EmployeeClassificationKey == employeeClassificationKey).ToList();
    }

    public IList<EmployeeDetail> FindByLastName(string lastName)
    {
        using (var db = new OrmCookbook())
            return db.EmployeeDetail.Where(x => x.LastName == lastName).ToList();
    }

    public EmployeeDetail? GetByEmployeeKey(int employeeKey)
    {
        using (var db = new OrmCookbook())
            return db.EmployeeDetail.Where(x => x.EmployeeKey == employeeKey).SingleOrDefault();
    }

    public IEmployeeClassification? GetClassification(int employeeClassificationKey)
    {
        using (var db = new OrmCookbook())
            return db.EmployeeClassification.Where(x => x.EmployeeClassificationKey == employeeClassificationKey).SingleOrDefault();
    }
}

LLBLGen Pro

The views are mapped as regular entities and marked as 'readonly' in the designer. Alternatively we could have mapped them as Typed View POCOs however these aren't able to participate in entity relationships.

public class ViewsScenario : IViewsScenario<EmployeeDetailEntity, 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);
            return employee.EmployeeKey;
        }
    }

    public IList<EmployeeDetailEntity> FindByEmployeeClassificationKey(int employeeClassificationKey)
    {
        using (var adapter = new DataAccessAdapter())
        {
            return new LinqMetaData(adapter).EmployeeDetail
                                            .Where(x => x.EmployeeClassificationKey == employeeClassificationKey)
                                            .ToList();
        }
    }

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

    public EmployeeDetailEntity? GetByEmployeeKey(int employeeKey)
    {
        using (var adapter = new DataAccessAdapter())
        {
            return new LinqMetaData(adapter).EmployeeDetail
                                            .SingleOrDefault(x => x.EmployeeKey == employeeKey);
        }
    }

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

NHibernate

In NHibernate, views require a unique ID column. They must also be configured as mutable="false".

<hibernate-mapping
  assembly="Recipes.NHibernate"
  namespace="Recipes.NHibernate.Entities">
  <class
    name="EmployeeDetail"
    table="EmployeeDetail"
    schema="HR"
    mutable="false">
    <id
      name="EmployeeKey" />
    <property
      name="FirstName" />
    <property
      name="MiddleName" />
    <property
      name="LastName" />
    <property
      name="Title" />
    <property
      name="OfficePhone" />
    <property
      name="CellPhone" />
    <property
      name="EmployeeClassificationKey" />
    <property
      name="EmployeeClassificationName" />
    <property
      name="IsExempt" />
    <property
      name="IsEmployee" />
  </class>
</hibernate-mapping>

public class ViewsScenario : IViewsScenario<EmployeeDetail, Employee>
{
    readonly ISessionFactory m_SessionFactory;

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

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

        using (var session = m_SessionFactory.OpenSession())
        {
            session.Save(employee);
            session.Flush();
            return employee.EmployeeKey;
        }
    }

    public IList<EmployeeDetail> FindByEmployeeClassificationKey(int employeeClassificationKey)
    {
        using (var session = m_SessionFactory.OpenStatelessSession())
        {
            return session.QueryOver<EmployeeDetail>()
                .Where(ed => ed.EmployeeClassificationKey == employeeClassificationKey)
                .List();
        }
    }

    public IList<EmployeeDetail> FindByLastName(string lastName)
    {
        using (var session = m_SessionFactory.OpenStatelessSession())
        {
            return session.QueryOver<EmployeeDetail>().Where(ed => ed.LastName == lastName).List();
        }
    }

    public EmployeeDetail? GetByEmployeeKey(int employeeKey)
    {
        using (var session = m_SessionFactory.OpenStatelessSession())
            return session.Get<EmployeeDetail>(employeeKey);
    }

    public IEmployeeClassification? GetClassification(int employeeClassificationKey)
    {
        using (var session = m_SessionFactory.OpenStatelessSession())
            return session.Get<EmployeeClassification>(employeeClassificationKey);
    }
}

RepoDb

public class ViewsScenario : DbRepository<SqlConnection>,
    IViewsScenario<EmployeeDetail, EmployeeSimple>
{
    public ViewsScenario(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 IList<EmployeeDetail> FindByEmployeeClassificationKey(int employeeClassificationKey)
    {
        return Query<EmployeeDetail>(e => e.EmployeeClassificationKey == employeeClassificationKey).AsList();
    }

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

    public EmployeeDetail? GetByEmployeeKey(int employeeKey)
    {
        return Query<EmployeeDetail>(e => e.EmployeeKey == employeeKey).FirstOrDefault();
    }

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

ServiceStack

public class ViewsScenario : IViewsScenario<EmployeeDetail, Employee>
{
    private readonly IDbConnectionFactory _dbConnectionFactory;

    public ViewsScenario(IDbConnectionFactory dbConnectionFactory)
    {
        _dbConnectionFactory = dbConnectionFactory;
    }

    public int Create(Employee employee)
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            return (int)db.Insert(employee, true);
        }
    }

    public IList<EmployeeDetail> FindByEmployeeClassificationKey(int employeeClassificationKey)
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            return db.Select<EmployeeDetail>(
                r => r.EmployeeClassificationKey == employeeClassificationKey);
        }
    }

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

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

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