public void ItShouldBePossibleToDeleteWithASubQuery()
{
var stream =
S.DeleteNode(
S.From <Car>(),
S.Where(
S.BinExp(
S.Col <Car>("DriverId"),
BinaryOperation.In,
S.SubExp(
S.Select(S.Col <Person>("Id")),
S.From <Person>(),
S.Where(S.BinExp(S.Col <Person>("Age"), BinaryOperation.LessThan, S.Const(18)))))));
var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream);
Assert.That(
result,
Is.EqualTo(
"DELETE FROM Car CarRef " +
"WHERE CarRef.DriverId IN (" +
"SELECT PersonRef.Id " +
"FROM Person PersonRef " +
"WHERE PersonRef.Age < 18" +
")"));
}
public void ItShouldBePossibleToUpdateValuesToASubExpression()
{
var stream =
S.UpdateNode(
S.UpdHead <Person>(
S.Ust <Person>(
"Age",
S.SubExp(
S.Select(S.Aggregate(AggregateType.Max, S.Col <Car>("Age"))),
S.From <Car>()))));
var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream);
Assert.That(result, Is.EqualTo("UPDATE Person SET Age = (SELECT MAX(CarRef.Age) FROM Car CarRef)"));
}
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)"));
}
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)"));
}
