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 JoinLeftQueryFact()
{
var fromTable = new Table("dbo.from", "f");
var joinTable1 = new Table("dbo.join1", "j1");
var joinTable3 = new Table("dbo.join3", "j3");
var joinTable4 = new Table("dbo.join4", "j4");
var joinTable5 = new Table("dbo.join5", "j5");
var joinTable6 = new Table("dbo.join6", "j6");
var joinTable7 = new Table("dbo.join7", "j7");
var joinTable8 = new Table("dbo.join8", "j8");
var joinTable9 = new Table("dbo.join9", "j9");
var joinTableA = new Table("dbo.joinA", "jA");
var builder = new SqlExpressionBuilderSelect();
builder.From(fromTable);
builder.JoinLeft(joinTable1, "f.Id = j1.f_Id");
builder.JoinLeft("dbo.join2", "j2", "j1.Id = j2.j1_Id");
builder.JoinLeft(joinTable3, "j2.Id = j3.j2_Id", new[] { "col1", "col2", "col3" });
builder.JoinLeft(
joinTable4,
"j3.Id = j4.j3_Id AND j4.col1 = @param1",
new List <SqlParameter> {
new SqlParameter {
ParameterName = "@param1"
}
});
builder.JoinLeft(
joinTable5,
"j4.Id = j5.j4_Id AND j5.col1 = @param2",
new[] { "col1", "col2" },
new[] { new SqlParameter {
ParameterName = "@param2"
} });
builder.JoinLeft(
new Compare <ColumnExpression, ColumnExpression>(
new ColumnExpression("Id", joinTable5),
new ColumnExpression("j5_Id", joinTable6)));
builder.JoinLeft(
new Compare <ColumnExpression, ColumnExpression>(
new ColumnExpression("Id", joinTable6),
new ColumnExpression("j6_Id", joinTable7)),
new[] { "col1", "col2" });
builder.JoinLeft(joinTable7.GetColumn("Id"), joinTable8.GetColumn("j7_Id"));
builder.JoinLeft(joinTable8.GetColumn("Id"), joinTable9.GetColumn("j8_Id"), new[] { "col1", "col2" });
builder.JoinLeft(joinTable9.GetColumn("Id"), joinTableA.GetColumn("j9_Id"), CompareOperations.Equals);
const string expected =
"SELECT j3.col1, j3.col2, j3.col3, j5.col1, j5.col2, j7.col1, j7.col2, j9.col1, j9.col2 " +
"FROM dbo.from f " +
"LEFT JOIN dbo.join1 j1 ON f.Id = j1.f_Id " +
"LEFT JOIN dbo.join2 j2 ON j1.Id = j2.j1_Id " +
"LEFT JOIN dbo.join3 j3 ON j2.Id = j3.j2_Id " +
"LEFT JOIN dbo.join4 j4 ON j3.Id = j4.j3_Id AND j4.col1 = @param1 " +
"LEFT JOIN dbo.join5 j5 ON j4.Id = j5.j4_Id AND j5.col1 = @param2 " +
"LEFT JOIN dbo.join6 j6 ON j5.Id = j6.j5_Id " +
"LEFT JOIN dbo.join7 j7 ON j6.Id = j7.j6_Id " +
"LEFT JOIN dbo.join8 j8 ON j7.Id = j8.j7_Id " +
"LEFT JOIN dbo.join9 j9 ON j8.Id = j9.j8_Id " +
"LEFT JOIN dbo.joinA jA ON j9.Id = jA.j9_Id";
string actual = builder.ToString();
Assert.Equal(expected, actual);
}