public void JoinReferencedTableExistsCheck_ExceptionDoesNotThrowFact()
{
var fromTable = new Table("from", "f");
var joinTable1 = new Table("join1", "j1");
// Works
var builder = new SqlExpressionBuilderSelect();
builder.From(fromTable);
Assert.DoesNotThrow(() => builder.JoinInner(fromTable.GetColumn("bla"), joinTable1.GetColumn("bla")));
}
public void JoinReferencedTableExistsCheck_ExceptionThrownOnReferencedTableNotFirstArgumentFact()
{
var fromTable = new Table("from", "f");
var joinTable1 = new Table("join1", "j1");
var builder = new SqlExpressionBuilderSelect();
builder.From(fromTable);
// first table has to be the referenced table
Assert.Throws <InvalidOperationException>(
() => builder.JoinInner(joinTable1.GetColumn("bla"), fromTable.GetColumn("bla")));
}
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);
}
public void JoinInnerQueryFact()
{
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.JoinInner(joinTable1, "f.Id = j1.f_Id");
builder.JoinInner("dbo.join2", "j2", "j1.Id = j2.j1_Id");
builder.JoinInner(joinTable3, "j2.Id = j3.j2_Id", new[] { "col1", "col2", "col3" });
builder.JoinInner(
joinTable4,
"j3.Id = j4.j3_Id AND j4.col1 = @param1",
new List <SqlParameter> {
new SqlParameter {
ParameterName = "@param1"
}
});
builder.JoinInner(
joinTable5,
"j4.Id = j5.j4_Id AND j5.col1 = @param2",
new[] { "col1", "col2" },
new[] { new SqlParameter {
ParameterName = "@param2"
} });
builder.JoinInner(
new Compare <ColumnExpression, ColumnExpression>(
new ColumnExpression("Id", joinTable5),
new ColumnExpression("j5_Id", joinTable6)));
builder.JoinInner(
new Compare <ColumnExpression, ColumnExpression>(
new ColumnExpression("Id", joinTable6),
new ColumnExpression("j6_Id", joinTable7)),
new[] { "col1", "col2" });
builder.JoinInner(joinTable7.GetColumn("Id"), joinTable8.GetColumn("j7_Id"));
builder.JoinInner(joinTable8.GetColumn("Id"), joinTable9.GetColumn("j8_Id"), new[] { "col1", "col2" });
builder.JoinInner(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 " +
"INNER JOIN dbo.join1 j1 ON f.Id = j1.f_Id " +
"INNER JOIN dbo.join2 j2 ON j1.Id = j2.j1_Id " +
"INNER JOIN dbo.join3 j3 ON j2.Id = j3.j2_Id " +
"INNER JOIN dbo.join4 j4 ON j3.Id = j4.j3_Id AND j4.col1 = @param1 " +
"INNER JOIN dbo.join5 j5 ON j4.Id = j5.j4_Id AND j5.col1 = @param2 " +
"INNER JOIN dbo.join6 j6 ON j5.Id = j6.j5_Id " +
"INNER JOIN dbo.join7 j7 ON j6.Id = j7.j6_Id " +
"INNER JOIN dbo.join8 j8 ON j7.Id = j8.j7_Id " +
"INNER JOIN dbo.join9 j9 ON j8.Id = j9.j8_Id " +
"INNER JOIN dbo.joinA jA ON j9.Id = jA.j9_Id";
string actual = builder.ToString();
Assert.Equal(expected, actual);
}
