public void LeftOuterJoin()
{
EmployeeDepartmentResults r = new EmployeeDepartmentResults();
q = Select.Results(r.EmployeeID, r.EmployeeName, r.DepartmentID, r.DepartmentName).
From(e).
Columns(e.ID.As(r.EmployeeID),
e.Name.As(r.EmployeeName),
d.ID.As(r.DepartmentID),
d.Name.As(r.DepartmentName)).
LeftOuterJoin(d).On(e.DepartmentID == d.ID);
AssertSql("SELECT t0.ID EmployeeID, t0.Name EmployeeName, t1.ID DepartmentID, t1.Name DepartmentName FROM Employee t0 LEFT OUTER JOIN Department t1 ON t0.DepartmentID = t1.ID");
AssertRowCount(6);
AssertRow(1, "Jason", 1, "Engineering");
AssertRow(2, "Dorie", 1, "Engineering");
AssertRow(3, "Nikki", 1, "Engineering");
AssertRow(4, "Aaron", 2, "QA");
AssertRow(5, "Kevin", 3, "Product Management");
AssertRow(6, "Woody", DBNull.Value, DBNull.Value);
}
public void TwoTablesInnerJoinedTwoColumnsFromEachTable()
{
EmployeeDepartmentResults r = new EmployeeDepartmentResults();
q = Select.Results(r.EmployeeID, r.EmployeeName, r.DepartmentID, r.DepartmentName).
From(e).
Columns(e.ID.As(r.EmployeeID),
e.Name.As(r.EmployeeName),
d.ID.As(r.DepartmentID),
d.Name.As(r.DepartmentName)).
InnerJoin(d).On(e.DepartmentID == d.ID);
AssertSql("SELECT t0.ID EmployeeID, t0.Name EmployeeName, t1.ID DepartmentID, t1.Name DepartmentName FROM Employee t0 INNER JOIN Department t1 ON t0.DepartmentID = t1.ID");
AssertRowCount(5);
AssertRow(1, "Jason", 1, "Engineering");
AssertRow(2, "Dorie", 1, "Engineering");
AssertRow(3, "Nikki", 1, "Engineering");
AssertRow(4, "Aaron", 2, "QA");
AssertRow(5, "Kevin", 3, "Product Management");
}
