public void ItCanSupportFlippingOrdersForInvertedComparisonWithNull()
{
var stream =
S.SelectNode(
S.Select(S.Star <Person>()),
S.From <Person>(),
S.Where(
S.BinExp(S.Null(), BinaryOperation.NotEqual, S.Col <Person>("Age"))));
var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream);
Assert.That(result, Is.EqualTo("SELECT PersonRef.* FROM Person PersonRef WHERE PersonRef.Age IS NOT NULL"));
}
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));
}
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"));
}