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