public void AndCriteria()
{
EmployeeResults r = new EmployeeResults();
q = Select.Results(r.ID, r.Name).
From(e).
Columns(e.ID.As(r.ID), e.Name.As(r.Name)).
Where(e.Gender == "F").
Where(e.DepartmentID == 1);
AssertSql("SELECT ID, Name FROM Employee WHERE (Gender = 'F') AND (DepartmentID = 1)");
AssertRowCount(2);
AssertRow(2, "Dorie");
AssertRow(3, "Nikki");
}
public void CanSelectSubsetOfResults()
{
EmployeeResults r = new EmployeeResults();
q = Select.Results(r.ID, r.Name).
From(e).
Columns(e.ID.As(r.ID),
e.Name.As(r.Name));
AssertSql("SELECT ID, Name FROM Employee");
AssertRowCount(6);
AssertRow(1, "Jason");
AssertRow(2, "Dorie");
AssertRow(3, "Nikki");
AssertRow(4, "Aaron");
AssertRow(5, "Kevin");
AssertRow(6, "Woody");
}
public void IntColumnIsLessThanOrEqualToValue()
{
EmployeeResults r = new EmployeeResults();
q = Select.Results(r.ID, r.Name).
From(e).
Columns(e.ID.As(r.ID), e.Name.As(r.Name)).
Where(e.DepartmentID <= 2);
AssertSql("SELECT ID, Name FROM Employee WHERE DepartmentID <= 2");
AssertRowCount(4);
AssertRow(1, "Jason");
AssertRow(2, "Dorie");
AssertRow(3, "Nikki");
AssertRow(4, "Aaron");
}
public void IntColumnIsGreaterThanOrEqualToValue()
{
EmployeeResults r = new EmployeeResults();
q = Select.Results(r.ID, r.Name).
From(e).
Columns(e.ID.As(r.ID), e.Name.As(r.Name)).
Where(e.DepartmentID >= 2);
AssertSql("SELECT ID, Name FROM Employee WHERE DepartmentID >= 2");
AssertRowCount(2);
AssertRow(4, "Aaron");
AssertRow(5, "Kevin");
}
public void StringColumnIsNotEqualToValue()
{
EmployeeResults r = new EmployeeResults();
q = Select.Results(r.ID, r.Name).
From(e).
Columns(e.ID.As(r.ID), e.Name.As(r.Name)).
Where(e.Name != "Jason");
AssertSql("SELECT ID, Name FROM Employee WHERE Name <> 'Jason'");
AssertRowCount(5);
AssertRow(2, "Dorie");
AssertRow(3, "Nikki");
AssertRow(4, "Aaron");
AssertRow(5, "Kevin");
AssertRow(6, "Woody");
}
public void OneTableTwoColumnsWithTwoOrderBysOneAscendingAndOneDescending()
{
EmployeeResults r = new EmployeeResults();
q = Select.Results(r.ID, r.Name).
From(e).
Columns(e.ID.As(r.ID), e.Name.As(r.Name)).
OrderBy(e.DepartmentID, r.Name.Descending());
AssertSql("SELECT ID, Name FROM Employee ORDER BY DepartmentID, Name DESC");
AssertRowCount(6);
AssertRow(6, "Woody");
AssertRow(3, "Nikki");
AssertRow(1, "Jason");
AssertRow(2, "Dorie");
AssertRow(4, "Aaron");
AssertRow(5, "Kevin");
}
public void NotInList()
{
EmployeeResults r = new EmployeeResults();
q = Select.Results(r.ID, r.Name).
From(e).
Columns(e.ID.As(r.ID), e.Name.As(r.Name)).
Where(e.DepartmentID.IsNotIn(1, 2));
AssertSql("SELECT ID, Name FROM Employee WHERE DepartmentID NOT IN (1, 2)");
AssertRowCount(2);
AssertRow(5, "Kevin");
AssertRow(6, "Woody");
}
public void MinusInWhere()
{
EmployeeResults r = new EmployeeResults();
q = Select.Results(r.ID).
From(e).
Columns(e.ID.As(r.ID)).
Where(e.ID - e.DepartmentID == 2);
AssertSql("SELECT ID FROM Employee WHERE (ID - DepartmentID) = 2");
AssertRowCount(3);
AssertRow(3);
AssertRow(4);
AssertRow(5);
}
public void LiteralColumn()
{
EmployeeResults r = new EmployeeResults();
q = Select.Results(r.ID, r.Name, r.DepartmentID).
From(e).
Columns(e.ID.As(r.ID),
e.Name.As(r.Name),
Literal.IntValue(1).As(r.DepartmentID));
AssertSql("SELECT ID, Name, 1 DepartmentID FROM Employee");
AssertRowCount(6);
AssertRow(1, "Jason", 1);
AssertRow(2, "Dorie", 1);
AssertRow(3, "Nikki", 1);
AssertRow(4, "Aaron", 1);
AssertRow(5, "Kevin", 1);
AssertRow(6, "Woody", 1);
}
public void GroupBy()
{
EmployeeResults r = new EmployeeResults();
q = Select.Results(r.DepartmentID).
From(e).
Columns(e.DepartmentID.As(r.DepartmentID)).
GroupBy(e.DepartmentID);
AssertSql("SELECT DepartmentID FROM Employee GROUP BY DepartmentID");
AssertRowCount(4);
AssertRow(1);
AssertRow(2);
AssertRow(3);
AssertRow(DBNull.Value);
}
public void DistinctWithTwoColumns()
{
EmployeeResults r = new EmployeeResults();
q = Select.Results(r.Gender, r.DepartmentID).
From(e).
Distinct().
Columns(e.Gender.As(r.Gender), e.DepartmentID.As(r.DepartmentID));
AssertSql("SELECT DISTINCT Gender, DepartmentID FROM Employee");
AssertRowCount(5);
AssertRow("M", 1);
AssertRow("F", 1);
AssertRow("M", 2);
AssertRow("M", 3);
AssertRow("M", DBNull.Value);
}
public void Distinct()
{
EmployeeResults r = new EmployeeResults();
q = Select.Results(r.DepartmentID).
From(e).
Distinct().
Columns(e.DepartmentID.As(r.DepartmentID));
AssertSql("SELECT DISTINCT DepartmentID FROM Employee");
AssertRowCount(4);
AssertRow(1);
AssertRow(2);
AssertRow(3);
AssertRow(DBNull.Value);
}
public void DecimalColumnIsEqualToValueInCultureThatUsesCommas()
{
CultureInfo previousCulture = Thread.CurrentThread.CurrentCulture;
Thread.CurrentThread.CurrentCulture = CultureInfo.GetCultureInfo("nn-NO");
EmployeeResults r = new EmployeeResults();
q = Select.Results(r.ID, r.Name).
From(e).
Columns(e.ID.As(r.ID), e.Name.As(r.Name)).
Where(e.Rate == 123.45m);
AssertSql("SELECT ID, Name FROM Employee WHERE Rate = 123.45");
AssertRowCount(2);
AssertRow(1, "Jason");
AssertRow(6, "Woody");
Thread.CurrentThread.CurrentCulture = previousCulture;
}
public void DecimalColumnIsEqualToValue()
{
EmployeeResults r = new EmployeeResults();
q = Select.Results(r.ID, r.Name).
From(e).
Columns(e.ID.As(r.ID), e.Name.As(r.Name)).
Where(e.Rate == 123.45m);
AssertSql("SELECT ID, Name FROM Employee WHERE Rate = 123.45");
AssertRowCount(2);
AssertRow(1, "Jason");
AssertRow(6, "Woody");
}
public void DateColumnIsLessThanOrEqualToValue()
{
EmployeeResults r = new EmployeeResults();
q = Select.Results(r.ID, r.Name).
From(e).
Columns(e.ID.As(r.ID), e.Name.As(r.Name)).
Where(e.HireDate <= new DateTime(2009, 1, 28));
AssertSql("SELECT ID, Name FROM Employee WHERE HireDate <= '2009-01-28'");
AssertSqlServer("SELECT ID, Name FROM Employee WHERE HireDate <= '20090128'");
AssertOracle("SELECT ID, Name FROM Employee WHERE HireDate <= TO_DATE('2009-01-28', 'YYYY-MM-DD')");
AssertRowCount(3);
AssertRow(1, "Jason");
AssertRow(2, "Dorie");
AssertRow(3, "Nikki");
}
public void IntColumnIsNotEqualToNull()
{
EmployeeResults r = new EmployeeResults();
q = Select.Results(r.ID, r.Name).
From(e).
Columns(e.ID.As(r.ID), e.Name.As(r.Name)).
Where(e.DepartmentID != null);
AssertSql("SELECT ID, Name FROM Employee WHERE DepartmentID IS NOT NULL");
AssertRowCount(5);
AssertRow(1, "Jason");
AssertRow(2, "Dorie");
AssertRow(3, "Nikki");
AssertRow(4, "Aaron");
AssertRow(5, "Kevin");
}
public void IntColumnIsNotEqualToValue()
{
EmployeeResults r = new EmployeeResults();
q = Select.Results(r.ID, r.Name).
From(e).
Columns(e.ID.As(r.ID), e.Name.As(r.Name)).
Where(e.DepartmentID != 1);
AssertSql("SELECT ID, Name FROM Employee WHERE DepartmentID <> 1");
AssertRowCount(3);
AssertRow(4, "Aaron");
AssertRow(5, "Kevin");
AssertRow(6, "Woody");
}
public void GroupByWithTwoColumns()
{
EmployeeResults r = new EmployeeResults();
q = Select.Results(r.Gender, r.DepartmentID).
From(e).
Columns(e.Gender.As(r.Gender), e.DepartmentID.As(r.DepartmentID)).
GroupBy(e.Gender, e.DepartmentID);
AssertSql("SELECT Gender, DepartmentID FROM Employee GROUP BY Gender, DepartmentID");
AssertRowCount(5);
AssertRow("M", 1);
AssertRow("F", 1);
AssertRow("M", 2);
AssertRow("M", 3);
AssertRow("M", DBNull.Value);
}
public void MinOfNullColumnWithGroupByGender()
{
EmployeeResults r = new EmployeeResults();
q = Select.Results(r.DepartmentID, r.Gender).
From(e).
Columns(Min.Of(e.DepartmentID).As(r.DepartmentID), e.Gender.As(r.Gender)).
GroupBy(e.Gender);
AssertSql("SELECT MIN(DepartmentID) DepartmentID, Gender FROM Employee GROUP BY Gender");
AssertRowCount(2);
AssertRow(1, "M");
AssertRow(1, "F");
}
public void InList()
{
EmployeeResults r = new EmployeeResults();
q = Select.Results(r.ID, r.Name).
From(e).
Columns(e.ID.As(r.ID), e.Name.As(r.Name)).
Where(e.DepartmentID.IsIn(1, 2));
AssertSql("SELECT ID, Name FROM Employee WHERE DepartmentID IN (1, 2)");
AssertRowCount(4);
AssertRow(1, "Jason");
AssertRow(2, "Dorie");
AssertRow(3, "Nikki");
AssertRow(4, "Aaron");
}
public void MinusTwoDatesInWhere()
{
EmployeeResults r = new EmployeeResults();
q = Select.Results(r.ID).
From(e).
Columns(e.ID.As(r.ID)).
Where(Literal.DateValue(new DateTime(2009, 1, 28)) - e.HireDate >= 100);
// TODO: I think the default formatter shouldn't support this operation.
AssertSql("SELECT ID FROM Employee WHERE ('2009-01-28' - HireDate) >= 100");
// TODO: I don't think SQL Server supports subtracting two dates. Might need to use DATEDIFF.
AssertSqlServer("SELECT ID FROM Employee WHERE ('20090128' - HireDate) >= 100");
AssertOracle("SELECT ID FROM Employee WHERE (TO_DATE('2009-01-28', 'YYYY-MM-DD') - HireDate) >= 100");
AssertRowCount(2);
AssertRow(2);
AssertRow(3);
}
public void InNonCorrelatedSubQuery()
{
EmployeeResults r = new EmployeeResults();
EmployeeProjectResults r2 = new EmployeeProjectResults();
q = Select.Results(r.ID, r.Name).
From(e).
Columns(e.ID.As(r.ID), e.Name.As(r.Name)).
Where(e.ID.IsIn(Select.Results(r2.EmployeeID).
From(ep).
Columns(ep.EmployeeID.As(r2.EmployeeID))));
AssertSql("SELECT t0.ID ID, t0.Name Name FROM Employee t0 WHERE t0.ID IN (SELECT t1.EmployeeID EmployeeID FROM EmployeeProject t1)");
AssertRowCount(4);
AssertRow(1, "Jason");
AssertRow(2, "Dorie");
AssertRow(3, "Nikki");
AssertRow(6, "Woody");
}
public void OneTableOneColumn()
{
EmployeeResults r = new EmployeeResults();
q = Select.Results(r.Name).
From(e).
Columns(e.Name.As(r.Name));
AssertSql("SELECT Name FROM Employee");
AssertRowCount(6);
AssertRow("Jason");
AssertRow("Dorie");
AssertRow("Nikki");
AssertRow("Aaron");
AssertRow("Kevin");
AssertRow("Woody");
}
public void IntColumnIsEqualToNull()
{
EmployeeResults r = new EmployeeResults();
q = Select.Results(r.ID, r.Name).
From(e).
Columns(e.ID.As(r.ID), e.Name.As(r.Name)).
Where(e.DepartmentID == null);
AssertSql("SELECT ID, Name FROM Employee WHERE DepartmentID IS NULL");
AssertRowCount(1);
AssertRow(6, "Woody");
}
public void StringColumnIsEqualToValue()
{
EmployeeResults r = new EmployeeResults();
q = Select.Results(r.ID, r.Name).
From(e).
Columns(e.ID.As(r.ID), e.Name.As(r.Name)).
Where(e.Name == "Jason");
AssertSql("SELECT ID, Name FROM Employee WHERE Name = 'Jason'");
AssertRowCount(1);
AssertRow(1, "Jason");
}
public void StringColumnIsEqualToValueContainingSingleQuote()
{
EmployeeResults r = new EmployeeResults();
q = Select.Results(r.ID, r.Name).
From(e).
Columns(e.ID.As(r.ID), e.Name.As(r.Name)).
Where(e.Name == "O'Reilly");
AssertSql("SELECT ID, Name FROM Employee WHERE Name = 'O''Reilly'");
AssertRowCount(0);
}