public void SelectStatement_ThrowsIfWrongColumnName()
{
var builder = new SqlExpressionBuilderSelect();
builder.From("table", "t");
Assert.Throws <ArgumentException>(() => builder.Select("t.b.foo"));
}
public void SelectStatementFact()
{
var fromTable = new Table("dbo.from", "f");
var joinTable = new Table("dbo.join", "j");
var builder = new SqlExpressionBuilderSelect();
builder
.From(fromTable)
.Select("bla")
.Select(new[] { "f.blubb", "f.foo" });
Assert.Equal("SELECT f.bla, f.blubb, f.foo FROM dbo.from f", builder.ToString());
builder.Select(new[] { "f.muh" }, ExpressionOptions.Overwrite);
Assert.Equal("SELECT f.muh FROM dbo.from f", builder.ToString());
builder.Select("maeh", fromTable);
Assert.Equal("SELECT f.muh, f.maeh FROM dbo.from f", builder.ToString());
builder.JoinLeft(joinTable, "was = wer");
builder.Select(new[] { "muh", "maeh", "foo" }, joinTable);
Assert.Equal(
"SELECT f.muh, f.maeh, j.muh, j.maeh, j.foo FROM dbo.from f LEFT JOIN dbo.join j ON was = wer",
builder.ToString());
builder.Select(new[] { "foo", "bar" }, joinTable, ExpressionOptions.Overwrite);
Assert.Equal(
"SELECT j.foo, j.bar FROM dbo.from f LEFT JOIN dbo.join j ON was = wer", builder.ToString());
builder.Select(new AliasedExpression <Expression>(new Expression("CONCAT(Street, Number)"), "Address"));
Assert.Equal(
"SELECT j.foo, j.bar, CONCAT(Street, Number) AS Address " +
"FROM dbo.from f LEFT JOIN dbo.join j ON was = wer",
builder.ToString());
builder.Distinct();
Assert.Equal(
"SELECT DISTINCT j.foo, j.bar, CONCAT(Street, Number) AS Address " +
"FROM dbo.from f LEFT JOIN dbo.join j ON was = wer",
builder.ToString());
}
public void GroupFacts()
{
var builder = new SqlExpressionBuilderSelect();
var fromTable = new Table("table", "t");
var joinTable = new Table("join", "j");
builder.From(fromTable);
builder.Select("Id", fromTable);
builder.Select(new AliasedExpression <CountExpression>(
new CountExpression(joinTable.GetColumn("moo")), "count"));
builder.JoinLeft(fromTable.GetColumn("Id"), joinTable.GetColumn("fromId"));
builder.Group(new[] { "t.Id" });
const string expected =
"SELECT t.Id, COUNT(j.moo) AS count " +
"FROM table t " +
"LEFT JOIN join j ON t.Id = j.fromId " +
"GROUP BY t.Id";
Assert.Equal(expected, builder.ToString());
}
public void SelectStatement_ThrowsIfNoDefaultTableSelected()
{
var builder = new SqlExpressionBuilderSelect();
Assert.Throws <InvalidOperationException>(() => builder.Select("foo"));
}