public void ItShouldBePossibleToSpecifyNullsFirstOrdering()
{
var stream =
S.SelectNode(
S.Select(S.Star <Person>()),
S.From <Person>(),
orderBy: S.OrderBy(S.OrderClause(S.Col <Person>("Age"), Direction.Ascending, NullsOrdering.NullsFirst)));
var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream);
Assert.That(result, Is.EqualTo("SELECT PersonRef.* FROM Person PersonRef ORDER BY PersonRef.Age ASC NULLS FIRST"));
}
public void ItShouldBePossibleToOrderDescendingByAColumn()
{
var stream =
S.SelectNode(
S.Select(S.Star <Person>()),
S.From <Person>(),
orderBy: S.OrderBy(S.OrderClause(S.Col <Person>("Age"), Direction.Descending)));
var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream);
Assert.That(result, Is.EqualTo(@"SELECT PersonRef.* FROM Person PersonRef ORDER BY PersonRef.Age DESC"));
}
public void ItShouldBePossibleToOrderByAColumn()
{
var expr =
S.SelectNode(
S.Select(
S.Col <Person>("Name"),
S.WinCol(
AggregateType.Sum,
S.Col <Person>("Age"),
orderBy: new[] { S.OrderClause(S.Col <Person>("Age"), Direction.Ascending) })),
S.From <Person>());
var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, expr);
Assert.That(result, Is.EqualTo("SELECT PersonRef.Name, SUM(PersonRef.Age) OVER (ORDER BY PersonRef.Age ASC) FROM Person PersonRef"));
}
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 ItShouldBePossibleToOrderBySeveralColumns()
{
var stream =
S.SelectNode(
S.Select(S.Star <Person>(), S.Star <Car>()),
S.From <Person>(
S.Join <Person, Car>(JoinType.InnerJoin, S.BinExp(S.Col <Person>("Id"), BinaryOperation.Equal, S.Col <Car>("DriverId")))),
orderBy:
S.OrderBy(
S.OrderClause(S.Col <Person>("Age"), Direction.Ascending),
S.OrderClause(S.Col <Car>("Brand"), Direction.Descending)));
var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream);
Assert.That(
result,
Is.EqualTo(
@"SELECT PersonRef.*, CarRef.* FROM Person PersonRef " +
"INNER JOIN Car CarRef ON PersonRef.Id = CarRef.DriverId " +
"ORDER BY PersonRef.Age ASC, CarRef.Brand DESC"));
}