public void AddJoin(ObjectSubQueryJoin joinSpecification, string childAlias, string parentAlias) { EnsureSubQuery(childAlias, joinSpecification.ChildType); EnsureSubQuery(parentAlias, joinSpecification.ParentType); string manyToOneForeignKeyColumnName; if (joinSpecification.HasChildProperty) { manyToOneForeignKeyColumnName = _conventionReader.GetManyToOneForeignKeyColumnName(joinSpecification.ChildProperty); } else { manyToOneForeignKeyColumnName = _conventionReader.GetColumnName(joinSpecification.ChildToParentForeignKeyProperty); } var joinSpec = new SqlSubQueryJoin { ChildTableName = _conventionReader.GetTableName(joinSpecification.ChildType), ParentTableName = _conventionReader.GetTableName(joinSpecification.ParentType), ChildForeignKeyColumnName = manyToOneForeignKeyColumnName, ParentPrimaryKeyColumnName = _conventionReader.GetPrimaryKeyColumnName(joinSpecification.ParentType), }; AddJoin(joinSpec, childAlias, parentAlias); ObjectRelations.Add(ObjectRelation.Create(joinSpecification, childAlias, parentAlias)); }
public void AddJoin(SqlSubQueryJoin joinSpec, string childAlias, string parentAlias) { joinSpec.ParentSubQuery = GetSubQuery(joinSpec.ParentTableName, parentAlias); joinSpec.ChildSubQuery = GetSubQuery(joinSpec.ChildTableName, childAlias); Joins.Add(joinSpec); }
private string CreateJoinClause(SqlSubQueryJoin joinSpec, AliasedSqlSubQuery newSubQuery) { return(string.Format("LEFT OUTER JOIN {0} ON {1}.{2} = {3}.{4}", FromClauseTableIdentifier(newSubQuery), Escape(joinSpec.ParentSubQuery.TableIdentifier), Escape(joinSpec.ParentPrimaryKeyColumnName), Escape(joinSpec.ChildSubQuery.TableIdentifier), Escape(joinSpec.ChildForeignKeyColumnName))); }
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); }