public void OrderQueryFact()
{
string expectedTemplate = "SELECT * FROM dbo.from f ";
var fromTable = new Table("dbo.from", "f");
var joinTable = new Table("dbo.join", "j");
var builder = new SqlExpressionBuilderSelect();
builder
.From(fromTable)
.Order("bla")
.Order("blubb", OrderOptions.Desc);
string expected = expectedTemplate + "ORDER BY f.bla ASC, f.blubb DESC";
Assert.Equal(expected, builder.ToString());
// Fails
bool throws = false;
builder = new SqlExpressionBuilderSelect();
builder.From(fromTable).Order("bla");
try
{
builder.Order("b.blubb", OrderOptions.Desc); // table with alias b not found
}
catch
{
throws = true;
}
Assert.True(throws);
builder
.JoinInner(joinTable, "wayne = wayne")
.Order("j.blubb", OrderOptions.Desc)
.Order("j.foo", ExpressionOptions.Overwrite)
.Order(new ColumnExpression("bla", fromTable))
.Order(new ColumnExpression("muh", fromTable), OrderOptions.Desc);
expectedTemplate += "INNER JOIN dbo.join j ON wayne = wayne ";
expected = expectedTemplate + "ORDER BY j.foo ASC, f.bla ASC, f.muh DESC";
Assert.Equal(expected, builder.ToString());
builder.Order(new ColumnExpression("bla", fromTable), ExpressionOptions.Overwrite);
expected = expectedTemplate + "ORDER BY f.bla ASC";
Assert.Equal(expected, builder.ToString());
builder.Order(new ColumnExpression("foo", fromTable), OrderOptions.Desc, ExpressionOptions.Overwrite);
expected = expectedTemplate + "ORDER BY f.foo DESC";
Assert.Equal(expected, builder.ToString());
// Fails
throws = false;
builder = new SqlExpressionBuilderSelect();
builder.From(fromTable).Order("bla");
try
{
builder.Order(new ColumnExpression("foo", joinTable)); // table does not exist in sql builder
}
catch
{
throws = true;
}
Assert.True(throws);
}