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")); }
public void ItShouldBePossibleToHaveMultipleConditionsOnTheUpdate() { var stream = S.UpdateNode( S.UpdHead <Person>(S.Ust <Person>("Age", 42)), S.Where( S.BinExp(S.Col <Person>("Name"), BinaryOperation.Equal, S.Const("'Kalle'")), S.Or(S.BinExp(S.Col <Person>("Name"), BinaryOperation.Equal, S.Const("'Pelle'"))), S.And(S.BinExp(S.Col <Person>("Age"), BinaryOperation.LessThan, S.Const(18))))); var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream); Assert.That(result, Is.EqualTo("UPDATE Person SET Age = 42 WHERE Name = 'Kalle' OR Name = 'Pelle' AND Age < 18")); }
public void ItShouldBePossibleToSqlifyAWhereWithAndOrsThatIsNotInBinaryExpressions() { var stream = S.SelectNode( S.Select(S.Star <Person>()), S.From <Person>(), S.Where( S.BinExp(S.Col <Person>("Age"), BinaryOperation.LessThan, S.Const(42)), S.And(S.BinExp(S.Col <Person>("Age"), BinaryOperation.GreaterThan, S.Const(12))), S.Or(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 AND PersonRef.Age > 12 OR PersonRef.Name = 'Karl'")); }
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'")); }
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")); }