Projecting with a Join

These scenarios demonstrate how to define a join and project the reults into a simple object.

See CRUD Operations on Model with Object-Based Foreign Key and CRUD Operations on Model with Child Records for other examples of performing joins.

Scenario Prototype

For the purpose of these examples, a database view may not be used.

public interface IJoinsScenario<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 an EmployeeDetail row by its 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);
}

ADO.NET

    public class JoinsScenario : SqlServerScenarioBase, IJoinsScenario<EmployeeDetail, EmployeeSimple>
    {
        public JoinsScenario(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 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 WHERE e.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 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 WHERE e.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 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 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 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

Chain doesn't natively support joins, so raw SQL (or a view) has to be used as a fallback.

    public class JoinsScenario : IJoinsScenario<EmployeeDetail, EmployeeSimple>
    {
        readonly SqlServerDataSource m_DataSource;

        public JoinsScenario(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)
        {
            const string sql = @"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
WHERE e.EmployeeClassificationKey = @EmployeeClassificationKey";

            return m_DataSource.Sql(sql, new { employeeClassificationKey }).ToCollection<EmployeeDetail>().Execute();
        }

        public IList<EmployeeDetail> FindByLastName(string lastName)
        {
            const string sql = "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 " +
"WHERE e.LastName = @LastName";

            return m_DataSource.Sql(sql, new { lastName }).ToCollection<EmployeeDetail>().Execute();
        }

        public EmployeeDetail? GetByEmployeeKey(int employeeKey)
        {
            const string sql = "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 " +
"WHERE e.EmployeeKey = @EmployeeKey";

            return m_DataSource.Sql(sql, new { employeeKey }).ToObjectOrNull<EmployeeDetail>().Execute();
        }

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

Dapper

public class JoinsScenario : ScenarioBase, IJoinsScenario<EmployeeDetail, EmployeeSimple>
{
    public JoinsScenario(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 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 WHERE e.EmployeeClassificationKey = @EmployeeClassificationKey";

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

    public IList<EmployeeDetail> FindByLastName(string lastName)
    {
        const string sql = "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 WHERE e.LastName = @LastName";

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

    public EmployeeDetail? GetByEmployeeKey(int employeeKey)
    {
        const string sql = "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 WHERE e.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 JoinsScenario : ScenarioBase, IJoinsScenario<EmployeeDetail, EmployeeSimple>
{
    public JoinsScenario(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 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 
        WHERE e.EmployeeClassificationKey = @employeeClassificationKey";

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

    public IList<EmployeeDetail> FindByLastName(string lastName)
    {
        const string sql = @"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 
        WHERE e.LastName = @lastName";

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

    public EmployeeDetail? GetByEmployeeKey(int employeeKey)
    {
        const string sql = @"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 
        WHERE e.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

Entity Framework natively supports joins, but not implicit projections. Multiple objects need to be explicitly mapped.

public class JoinsScenario : IJoinsScenario<EmployeeDetail, Employee>
{
    private Func<OrmCookbookContext> CreateDbContext;

    public JoinsScenario(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.Employee
                .Join(context.EmployeeClassification,
                    e => e.EmployeeClassificationKey,
                    ec => ec.EmployeeClassificationKey,
                    (e, ec) => new { e, ec })
                    .Where(ed => ed.ec.EmployeeClassificationKey == employeeClassificationKey)
                    .ToList() //everything below this line is client-side
                    .Select(ed => new EmployeeDetail()
                    {
                        EmployeeKey = ed.e.EmployeeKey,
                        FirstName = ed.e.FirstName,
                        MiddleName = ed.e.MiddleName,
                        LastName = ed.e.LastName,
                        CellPhone = ed.e.CellPhone,
                        OfficePhone = ed.e.OfficePhone,
                        Title = ed.e.Title,
                        EmployeeClassificationKey = ed.ec.EmployeeClassificationKey,
                        EmployeeClassificationName = ed.ec.EmployeeClassificationName,
                        IsEmployee = ed.ec.IsEmployee,
                        IsExempt = ed.ec.IsExempt,
                    })
                    .ToList();
    }

    public IList<EmployeeDetail> FindByLastName(string lastName)
    {
        using (var context = CreateDbContext())
            return context.Employee
                .Join(context.EmployeeClassification,
                    e => e.EmployeeClassificationKey,
                    ec => ec.EmployeeClassificationKey,
                    (e, ec) => new { e, ec })
                .Where(ed => ed.e.LastName == lastName)
                .ToList() //everything below this line is client-side
                .Select(ed => new EmployeeDetail()
                {
                    EmployeeKey = ed.e.EmployeeKey,
                    FirstName = ed.e.FirstName,
                    MiddleName = ed.e.MiddleName,
                    LastName = ed.e.LastName,
                    CellPhone = ed.e.CellPhone,
                    OfficePhone = ed.e.OfficePhone,
                    Title = ed.e.Title,
                    EmployeeClassificationKey = ed.ec.EmployeeClassificationKey,
                    EmployeeClassificationName = ed.ec.EmployeeClassificationName,
                    IsEmployee = ed.ec.IsEmployee,
                    IsExempt = ed.ec.IsExempt,
                })
                .ToList();
    }

    public EmployeeDetail? GetByEmployeeKey(int employeeKey)
    {
        using (var context = CreateDbContext())
            return context.Employee
                .Join(context.EmployeeClassification,
                    e => e.EmployeeClassificationKey,
                    ec => ec.EmployeeClassificationKey,
                    (e, ec) => new { e, ec })
                .Where(ed => ed.e.EmployeeKey == employeeKey)
                .ToList() //everything below this line is client-side
                .Select(ed => new EmployeeDetail()
                {
                    EmployeeKey = ed.e.EmployeeKey,
                    FirstName = ed.e.FirstName,
                    MiddleName = ed.e.MiddleName,
                    LastName = ed.e.LastName,
                    CellPhone = ed.e.CellPhone,
                    OfficePhone = ed.e.OfficePhone,
                    Title = ed.e.Title,
                    EmployeeClassificationKey = ed.ec.EmployeeClassificationKey,
                    EmployeeClassificationName = ed.ec.EmployeeClassificationName,
                    IsEmployee = ed.ec.IsEmployee,
                    IsExempt = ed.ec.IsExempt,
                })
                .SingleOrDefault();
    }

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

Entity Framework Core

EF Core natively supports joins, but not implicit projections. Multiple objects need to be explicitly mapped.

public class JoinsScenario : IJoinsScenario<EmployeeDetail, Employee>
{
    private Func<OrmCookbookContext> CreateDbContext;

    public JoinsScenario(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.Employees
                .Join(context.EmployeeClassifications,
                    e => e.EmployeeClassificationKey,
                    ec => ec.EmployeeClassificationKey,
                    (e, ec) => new EmployeeDetail()
                    {
                        EmployeeKey = e.EmployeeKey,
                        FirstName = e.FirstName,
                        MiddleName = e.MiddleName,
                        LastName = e.LastName,
                        CellPhone = e.CellPhone,
                        OfficePhone = e.OfficePhone,
                        Title = e.Title,
                        EmployeeClassificationKey = ec.EmployeeClassificationKey,
                        EmployeeClassificationName = ec.EmployeeClassificationName,
                        IsEmployee = ec.IsEmployee ?? true,
                        IsExempt = ec.IsExempt,
                    })
                .Where(ed => ed.EmployeeClassificationKey == employeeClassificationKey)
                .ToList();
    }

    public IList<EmployeeDetail> FindByLastName(string lastName)
    {
        using (var context = CreateDbContext())
            return context.Employees
                .Join(context.EmployeeClassifications,
                    e => e.EmployeeClassificationKey,
                    ec => ec.EmployeeClassificationKey,
                    (e, ec) => new EmployeeDetail()
                    {
                        EmployeeKey = e.EmployeeKey,
                        FirstName = e.FirstName,
                        MiddleName = e.MiddleName,
                        LastName = e.LastName,
                        CellPhone = e.CellPhone,
                        OfficePhone = e.OfficePhone,
                        Title = e.Title,
                        EmployeeClassificationKey = ec.EmployeeClassificationKey,
                        EmployeeClassificationName = ec.EmployeeClassificationName,
                        IsEmployee = ec.IsEmployee ?? true,
                        IsExempt = ec.IsExempt,
                    })
                .Where(ed => ed.LastName == lastName)
                .ToList();
    }

    public EmployeeDetail? GetByEmployeeKey(int employeeKey)
    {
        using (var context = CreateDbContext())
            return context.Employees
                .Join(context.EmployeeClassifications,
                    e => e.EmployeeClassificationKey,
                    ec => ec.EmployeeClassificationKey,
                    (e, ec) => new EmployeeDetail()
                    {
                        EmployeeKey = e.EmployeeKey,
                        FirstName = e.FirstName,
                        MiddleName = e.MiddleName,
                        LastName = e.LastName,
                        CellPhone = e.CellPhone,
                        OfficePhone = e.OfficePhone,
                        Title = e.Title,
                        EmployeeClassificationKey = ec.EmployeeClassificationKey,
                        EmployeeClassificationName = ec.EmployeeClassificationName,
                        IsEmployee = ec.IsEmployee ?? true,
                        IsExempt = ec.IsExempt,
                    })
                .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 JoinsScenario : IJoinsScenario<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.Employee
                .Join(db.EmployeeClassification,
                    e => e.EmployeeClassificationKey,
                    ec => ec.EmployeeClassificationKey,
                    (e, ec) => new EmployeeDetail()
                    {
                        EmployeeKey = e.EmployeeKey,
                        FirstName = e.FirstName,
                        MiddleName = e.MiddleName,
                        LastName = e.LastName,
                        CellPhone = e.CellPhone,
                        OfficePhone = e.OfficePhone,
                        Title = e.Title,
                        EmployeeClassificationKey = ec.EmployeeClassificationKey,
                        EmployeeClassificationName = ec.EmployeeClassificationName,
                        IsEmployee = ec.IsEmployee,
                        IsExempt = ec.IsExempt,
                    })
                .Where(ed => ed.EmployeeClassificationKey == employeeClassificationKey)
                .ToList();
    }

    public IList<EmployeeDetail> FindByLastName(string lastName)
    {
        using (var db = new OrmCookbook())
            return db.Employee
                .Join(db.EmployeeClassification,
                    e => e.EmployeeClassificationKey,
                    ec => ec.EmployeeClassificationKey,
                    (e, ec) => new EmployeeDetail()
                    {
                        EmployeeKey = e.EmployeeKey,
                        FirstName = e.FirstName,
                        MiddleName = e.MiddleName,
                        LastName = e.LastName,
                        CellPhone = e.CellPhone,
                        OfficePhone = e.OfficePhone,
                        Title = e.Title,
                        EmployeeClassificationKey = ec.EmployeeClassificationKey,
                        EmployeeClassificationName = ec.EmployeeClassificationName,
                        IsEmployee = ec.IsEmployee,
                        IsExempt = ec.IsExempt,
                    })
                .Where(ed => ed.LastName == lastName)
                .ToList();
    }

    public EmployeeDetail? GetByEmployeeKey(int employeeKey)
    {
        using (var db = new OrmCookbook())
            return db.Employee
                .Join(db.EmployeeClassification,
                    e => e.EmployeeClassificationKey,
                    ec => ec.EmployeeClassificationKey,
                    (e, ec) => new EmployeeDetail()
                    {
                        EmployeeKey = e.EmployeeKey,
                        FirstName = e.FirstName,
                        MiddleName = e.MiddleName,
                        LastName = e.LastName,
                        CellPhone = e.CellPhone,
                        OfficePhone = e.OfficePhone,
                        Title = e.Title,
                        EmployeeClassificationKey = ec.EmployeeClassificationKey,
                        EmployeeClassificationName = ec.EmployeeClassificationName,
                        IsEmployee = ec.IsEmployee,
                        IsExempt = ec.IsExempt,
                    })
                .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

LLBLGen Pro native supports joins, but not implicit projections in the entity API. It does in the plain SQL API. In the entity API, multiple objects need to be explicitly mapped.

public class JoinsScenario : IJoinsScenario<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())
        {
            var metaData = new LinqMetaData(adapter);
            var q = from e in metaData.Employee
                    join ec in metaData.EmployeeClassification on e.EmployeeClassificationKey equals ec.EmployeeClassificationKey
                    where ec.EmployeeClassificationKey == employeeClassificationKey
                    select new EmployeeDetailEntity()
                    {
                        EmployeeKey = e.EmployeeKey,
                        FirstName = e.FirstName,
                        MiddleName = e.MiddleName,
                        LastName = e.LastName,
                        CellPhone = e.CellPhone,
                        OfficePhone = e.OfficePhone,
                        Title = e.Title,
                        EmployeeClassificationKey = ec.EmployeeClassificationKey,
                        EmployeeClassificationName = ec.EmployeeClassificationName,
                        IsEmployee = ec.IsEmployee,
                        IsExempt = ec.IsExempt,
                    };
            return q.ToList();
        }
    }

    public IList<EmployeeDetailEntity> FindByLastName(string lastName)
    {
        using (var adapter = new DataAccessAdapter())
        {
            var metaData = new LinqMetaData(adapter);
            var q = from e in metaData.Employee
                    join ec in metaData.EmployeeClassification on e.EmployeeClassificationKey equals ec.EmployeeClassificationKey
                    where e.LastName == lastName
                    select new EmployeeDetailEntity()
                    {
                        EmployeeKey = e.EmployeeKey,
                        FirstName = e.FirstName,
                        MiddleName = e.MiddleName,
                        LastName = e.LastName,
                        CellPhone = e.CellPhone,
                        OfficePhone = e.OfficePhone,
                        Title = e.Title,
                        EmployeeClassificationKey = ec.EmployeeClassificationKey,
                        EmployeeClassificationName = ec.EmployeeClassificationName,
                        IsEmployee = ec.IsEmployee,
                        IsExempt = ec.IsExempt,
                    };
            return q.ToList();
        }
    }

    public EmployeeDetailEntity? GetByEmployeeKey(int employeeKey)
    {
        using (var adapter = new DataAccessAdapter())
        {
            var metaData = new LinqMetaData(adapter);
            var q = from e in metaData.Employee
                    join ec in metaData.EmployeeClassification on e.EmployeeClassificationKey equals ec.EmployeeClassificationKey
                    where e.EmployeeKey == employeeKey
                    select new EmployeeDetailEntity()
                    {
                        EmployeeKey = e.EmployeeKey,
                        FirstName = e.FirstName,
                        MiddleName = e.MiddleName,
                        LastName = e.LastName,
                        CellPhone = e.CellPhone,
                        OfficePhone = e.OfficePhone,
                        Title = e.Title,
                        EmployeeClassificationKey = ec.EmployeeClassificationKey,
                        EmployeeClassificationName = ec.EmployeeClassificationName,
                        IsEmployee = ec.IsEmployee,
                        IsExempt = ec.IsExempt,
                    };
            return q.SingleOrDefault();
        }
    }

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

Additionally, LLBLGen Pro supports design time projects over an entity graph, called Typed Lists. Here a Typed List, EmployeeJoined has been created which is the same projection as the one in the queries in the normal linq repository.

public class JoinsScenarioTypedList : IJoinsScenario<EmployeeJoinedRow, 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<EmployeeJoinedRow> FindByEmployeeClassificationKey(int employeeClassificationKey)
    {
        using (var adapter = new DataAccessAdapter())
        {
            var metaData = new LinqMetaData(adapter);
            var q = metaData.GetEmployeeJoinedTypedList()
                            .Where(ec => ec.EmployeeClassificationKey == employeeClassificationKey);
            return q.ToList();
        }
    }

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

    public EmployeeJoinedRow? GetByEmployeeKey(int employeeKey)
    {
        using (var adapter = new DataAccessAdapter())
        {
            return new LinqMetaData(adapter).GetEmployeeJoinedTypedList().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);
        }
    }
}

NHibernate

NHibernate native supports joins, but not projections. Multiple objects need to be explicitly mapped.

<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 JoinsScenario : IJoinsScenario<EmployeeDetail, Employee>
{
    readonly ISessionFactory m_SessionFactory;

    public JoinsScenario(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<EmployeeComplex>()
                .JoinQueryOver(e => e.EmployeeClassification)
                .Where(ec => ec!.EmployeeClassificationKey == employeeClassificationKey)
                .List()
                .Select(e => new EmployeeDetail()
                {
                    EmployeeKey = e.EmployeeKey,
                    FirstName = e.FirstName,
                    MiddleName = e.MiddleName,
                    LastName = e.LastName,
                    CellPhone = e.CellPhone,
                    OfficePhone = e.OfficePhone,
                    Title = e.Title,
                    EmployeeClassificationKey = e.EmployeeClassification!.EmployeeClassificationKey,
                    EmployeeClassificationName = e.EmployeeClassification.EmployeeClassificationName,
                    IsEmployee = e.EmployeeClassification.IsEmployee,
                    IsExempt = e.EmployeeClassification.IsExempt,
                })
                .ToList();
        }
    }

    public IList<EmployeeDetail> FindByLastName(string lastName)
    {
        using (var session = m_SessionFactory.OpenStatelessSession())
        {
            return session.QueryOver<EmployeeComplex>()
                .Where(e => e.LastName == lastName)
                .JoinQueryOver(e => e.EmployeeClassification)
                .List()
                .Select(e => new EmployeeDetail()
                {
                    EmployeeKey = e.EmployeeKey,
                    FirstName = e.FirstName,
                    MiddleName = e.MiddleName,
                    LastName = e.LastName,
                    CellPhone = e.CellPhone,
                    OfficePhone = e.OfficePhone,
                    Title = e.Title,
                    EmployeeClassificationKey = e.EmployeeClassification!.EmployeeClassificationKey,
                    EmployeeClassificationName = e.EmployeeClassification.EmployeeClassificationName,
                    IsEmployee = e.EmployeeClassification.IsEmployee,
                    IsExempt = e.EmployeeClassification.IsExempt,
                })
                .ToList();
        }
    }

    public EmployeeDetail? GetByEmployeeKey(int employeeKey)
    {
        using (var session = m_SessionFactory.OpenStatelessSession())
            return session.QueryOver<EmployeeComplex>()
                .Where(e => e.EmployeeKey == employeeKey)
                .JoinQueryOver(e => e.EmployeeClassification)
                .List()
                .Select(e => new EmployeeDetail()
                {
                    EmployeeKey = e.EmployeeKey,
                    FirstName = e.FirstName,
                    MiddleName = e.MiddleName,
                    LastName = e.LastName,
                    CellPhone = e.CellPhone,
                    OfficePhone = e.OfficePhone,
                    Title = e.Title,
                    EmployeeClassificationKey = e.EmployeeClassification!.EmployeeClassificationKey,
                    EmployeeClassificationName = e.EmployeeClassification.EmployeeClassificationName,
                    IsEmployee = e.EmployeeClassification.IsEmployee,
                    IsExempt = e.EmployeeClassification.IsExempt,
                })
                .SingleOrDefault();
    }

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

RepoDb

RepoDb does not support joins by default, you have to right raw-SQLs to achieve this.

public class JoinsScenario : DbRepository<SqlConnection>,
    IJoinsScenario<EmployeeDetail, EmployeeSimple>
{
    public JoinsScenario(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)
    {
        const string sql = "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 WHERE e.EmployeeClassificationKey = @EmployeeClassificationKey";

        return ExecuteQuery<EmployeeDetail>(sql, new { employeeClassificationKey }).AsList();
    }

    public IList<EmployeeDetail> FindByLastName(string lastName)
    {
        const string sql = "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 WHERE e.LastName = @LastName";

        return ExecuteQuery<EmployeeDetail>(sql, new { lastName }).AsList();
    }

    public EmployeeDetail? GetByEmployeeKey(int employeeKey)
    {
        const string sql = "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 WHERE e.EmployeeKey = @EmployeeKey";

        return ExecuteQuery<EmployeeDetail>(sql, new { employeeKey }).FirstOrDefault();
    }

    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 ExecuteQuery<EmployeeClassification>(sql, new { employeeClassificationKey }).FirstOrDefault();
    }
}

ServiceStack

public class JoinsScenario : IJoinsScenario<EmployeeDetail, Employee>
{
    private readonly IDbConnectionFactory _dbConnectionFactory;

    public JoinsScenario(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 IList<EmployeeDetail> FindByEmployeeClassificationKey(int employeeClassificationKey)
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            var q = db.From<Employee>()
                .Join<EmployeeClassification>()
                .Where<EmployeeClassification>(x => x.Id == employeeClassificationKey);
            return db.Select<EmployeeDetail>(q);
        }
    }

    public IList<EmployeeDetail> FindByLastName(string lastName)
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            var q = db.From<Employee>()
                .Join<EmployeeClassification>()
                .Where(x => x.LastName == lastName);
            return db.Select<EmployeeDetail>(q);
        }
    }

    public EmployeeDetail? GetByEmployeeKey(int employeeKey)
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            var q = db.From<Employee>().Join<EmployeeClassification>()
                .Where(x => x.Id == employeeKey);
            return db.Single<EmployeeDetail>(q);
        }
    }

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