Ejemplo n.º 1
0
        public void ItCanSupportFlippingOrdersForInvertedComparisonWithNull()
        {
            var stream =
                S.SelectNode(
                    S.Select(S.Star <Person>()),
                    S.From <Person>(),
                    S.Where(
                        S.BinExp(S.Null(), BinaryOperation.NotEqual, S.Col <Person>("Age"))));
            var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream);

            Assert.That(result, Is.EqualTo("SELECT PersonRef.* FROM Person PersonRef WHERE PersonRef.Age IS NOT NULL"));
        }
Ejemplo n.º 2
0
        public void ItShouldBePossibleToDoPagingWithACommonTableExpression()
        {
            // To be honest, this case should be covered by the other test cases so this test case is a bit redundant.
            // However, using common table expressions for paging is a quite common technique and it's good to know for sure that it works as expected, so
            // let's do some bad practice testing and test something that's already covered by other tests!

            var expr =
                S.SelectNode(
                    S.With(
                        S.TableDef <PersonLitePagingResult>(
                            S.Select(
                                S.Col <Person>("Name"),
                                S.Col <Person>("Age"),
                                S.AliasedCol(
                                    "RowNumber",
                                    AggregateType.RowNumber,
                                    S.Null(),
                                    orderBy: new[] { S.OrderClause(S.Col <Person>("Age"), AST.Direction.Ascending) })),
                            S.From <Person>())
                        ),
                    S.Select(S.Col <PersonLitePagingResult>("Name"), S.Col <PersonLitePagingResult>("Age")),
                    S.From <PersonLitePagingResult>(),
                    S.Where(
                        S.BinExp(
                            S.BinExp(S.Col <PersonLitePagingResult>("RowNumber"), BinaryOperation.GreaterThanOrEqual, S.Const(10)),
                            BinaryOperation.And,
                            S.BinExp(S.Col <PersonLitePagingResult>("RowNumber"), BinaryOperation.LessThan, S.Const(20))
                            )));

            var result = SqlGen.SqlifyExpression(Dialects.AnsiSql.Dialect, expr);

            var expected =
                "WITH PersonLitePagingResult AS (" +
                "SELECT PersonRef.Name, PersonRef.Age, ROW_NUMBER() OVER (ORDER BY PersonRef.Age ASC) AS RowNumber " +
                "FROM Person PersonRef" +
                ") " +
                "SELECT PersonLitePagingResultRef.Name, PersonLitePagingResultRef.Age " +
                "FROM PersonLitePagingResult PersonLitePagingResultRef " +
                "WHERE PersonLitePagingResultRef.RowNumber >= 10 AND PersonLitePagingResultRef.RowNumber < 20";

            Assert.That(result, Is.EqualTo(expected));
        }
Ejemplo n.º 3
0
        public void ItShouldBePossibleToGroupByColumnsFromSeveralTables()
        {
            var stream =
                S.SelectNode(
                    S.Select(S.Col <Person>("Age"), S.Col <Car>("Brand"), S.Aggregate(AggregateType.Count, S.Null())),
                    S.From <Person>(
                        S.Join <Person, Car>(
                            JoinType.InnerJoin,
                            S.BinExp(S.Col <Person>("Id"), BinaryOperation.Equal, S.Col <Car>("DriverId")))),
                    groupBy: S.GroupBy(S.Col <Person>("Age"), S.Col <Car>("Brand")));
            var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream);

            Assert.That(
                result,
                Is.EqualTo(
                    "SELECT PersonRef.Age, CarRef.Brand, COUNT(*) " +
                    "FROM Person PersonRef " +
                    "INNER JOIN Car CarRef ON PersonRef.Id = CarRef.DriverId " +
                    "GROUP BY PersonRef.Age, CarRef.Brand"));
        }