Ejemplo n.º 1
0
        public void ItShouldBePossibleToPartitionByMultipleColumns()
        {
            var expr =
                S.SelectNode(
                    S.Select(
                        S.Col <Person>("Name"),
                        S.WinCol(
                            AggregateType.Max,
                            S.Col <Person>("Age"),
                            partitionBy: new[] { S.Col <Person>("Name"), S.Col <Person>("LivesAtHouseId"), })),
                    S.From <Person>()
                    );
            var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, expr);

            Assert.That(result, Is.EqualTo("SELECT PersonRef.Name, MAX(PersonRef.Age) OVER (PARTITION BY PersonRef.Name, PersonRef.LivesAtHouseId) FROM Person PersonRef"));
        }
Ejemplo n.º 2
0
        public void ItShouldBePossibleToBothPartitionAndOrderByAColumn()
        {
            var expr =
                S.SelectNode(
                    S.Select(
                        S.Col <Person>("Name"),
                        S.WinCol(
                            AggregateType.Sum,
                            S.Col <Person>("Age"),
                            partitionBy: new[] { S.Col <Person>("Name") },
                            orderBy: new[] { S.OrderClause(S.Col <Person>("Age"), Direction.Ascending) }
                            )),
                    S.From <Person>());
            var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, expr);

            Assert.That(result, Is.EqualTo("SELECT PersonRef.Name, SUM(PersonRef.Age) OVER (PARTITION BY PersonRef.Name ORDER BY PersonRef.Age ASC) FROM Person PersonRef"));
        }
Ejemplo n.º 3
0
        public void ItShouldBePossibleToSqlifyAQueryWithAnInSubExpression()
        {
            var stream =
                S.SelectNode(
                    S.Select(S.Star <Person>()),
                    S.From <Person>(),
                    S.Where(
                        S.BinExp(
                            S.Col <Person>("Id"),
                            BinaryOperation.In,
                            S.SubExp(
                                S.Select(S.Col <Car>("DriverId")),
                                S.From <Car>()))));
            var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream);

            Assert.That(result, Is.EqualTo(@"SELECT PersonRef.* FROM Person PersonRef WHERE PersonRef.Id IN (SELECT CarRef.DriverId FROM Car CarRef)"));
        }
Ejemplo n.º 4
0
        public void ItShouldBeAbleToSqlifyMultipleTableJoins()
        {
            var stream =
                S.SelectNode(
                    S.Select(S.Star <Person>(), S.Star <Car>(), S.Star <House>()),
                    S.From <House>(
                        S.Join <House, Person>(JoinType.InnerJoin, S.BinExp(S.Col <House>("Id"), BinaryOperation.Equal, S.Col <Person>("LivesAtHouseId"))),
                        S.Join <Person, Car>(JoinType.LeftJoin, S.BinExp(S.Col <Person>("Id"), BinaryOperation.Equal, S.Col <Car>("DriverId")))));
            var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream);

            Assert.That(
                result,
                Is.EqualTo(
                    @"SELECT PersonRef.*, CarRef.*, HouseRef.* " +
                    "FROM House HouseRef " +
                    "INNER JOIN Person PersonRef ON HouseRef.Id = PersonRef.LivesAtHouseId " +
                    "LEFT JOIN Car CarRef ON PersonRef.Id = CarRef.DriverId"));
        }
Ejemplo n.º 5
0
        public void ItShouldBePossibleToUseAndAndOrOnAWhereStatementOnADeleteStatement()
        {
            var stream =
                S.DeleteNode(
                    S.From <Person>(),
                    S.Where(
                        S.BinExp(S.Col <Person>("Age"), BinaryOperation.GreaterThan, S.Const(42)),
                        S.And(S.BinExp(S.Col <Person>("Name"), BinaryOperation.Equal, S.Const("'Kalle'"))),
                        S.Or(S.BinExp(S.Col <Person>("Name"), BinaryOperation.Equal, S.Const("'Henrik'")))));
            var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream);

            Assert.That(result,
                        Is.EqualTo(
                            "DELETE FROM Person PersonRef " +
                            "WHERE PersonRef.Age > 42 " +
                            "AND PersonRef.Name = 'Kalle' " +
                            "OR PersonRef.Name = 'Henrik'"));
        }
Ejemplo n.º 6
0
        public void ItCanGenerateSqlForToLower()
        {
            var stream =
                S.SelectNode(
                    S.Select(S.Func(AST.FunctionType.Lower, new[] { S.Col <Person>("Name") })),
                    S.From <Person>(),
                    S.Where(
                        S.BinExp(
                            S.Func(AST.FunctionType.Lower, new[] { S.Col <Person>("Name") }),
                            BinaryOperation.Equal,
                            S.Const("'kalle'"))
                        ));
            var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream);

            string expected = "SELECT LOWER(PersonRef.Name) FROM Person PersonRef WHERE LOWER(PersonRef.Name) = 'kalle'";

            Assert.That(result, Is.EqualTo(expected));
        }
Ejemplo n.º 7
0
        public void ItShouldBePossibleToSqlifyACompareAgainstASubExpressionInAWhereExpression()
        {
            var stream =
                S.SelectNode(
                    S.Select(S.Star <Person>()),
                    S.From <Person>(),
                    S.Where(
                        S.BinExp(
                            S.Col <Person>("Age"),
                            BinaryOperation.GreaterThan,
                            S.SubExp(
                                S.Select(S.Col <Car>("Age")),
                                S.From <Car>(),
                                S.Where(S.BinExp(S.Col <Car>("Id"), BinaryOperation.Equal, S.Const(42)))))));
            var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream);

            Assert.That(result, Is.EqualTo(@"SELECT PersonRef.* FROM Person PersonRef WHERE PersonRef.Age > (SELECT CarRef.Age FROM Car CarRef WHERE CarRef.Id = 42)"));
        }
Ejemplo n.º 8
0
        public void ItShouldBePossibleToJoinATableToItself()
        {
            var child = Table <Person> .WithReferenceName("child");

            var parent = Table <Person> .WithReferenceName("parent");

            var stream =
                S.SelectNode(
                    S.Select(S.Col(child, "Name"), S.Col(parent, "Name")),
                    S.From(
                        child,
                        S.Join(child, parent, JoinType.InnerJoin, S.BinExp(S.Col(child, "ParentId"), BinaryOperation.Equal, S.Col(parent, "Id")))));
            var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream);

            Assert.That(result,
                        Is.EqualTo("SELECT child.Name, parent.Name FROM Person child " +
                                   "INNER JOIN Person parent ON child.ParentId = parent.Id"));
        }
Ejemplo n.º 9
0
        public void ItShouldBePossibleToOrderByMultipleColumns()
        {
            var expr =
                S.SelectNode(
                    S.Select(
                        S.Col <Person>("Name"),
                        S.WinCol(
                            AggregateType.Sum,
                            S.Col <Person>("Age"),
                            orderBy: new[]
            {
                S.OrderClause(S.Col <Person>("Age"), Direction.Ascending, NullsOrdering.NullsLast),
                S.OrderClause(S.Col <Person>("Name"), Direction.Descending),
            })),
                    S.From <Person>());
            var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, expr);

            Assert.That(result, Is.EqualTo("SELECT PersonRef.Name, SUM(PersonRef.Age) OVER (ORDER BY PersonRef.Age ASC NULLS LAST, PersonRef.Name DESC) FROM Person PersonRef"));
        }
Ejemplo n.º 10
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));
        }
Ejemplo n.º 11
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.º 12
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"));
        }
Ejemplo n.º 13
0
        public void ItShouldBePossibleToMixAliasedAndUnAliasedColumns()
        {
            var stream =
                S.SelectNode(
                    S.Select(
                        S.Col <Person>("Name"),
                        S.AliasedCol <Person>(
                            AggregateType.Max,
                            S.Col <Person>("Age"),
                            "MaxAge"),
                        S.AliasedCol <Person>(
                            AggregateType.Min,
                            S.Col <Person>("Age"),
                            "MinAge")),
                    S.From <Person>(),
                    groupBy: S.GroupBy(S.Col <Person>("Name")));
            var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream);

            Assert.That(result, Is.EqualTo(@"SELECT PersonRef.Name, MAX(PersonRef.Age) AS MaxAge, MIN(PersonRef.Age) AS MinAge FROM Person PersonRef GROUP BY PersonRef.Name"));
        }
Ejemplo n.º 14
0
        public void ItShouldBePossibleToUseASingleHavingExpression()
        {
            var stream =
                S.SelectNode(
                    S.Select(S.Col <Person>("Name"), S.Aggregate(AggregateType.Avg, S.Col <Person>("Age"))),
                    S.From <Person>(),
                    groupBy:
                    S.GroupBy(
                        new[] { S.Col <Person>("Name") },
                        S.And(S.BinExp(S.Aggregate(AggregateType.Avg, S.Col <Person>("Age")), BinaryOperation.GreaterThan, S.Const(42)))));
            var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream);

            Assert.That(
                result,
                Is.EqualTo(
                    "SELECT PersonRef.Name, AVG(PersonRef.Age) " +
                    "FROM Person PersonRef " +
                    "GROUP BY PersonRef.Name " +
                    "HAVING AVG(PersonRef.Age) > 42"));
        }
Ejemplo n.º 15
0
        public void ItShouldBePossibleToOrderBySeveralColumns()
        {
            var stream =
                S.SelectNode(
                    S.Select(S.Star <Person>(), S.Star <Car>()),
                    S.From <Person>(
                        S.Join <Person, Car>(JoinType.InnerJoin, S.BinExp(S.Col <Person>("Id"), BinaryOperation.Equal, S.Col <Car>("DriverId")))),
                    orderBy:
                    S.OrderBy(
                        S.OrderClause(S.Col <Person>("Age"), Direction.Ascending),
                        S.OrderClause(S.Col <Car>("Brand"), Direction.Descending)));
            var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream);

            Assert.That(
                result,
                Is.EqualTo(
                    @"SELECT PersonRef.*, CarRef.* FROM Person PersonRef " +
                    "INNER JOIN Car CarRef ON PersonRef.Id = CarRef.DriverId " +
                    "ORDER BY PersonRef.Age ASC, CarRef.Brand DESC"));
        }
Ejemplo n.º 16
0
        public void ItShouldBePossibleToSqlifyAWhereWithAndAndOr()
        {
            var stream =
                S.SelectNode(
                    S.Select(S.Star <Person>()),
                    S.From <Person>(),
                    S.Where(
                        S.BinExp(
                            S.BinExp(S.Col <Person>("Age"), BinaryOperation.GreaterThan, S.Const(42)),
                            BinaryOperation.Or,
                            S.BinExp(
                                S.BinExp(S.Col <Person>("Age"), BinaryOperation.LessThan, S.Const(10)),
                                BinaryOperation.And,
                                S.BinExp(S.Col <Person>("Name"), BinaryOperation.Equal, S.Const("'Karl'"))
                                )
                            )));

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

            Assert.That(result, Is.EqualTo(@"SELECT PersonRef.* FROM Person PersonRef WHERE PersonRef.Age > 42 OR PersonRef.Age < 10 AND PersonRef.Name = 'Karl'"));
        }
Ejemplo n.º 17
0
        public void ItShouldBePossibleToJoinATableToItself()
        {
            var child = Table <Person> .WithReferenceName("child");

            var parent = Table <Person> .WithReferenceName("parent");

            var expr = Select.Column(child, p => new { p.Name })
                       .Column(parent, p => new { p.Name })
                       .From(child)
                       .InnerJoin(child, parent, (p1, p2) => p1.ParentId == p2.Id);
            var result = expr.ToSqlExpression();

            var expected =
                S.SelectNode(
                    S.Select(S.Col(parent, "Name"), S.Col(child, "Name")),
                    S.From(
                        child,
                        S.Join(child, parent, JoinType.InnerJoin, S.BinExp(S.Col(child, "ParentId"), BinaryOperation.Equal, S.Col(parent, "Id")))));

            Assert.That(result, Is.EqualTo(expected));
        }
Ejemplo n.º 18
0
        public void ItShouldAddParensWhenNecessaryToPreserveAndOrPredecence()
        {
            var stream =
                S.SelectNode(
                    S.Select(S.Star <Person>()),
                    S.From <Person>(),
                    S.Where(
                        S.BinExp(
                            S.BinExp(
                                S.BinExp(S.Col <Person>("Age"), BinaryOperation.LessThan, S.Const(10)),
                                BinaryOperation.Or,
                                S.BinExp(S.Col <Person>("Name"), BinaryOperation.Equal, S.Const("'Karl'"))
                                ),
                            BinaryOperation.And,
                            S.BinExp(S.Col <Person>("Age"), BinaryOperation.GreaterThan, S.Const(42))
                            )));

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

            Assert.That(result, Is.EqualTo(@"SELECT PersonRef.* FROM Person PersonRef WHERE (PersonRef.Age < 10 OR PersonRef.Name = 'Karl') AND PersonRef.Age > 42"));
        }
Ejemplo n.º 19
0
        public void ItShouldBePossibleToInnerJoinATableOnSeveralOtherTables()
        {
            var expr = Select.Star <Car>()
                       .From <House>()
                       .InnerJoin <House, Person>((h, p) => h.Id == p.LivesAtHouseId)
                       .InnerJoin <House, Person, Car>((h, p, c) => c.Brand == h.Address && p.LivesAtHouseId == h.Id);
            var result = expr.ToSqlExpression();

            var expected =
                S.SelectNode(
                    S.Select(S.Star <Car>()),
                    S.From <House>(
                        S.Join <House, Person, Car>(
                            JoinType.InnerJoin,
                            S.BinExp(
                                S.BinExp(S.Col <Car>("Brand"), BinaryOperation.Equal, S.Col <House>("Address")),
                                BinaryOperation.And,
                                S.BinExp(S.Col <Person>("LivesAtHouseId"), BinaryOperation.Equal, S.Col <House>("Id")))),
                        S.Join <House, Person>(
                            JoinType.InnerJoin,
                            S.BinExp(S.Col <House>("Id"), BinaryOperation.Equal, S.Col <Person>("LivesAtHouseId")))));

            Assert.That(result, Is.EqualTo(expected));
        }
Ejemplo n.º 20
0
        public void ItShouldHandleASimpleMinus()
        {
            var stream =
                AST.SqlExpression.NewSelect(
                    AST.SelectExpression.NewPlain(
                        AST.PlainSelectExpression.NewSet(
                            S.Minus(
                                S.PlainSelect(
                                    S.Select(S.Star <Person>()),
                                    S.From <Person>()),
                                S.PlainSelect(
                                    S.Select(S.Star <Person>()),
                                    S.From <Person>(),
                                    S.Where(
                                        S.BinExp(S.Col <Person>("Age"), AST.BinaryOperation.GreaterThan, S.Const(42))))))));
            var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream);

            var expected =
                "SELECT PersonRef.* FROM Person PersonRef " +
                "MINUS " +
                "SELECT PersonRef.* FROM Person PersonRef WHERE PersonRef.Age > 42";

            Assert.That(result, Is.EqualTo(expected));
        }
Ejemplo n.º 21
0
        public void ItCanGenerateSqlForAConcatFunctionCall()
        {
            var stream =
                S.SelectNode(
                    S.Select(S.Func(AST.FunctionType.Concat, new[] { S.Col <Person>("Name"), S.Col <Car>("Brand"), S.Col <Person>("Name") })),
                    S.From <Person>(
                        S.Join <Person, Car>(
                            AST.JoinType.InnerJoin,
                            S.BinExp(S.Col <Person>("Id"), BinaryOperation.Equal, S.Col <Car>("DriverId")))),
                    S.Where(
                        S.BinExp(
                            S.Func(AST.FunctionType.Concat, new[] { S.Col <Person>("Name"), S.Col <Car>("Brand"), }),
                            BinaryOperation.Equal,
                            S.Const("'kallesaab'"))));
            var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream);

            string expected =
                "SELECT CONCAT(PersonRef.Name, CarRef.Brand, PersonRef.Name) " +
                "FROM Person PersonRef " +
                "INNER JOIN Car CarRef ON PersonRef.Id = CarRef.DriverId " +
                "WHERE CONCAT(PersonRef.Name, CarRef.Brand) = 'kallesaab'";

            Assert.That(result, Is.EqualTo(expected));
        }
Ejemplo n.º 22
0
        public void ItShouldBePossibleToUseTheParameterInMoreComplexExpressions()
        {
            var stream =
                S.SelectNode(
                    S.Select(S.Star<Person>()),
                    S.From<Person>(),
                    S.Where(
                        S.BinExp(
                            S.BinExp(S.Col<Person>("Age"), BinaryOperation.GreaterThan, S.Param("age")),
                            BinaryOperation.And,
                            S.BinExp(S.Param("age"), BinaryOperation.LessThan, S.Const(90))
                            ),
                        S.Or(S.BinExp(S.Col<Person>("Age"), BinaryOperation.LessThan, S.Param("age")))
                    ));
            var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream);

            Assert.That(result,
                Is.EqualTo(
                    @"SELECT PersonRef.* " +
                    "FROM Person PersonRef " +
                    "WHERE PersonRef.Age > @age "+
                    "AND @age < 90 " +
                    "OR PersonRef.Age < @age"));
        }
Ejemplo n.º 23
0
        public void ItShouldBePossibleToUseMultipleHavingExpressions()
        {
            var stream =
                S.SelectNode(
                    S.Select(
                        S.Col <Person>("Name"),
                        S.Aggregate(AggregateType.Avg, S.Col <Person>("Age")),
                        S.Col <Car>("Brand"),
                        S.Aggregate(AggregateType.Min, S.Col <Car>("Age"))),
                    S.From <Person>(
                        S.Join <Person, Car>(JoinType.InnerJoin, S.BinExp(S.Col <Person>("Id"), BinaryOperation.Equal, S.Col <Car>("DriverId")))),
                    groupBy:
                    S.GroupBy(
                        new[] { S.Col <Person>("Name"), S.Col <Car>("Brand"), },
                        S.And(S.BinExp(S.Aggregate(AggregateType.Avg, S.Col <Person>("Age")), BinaryOperation.GreaterThan, S.Const(42))),
                        S.And(S.BinExp(S.Aggregate(AggregateType.Min, S.Col <Car>("Age")), BinaryOperation.GreaterThan, S.Const(2)))));
            var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream);

            Assert.That(
                result,
                Is.EqualTo(
                    "SELECT PersonRef.Name, AVG(PersonRef.Age), CarRef.Brand, MIN(CarRef.Age) " +
                    "FROM Person PersonRef " +
                    "INNER JOIN Car CarRef ON PersonRef.Id = CarRef.DriverId " +
                    "GROUP BY PersonRef.Name, CarRef.Brand " +
                    "HAVING AVG(PersonRef.Age) > 42 " +
                    "AND MIN(CarRef.Age) > 2"));
        }
Ejemplo n.º 24
0
        public void ItShouldBePossibleToUpdateMultipleInPlace()
        {
            var stream =
                S.UpdateNode(
                    S.UpdHead <Person>(
                        S.Ust <Person>("Age", S.BinExp(S.Col <Person>("Age"), BinaryOperation.Subtract, S.Const(2))),
                        S.Ust <Person>("Name", S.BinExp(S.Const("'Kalle'"), BinaryOperation.Add, S.Col <Person>("Name")))));

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

            Assert.That(result, Is.EqualTo("UPDATE Person SET Age = Age - 2, Name = 'Kalle' + Name"));
        }
Ejemplo n.º 25
0
        public void ItShouldBePossibleToGroupByMultipleColumns()
        {
            var stream =
                S.SelectNode(
                    S.Select(S.Col <Person>("Name"), S.Col <Person>("LivesAtHouseId"), S.Aggregate(AggregateType.Min, S.Col <Person>("Age"))),
                    S.From <Person>(),
                    groupBy: S.GroupBy(S.Col <Person>("Name"), S.Col <Person>("LivesAtHouseId")));
            var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream);

            Assert.That(
                result,
                Is.EqualTo(
                    @"SELECT PersonRef.Name, PersonRef.LivesAtHouseId, MIN(PersonRef.Age) FROM Person PersonRef " +
                    "GROUP BY PersonRef.Name, PersonRef.LivesAtHouseId"));
        }
Ejemplo n.º 26
0
        public void ItShouldBePossibleToInnerJoinATableOnItselfSeveralTimes()
        {
            var child = Table <Person> .WithReferenceName("child");

            var parent = Table <Person> .WithReferenceName("parent");

            var grandparent = Table <Person> .WithReferenceName("grandparent");

            var expr = Select.Column(grandparent, gp => new { gp.Name, gp.Age })
                       .From(child)
                       .InnerJoin(child, parent, (c, p) => c.ParentId == p.Id)
                       .InnerJoin(child, parent, grandparent, (c, p, gp) => p.ParentId == gp.Id && c.LivesAtHouseId == gp.LivesAtHouseId);
            var result = expr.ToSqlExpression();

            var expected =
                S.SelectNode(
                    S.Select(
                        S.Col(grandparent, "Name"),
                        S.Col(grandparent, "Age")),
                    S.From(
                        child,
                        S.Join(
                            child,
                            parent,
                            grandparent,
                            JoinType.InnerJoin,
                            S.BinExp(
                                S.BinExp(S.Col(parent, "ParentId"), BinaryOperation.Equal, S.Col(grandparent, "Id")),
                                BinaryOperation.And,
                                S.BinExp(S.Col(child, "LivesAtHouseId"), BinaryOperation.Equal, S.Col(grandparent, "LivesAtHouseId")))),
                        S.Join(child, parent, JoinType.InnerJoin, S.BinExp(S.Col(child, "ParentId"), BinaryOperation.Equal, S.Col(parent, "Id")))));

            Assert.That(result, Is.EqualTo(expected));
        }
Ejemplo n.º 27
0
 private SqlExpression GetExpectedJoinStream(JoinType joinKeyword)
 {
     return
         (S.SelectNode(
              S.Select(S.Star <Person>(), S.Star <Car>()),
              S.From <Person>(
                  S.Join <Person, Car>(joinKeyword, S.BinExp(S.Col <Person>("Id"), BinaryOperation.Equal, S.Col <Car>("DriverId")))
                  )));
 }