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