예제 #1
0
        public void ItShouldHandleASimpleUnionAll()
        {
            var stream =
                AST.SqlExpression.NewSelect(
                    AST.SelectExpression.NewPlain(
                        AST.PlainSelectExpression.NewSet(
                            S.UnionAll(
                                S.PlainSelect(
                                    S.Select(S.Star <Person>()),
                                    S.From <Person>(),
                                    S.Where(
                                        S.BinExp(S.Col <Person>("Age"), AST.BinaryOperation.GreaterThan, S.Const(42)))),
                                S.PlainSelect(
                                    S.Select(S.Star <Person>()),
                                    S.From <Person>(),
                                    S.Where(
                                        S.BinExp(S.Col <Person>("Name"), AST.BinaryOperation.Equal, S.Const("'Kalle'"))))
                                ))));
            var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream);

            var expected =
                "SELECT PersonRef.* FROM Person PersonRef WHERE PersonRef.Age > 42 " +
                "UNION ALL " +
                "SELECT PersonRef.* FROM Person PersonRef WHERE PersonRef.Name = 'Kalle'";

            Assert.That(result, Is.EqualTo(expected));
        }
예제 #2
0
        public void ItShouldBePossibleToDoARecursiveCommonTableExpression()
        {
            var stream =
                S.SelectNode(
                    S.With(
                        S.TableDef <RecursivePerson>(
                            S.UnionAll(
                                S.PlainSelect(
                                    S.Select(
                                        S.AliasedCol(S.Const(0), "Level"),
                                        S.Col <Person>("Name"),
                                        S.Col <Person>("ParentId")),
                                    S.From <Person>(),
                                    S.Where(
                                        S.BinExp(S.Col <Person>("Name"), BinaryOperation.Equal, S.Const("'Kalle'")))),
                                S.PlainSelect(
                                    S.Select(
                                        S.AliasedCol(
                                            S.BinExp(S.Col <RecursivePerson>("Level"), BinaryOperation.Add, S.Const(1)), "Level"),
                                        S.Col <Person>("Name"),
                                        S.Col <Person>("ParentId")),
                                    S.From <Person>(
                                        S.Join <Person, RecursivePerson>(
                                            AST.JoinType.InnerJoin,
                                            S.BinExp(S.Col <Person>("Id"), BinaryOperation.Equal, S.Col <RecursivePerson>("ParentId")))))))),
                    S.PlainSelect(
                        S.Select(S.Star <RecursivePerson>()),
                        S.From <RecursivePerson>()));
            var result = SqlGen.SqlifyExpression(Dialects.AnsiSql.Dialect, stream);

            var expected =
                "WITH RecursivePerson AS (" +
                "SELECT 0 AS Level, PersonRef.Name, PersonRef.ParentId FROM Person PersonRef WHERE PersonRef.Name = 'Kalle' " +
                "UNION ALL " +
                "SELECT RecursivePersonRef.Level + 1 AS Level, PersonRef.Name, PersonRef.ParentId " +
                "FROM Person PersonRef " +
                "INNER JOIN RecursivePerson RecursivePersonRef ON PersonRef.Id = RecursivePersonRef.ParentId" +
                ") " +
                "SELECT RecursivePersonRef.* FROM RecursivePerson RecursivePersonRef";

            Assert.That(result, Is.EqualTo(expected));
        }