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); }