public void SetUp()
{
var sqlServerCommandFactory = new SqlServerCommandFactory(null);
_generator = new SqlCeTSqlGenerator(sqlServerCommandFactory);
_subQuery = new AliasedSqlSubQuery();
_subQuery.ExplicitlySpecifiedColumnsToSelect = new[] { "ColumnName1", "ColumnName2" };
_subQuery.TableName = "TableName";
_subQuery.PrimaryKeyColumnName = "IdColumnName";
}
public void SetUp()
{
var sqlServerCommandFactory = new SqlServerCommandFactory(null);
_generator = new SqlCeTSqlGenerator(sqlServerCommandFactory);
_subQuery = new AliasedSqlSubQuery();
_subQuery.ExplicitlySpecifiedColumnsToSelect = new[] { "ColumnName1", "ColumnName2" };
_subQuery.TableName = "TableName";
_subQuery.PrimaryKeyColumnName = "IdColumnName";
}
private void AddColumnsNotAddedByJoin(ResultSet matches, AliasedSqlSubQuery subQuery)
{
var allColumns = subQuery.AllSelectableColumnNames
.Select(x => new ColumnValue(subQuery.TableIdentifier, x, null))
.ToList();
foreach (var row in matches.Rows)
{
var missingColumns = allColumns
.Where(x => !row.HasColumnValue(x.TableName, x.ColumnName))
.Select(x => x.Copy(null))
.ToList();
row.Add(missingColumns);
}
}
public void Joins_in_a_multi_level_join_are_ordered_so_that_columns_are_not_referenced_before_the_table_has_been_joined()
{
_sqlQuery = new SqlQuery();
_subQuery = new AliasedSqlSubQuery
{
ExplicitlySpecifiedColumnsToSelect = new List<string> { "Name" },
TableName = "Blogs"
};
var spec2 = new AliasedSqlSubQuery
{
ExplicitlySpecifiedColumnsToSelect = new[] { "Name" },
TableName = "Posts",
PrimaryKeyColumnName = "Id"
};
var spec3 = new AliasedSqlSubQuery
{
ExplicitlySpecifiedColumnsToSelect = new List<string> { "Name" },
TableName = "Comments"
};
// The join between Posts and Comments is added before the join
// between Posts and Blogs, so the join would fail since not both
// the Posts table and the Comments table are "available" to join with
// before the Posts and Blogs join has been done
var join1 = new SqlSubQueryJoin
{
ParentTableName = "Posts",
ChildTableName = "Comments",
ParentPrimaryKeyColumnName = "Id",
ChildForeignKeyColumnName = "PostId",
ChildSubQuery = spec3,
ParentSubQuery = spec2
};
var join2 = new SqlSubQueryJoin
{
ParentTableName = "Blogs",
ChildTableName = "Posts",
ParentPrimaryKeyColumnName = "Id",
ChildForeignKeyColumnName = "BlogId",
ChildSubQuery = spec2,
ParentSubQuery = _subQuery
};
_sqlQuery.SubQueries.Add(_subQuery);
_sqlQuery.Joins.Add(join1);
_sqlQuery.Joins.Add(join2);
_sqlQuery.SubQueries.Add(spec2);
_sqlQuery.SubQueries.Add(spec3);
var expectedSql =
"SELECT [Blogs].[Name] AS \"Blogs Name\", " +
"[Posts].[Name] AS \"Posts Name\", " +
"[Comments].[Name] AS \"Comments Name\" " +
"FROM [Blogs] LEFT OUTER JOIN [Posts] ON [Blogs].[Id] = [Posts].[BlogId] " +
"LEFT OUTER JOIN [Comments] ON [Posts].[Id] = [Comments].[PostId]";
var query = _generator.GenerateSelectQuery(_sqlQuery);
Assert.AreEqual(expectedSql, query.CommandText);
}
public void Generating_ordered_table_join_with_constraint_adds_where_clause_before_order_by_clause()
{
_sqlQuery.AddOrderByStatement(OrderByStatement.Create("ColumnName3", OrderByDirection.Ascending, "TableName"));
_sqlQuery.AddConjunctionExpression(_subQuery.TableIdentifier, QueryExpression.Create(new EqualsExpression("ColumnName1", 123)));
var spec2 = new AliasedSqlSubQuery
{
ExplicitlySpecifiedColumnsToSelect = new List<string> { "Table2Column1" },
TableName = "TableName2"
};
var join = new SqlSubQueryJoin
{
ParentTableName = "TableName",
ChildTableName = "TableName2",
ParentPrimaryKeyColumnName = "PrimaryKeyColumnName",
ChildForeignKeyColumnName = "ForeignKeyColumnName",
ChildSubQuery = spec2,
ParentSubQuery = _subQuery
};
_sqlQuery.Joins.Add(join);
_sqlQuery.SubQueries.Add(spec2);
var expectedSql =
"SELECT [TableName].[ColumnName1] AS \"TableName ColumnName1\", [TableName].[ColumnName2] AS \"TableName ColumnName2\", " +
"[TableName2].[Table2Column1] AS \"TableName2 Table2Column1\" " +
"FROM [TableName] LEFT OUTER JOIN [TableName2] " +
"ON [TableName].[PrimaryKeyColumnName] = [TableName2].[ForeignKeyColumnName] " +
"WHERE [TableName].[ColumnName1] = @TableName_ColumnName1Constraint " +
"ORDER BY [TableName].[ColumnName3]";
var query = _generator.GenerateSelectQuery(_sqlQuery);
Assert.AreEqual(expectedSql, query.CommandText);
}
public void Generating_multi_table_join_generates_join_query_with_corresponding_join_clause()
{
_sqlQuery.AddConjunctionExpression(_subQuery.TableIdentifier, QueryExpression.Create(new EqualsExpression("ColumnName1", 123)));
var spec2 = new AliasedSqlSubQuery
{
ExplicitlySpecifiedColumnsToSelect = new List<string> { "Table2Column1", "Table2Column2" },
TableName = "TableName2"
};
var spec3 = new AliasedSqlSubQuery
{
ExplicitlySpecifiedColumnsToSelect = new List<string> { "Table3Column1" },
TableName = "TableName3"
};
var join1 = new SqlSubQueryJoin
{
ParentTableName = "TableName",
ChildTableName = "TableName2",
ParentPrimaryKeyColumnName = "PrimaryKeyColumnName",
ChildForeignKeyColumnName = "ForeignKeyColumnName",
ChildSubQuery = spec2,
ParentSubQuery = _subQuery
};
var join2 = new SqlSubQueryJoin
{
ParentTableName = "TableName2",
ChildTableName = "TableName3",
ParentPrimaryKeyColumnName = "Table2PrimaryKey",
ChildForeignKeyColumnName = "Table3ForeignKey",
ChildSubQuery = spec3,
ParentSubQuery = spec2
};
_sqlQuery.Joins.Add(join1);
_sqlQuery.Joins.Add(join2);
_sqlQuery.SubQueries.Add(spec2);
_sqlQuery.SubQueries.Add(spec3);
var expectedSql =
"SELECT [TableName].[ColumnName1] AS \"TableName ColumnName1\", [TableName].[ColumnName2] AS \"TableName ColumnName2\", " +
"[TableName2].[Table2Column1] AS \"TableName2 Table2Column1\", [TableName2].[Table2Column2] AS \"TableName2 Table2Column2\", " +
"[TableName3].[Table3Column1] AS \"TableName3 Table3Column1\" " +
"FROM [TableName] LEFT OUTER JOIN [TableName2] " +
"ON [TableName].[PrimaryKeyColumnName] = [TableName2].[ForeignKeyColumnName] " +
"LEFT OUTER JOIN [TableName3] ON [TableName2].[Table2PrimaryKey] = [TableName3].[Table3ForeignKey] " +
"WHERE [TableName].[ColumnName1] = @TableName_ColumnName1Constraint";
var query = _generator.GenerateSelectQuery(_sqlQuery);
Assert.AreEqual(expectedSql, query.CommandText);
}
public void Generating_multi_table_join_from_the_middle_outwards_generates_join_query_with_each_table_only_referenced_once()
{
_sqlQuery = new SqlQuery();
_subQuery = new AliasedSqlSubQuery
{
ExplicitlySpecifiedColumnsToSelect = new[] { "Name" },
TableName = "Posts",
PrimaryKeyColumnName = "Id"
};
var spec2 = new AliasedSqlSubQuery
{
ExplicitlySpecifiedColumnsToSelect = new List<string> { "Name" },
TableName = "Comments"
};
var spec3 = new AliasedSqlSubQuery
{
ExplicitlySpecifiedColumnsToSelect = new List<string> { "Name" },
TableName = "Blogs"
};
var join1 = new SqlSubQueryJoin
{
ParentTableName = "Posts",
ChildTableName = "Comments",
ParentPrimaryKeyColumnName = "Id",
ChildForeignKeyColumnName = "PostId",
ChildSubQuery = spec2,
ParentSubQuery = _subQuery
};
var join2 = new SqlSubQueryJoin
{
ParentTableName = "Blogs",
ChildTableName = "Posts",
ParentPrimaryKeyColumnName = "Id",
ChildForeignKeyColumnName = "BlogId",
ChildSubQuery = _subQuery,
ParentSubQuery = spec3
};
_sqlQuery.SubQueries.Add(_subQuery);
_sqlQuery.Joins.Add(join1);
_sqlQuery.Joins.Add(join2);
_sqlQuery.SubQueries.Add(spec2);
_sqlQuery.SubQueries.Add(spec3);
var expectedSql =
"SELECT [Posts].[Name] AS \"Posts Name\", " +
"[Comments].[Name] AS \"Comments Name\", " +
"[Blogs].[Name] AS \"Blogs Name\" " +
"FROM [Posts] LEFT OUTER JOIN [Comments] " +
"ON [Posts].[Id] = [Comments].[PostId] " +
"LEFT OUTER JOIN [Blogs] ON [Blogs].[Id] = [Posts].[BlogId]";
var query = _generator.GenerateSelectQuery(_sqlQuery);
Assert.AreEqual(expectedSql, query.CommandText);
}
public void Generating_join_with_order_by_generates_select_with_corresponding_order_by_with_qualified_column_name()
{
_sqlQuery.AddOrderByStatement(OrderByStatement.Create("ColumnName3", OrderByDirection.Ascending, "TableName"));
_sqlQuery.AddOrderByStatement(OrderByStatement.Create("ColumnName4", OrderByDirection.Descending, "TableName"));
var spec2 = new AliasedSqlSubQuery
{
ExplicitlySpecifiedColumnsToSelect = new List<string> { "Table2Column1" },
TableName = "TableName2"
};
var join = new SqlSubQueryJoin
{
ParentTableName = "TableName",
ChildTableName = "TableName2",
ParentPrimaryKeyColumnName = "PrimaryKeyColumnName",
ChildForeignKeyColumnName = "ForeignKeyColumnName",
ChildSubQuery = spec2,
ParentSubQuery = _subQuery
};
_sqlQuery.Joins.Add(join);
_sqlQuery.SubQueries.Add(spec2);
var query = _generator.GenerateSelectQuery(_sqlQuery);
var expectedSql =
"SELECT [TableName].[ColumnName1] AS \"TableName ColumnName1\", [TableName].[ColumnName2] AS \"TableName ColumnName2\", " +
"[TableName2].[Table2Column1] AS \"TableName2 Table2Column1\" " +
"FROM [TableName] LEFT OUTER JOIN [TableName2] " +
"ON [TableName].[PrimaryKeyColumnName] = [TableName2].[ForeignKeyColumnName] " +
"ORDER BY [TableName].[ColumnName3], [TableName].[ColumnName4] DESC";
Assert.AreEqual(expectedSql, query.CommandText);
}
public void Generating_join_with_constraints_on_multiple_tables_with_both_AND_and_OR_operators_generates_join_query_with_combined_constraints_using_expected_precedence()
{
var spec2 = new AliasedSqlSubQuery
{
ExplicitlySpecifiedColumnsToSelect = new List<string> { "Table2Column1", "Table2Column2" },
TableName = "TableName2",
Alias = "Table2Alias"
};
_sqlQuery.AddConjunctionExpression(_subQuery.TableIdentifier,
new OrExpression(
new EqualsExpression("Column1", 123),
new EqualsExpression("Column1", 345)));
_sqlQuery.AddConjunctionExpression(spec2.TableIdentifier,
QueryExpression.Create(new EqualsExpression("Table2Column1", 234)));
var join = new SqlSubQueryJoin
{
ParentTableName = "TableName",
ChildTableName = "TableName2",
ParentPrimaryKeyColumnName = "PrimaryKeyColumnName",
ChildForeignKeyColumnName = "ForeignKeyColumnName",
ChildSubQuery = spec2,
ParentSubQuery = _subQuery
};
_sqlQuery.Joins.Add(join);
_sqlQuery.SubQueries.Add(spec2);
var expectedSql =
"SELECT [TableName].[ColumnName1] AS \"TableName ColumnName1\", [TableName].[ColumnName2] AS \"TableName ColumnName2\", " +
"[Table2Alias].[Table2Column1] AS \"Table2Alias Table2Column1\", [Table2Alias].[Table2Column2] AS \"Table2Alias Table2Column2\" " +
"FROM [TableName] LEFT OUTER JOIN [TableName2] AS [Table2Alias] " +
"ON [TableName].[PrimaryKeyColumnName] = [Table2Alias].[ForeignKeyColumnName] " +
"WHERE ([TableName].[Column1] = @TableName_Column1Constraint OR [TableName].[Column1] = @TableName_Column1Constraint2) " +
"AND [Table2Alias].[Table2Column1] = @Table2Alias_Table2Column1Constraint";
var query = _generator.GenerateSelectQuery(_sqlQuery);
var actualParameters = query.Parameters.SortByParameterName();
Assert.AreEqual(expectedSql, query.CommandText);
Assert.AreEqual(3, actualParameters.Count);
Assert.AreEqual("Table2Alias_Table2Column1Constraint", actualParameters[0].ParameterName);
Assert.AreEqual(234, actualParameters[0].Value);
Assert.AreEqual("TableName_Column1Constraint", actualParameters[1].ParameterName);
Assert.AreEqual(123, actualParameters[1].Value);
Assert.AreEqual("TableName_Column1Constraint2", actualParameters[2].ParameterName);
Assert.AreEqual(345, actualParameters[2].Value);
}
public void Generating_join_with_constraints_on_aliased_table_generates_join_query_with_corresponding_constraints_qualified_with_table_alias()
{
var spec2 = new AliasedSqlSubQuery
{
ExplicitlySpecifiedColumnsToSelect = new List<string> { "Table2Column1", "Table2Column2" },
TableName = "TableName2",
Alias = "Table2Alias"
};
_sqlQuery.AddConjunctionExpression(spec2.TableIdentifier, QueryExpression.Create(
new OrExpression(
new EqualsExpression("ColumnName1", 123),
new InExpression(new PropertyExpression("ColumnName2"), new ArrayValueExpression(new[] { 1, 2 })),
new LikeExpression(new PropertyExpression("ColumnName2"), "likestring")
{
HasStartingWildCard = true,
HasEndingWildCard = true
})));
var join = new SqlSubQueryJoin
{
ParentTableName = "TableName",
ChildTableName = "TableName2",
ParentPrimaryKeyColumnName = "PrimaryKeyColumnName",
ChildForeignKeyColumnName = "ForeignKeyColumnName",
ChildSubQuery = spec2,
ParentSubQuery = _subQuery
};
_sqlQuery.Joins.Add(join);
_sqlQuery.SubQueries.Add(spec2);
var expectedSql =
"SELECT [TableName].[ColumnName1] AS \"TableName ColumnName1\", [TableName].[ColumnName2] AS \"TableName ColumnName2\", " +
"[Table2Alias].[Table2Column1] AS \"Table2Alias Table2Column1\", [Table2Alias].[Table2Column2] AS \"Table2Alias Table2Column2\" " +
"FROM [TableName] LEFT OUTER JOIN [TableName2] AS [Table2Alias] " +
"ON [TableName].[PrimaryKeyColumnName] = [Table2Alias].[ForeignKeyColumnName] " +
"WHERE [Table2Alias].[ColumnName1] = @Table2Alias_ColumnName1Constraint " +
"OR ([Table2Alias].[ColumnName2] IN (@Table2Alias_ColumnName2Constraint, @Table2Alias_ColumnName2Constraint2)) " +
"OR [Table2Alias].[ColumnName2] LIKE @Table2Alias_ColumnName2Constraint3";
var query = _generator.GenerateSelectQuery(_sqlQuery);
var actualParameters = query.Parameters.SortByParameterName();
Assert.AreEqual(expectedSql, query.CommandText);
Assert.AreEqual(4, actualParameters.Count);
Assert.AreEqual("Table2Alias_ColumnName1Constraint", actualParameters[0].ParameterName);
Assert.AreEqual(123, actualParameters[0].Value);
Assert.AreEqual("Table2Alias_ColumnName2Constraint", actualParameters[1].ParameterName);
Assert.AreEqual(1, actualParameters[1].Value);
Assert.AreEqual("Table2Alias_ColumnName2Constraint2", actualParameters[2].ParameterName);
Assert.AreEqual(2, actualParameters[2].Value);
Assert.AreEqual("Table2Alias_ColumnName2Constraint3", actualParameters[3].ParameterName);
Assert.AreEqual("%likestring%", actualParameters[3].Value);
}
public void Generating_join_without_constraints_generates_select_of_properties_from_both_tables_with_join_on_child_foreign_key_and_parent_id()
{
var spec2 = new AliasedSqlSubQuery
{
ExplicitlySpecifiedColumnsToSelect = new List<string> { "Table2Column1", "Table2Column2" },
TableName = "TableName2"
};
var join = new SqlSubQueryJoin
{
ParentTableName = "TableName",
ChildTableName = "TableName2",
ParentPrimaryKeyColumnName = "PrimaryKeyColumnName",
ChildForeignKeyColumnName = "ForeignKeyColumnName",
ChildSubQuery = spec2,
ParentSubQuery = _subQuery
};
_sqlQuery.Joins.Add(join);
_sqlQuery.SubQueries.Add(spec2);
var query = _generator.GenerateSelectQuery(_sqlQuery);
var expectedSql =
"SELECT [TableName].[ColumnName1] AS \"TableName ColumnName1\", [TableName].[ColumnName2] AS \"TableName ColumnName2\", " +
"[TableName2].[Table2Column1] AS \"TableName2 Table2Column1\", [TableName2].[Table2Column2] AS \"TableName2 Table2Column2\" " +
"FROM [TableName] LEFT OUTER JOIN [TableName2] " +
"ON [TableName].[PrimaryKeyColumnName] = [TableName2].[ForeignKeyColumnName]";
Assert.AreEqual(expectedSql, query.CommandText);
}
