public void WriteAggregate(string columnName, string tableName, SqlAggregate aggregateType)
{
Write(ConvertSqlAggregateToString(aggregateType));
WriteBeginGroup();
WriteColumnName(columnName, tableName);
WriteEndGroup();
}
public void Visit(SqlAggregate node)
{
if (!node.Expression.IsNullReference())
{
Visit(node.Expression);
}
}
public virtual void WriteRankOver(string columnName, string tableName, SqlAggregate aggregateType, string alias, bool rankDescending)
{
this.WriteRankOver(columnName, tableName, aggregateType, rankDescending);
WriteSpace();
Write(SqlConstants.AS);
WriteSpace();
WriteColumnName(alias);
}
public void ToSql_WithMySqlDialect_WithSqlSelectWithGroupBy_ReturnsSql()
{
const string expected = "SELECT `p`.`City`, AVG(`p`.`Age`) AS `AverageAge` FROM `Profile` `p` GROUP BY (`p`.`City`)";
var p = new SqlTable("Profile", "p");
var actual = Sql
.Select(p + "City", SqlAggregate.Average(p + "Age", "AverageAge"))
.From(p)
.GroupBy(p + "City")
.Go()
.ToSql(new MySqlDialect());
Assert.Equal(expected, actual);
}
public void ToSql_WithMySqlDialect_WithSqlSelectWithGroupByAndSumAggregate_ReturnsSql()
{
const string expected = "SELECT `oi`.`ProductCode`, SUM(`oi`.`Total`) AS `TotalPerProduct` FROM `OrderItem` `oi` GROUP BY (`oi`.`ProductCode`)";
var oi = new SqlTable("OrderItem", "oi");
var actual = Sql
.Select(oi + "ProductCode", SqlAggregate.Sum(oi + "Total", "TotalPerProduct"))
.From(oi)
.GroupBy(oi + "ProductCode")
.Go()
.ToSql(new MySqlDialect());
Assert.Equal(expected, actual);
}
public void ToSql_WithSqlSelectWithGroupBy_ReturnsSql()
{
const string expected = "SELECT [p].[City], AVG([p].[Age]) AS [AverageAge] FROM [Profile] [p] GROUP BY ([p].[City])";
var p = new SqlTable("Profile", "p");
var actual = Sql
.Select(p + "City", SqlAggregate.Average(p + "Age", "AverageAge"))
.From(p)
.GroupBy(p + "City")
.Go()
.ToSql();
Assert.Equal(expected, actual);
}
public void ToSql_WithSqlSelectWithGroupByAndSumAggregate_ReturnsSql()
{
const string expected = "SELECT [oi].[ProductCode], SUM([oi].[Total]) AS [TotalPerProduct] FROM [OrderItem] [oi] GROUP BY ([oi].[ProductCode])";
var oi = new SqlTable("OrderItem", "oi");
var actual = Sql
.Select(oi + "ProductCode", SqlAggregate.Sum(oi + "Total", "TotalPerProduct"))
.From(oi)
.GroupBy(oi + "ProductCode")
.Go()
.ToSql();
Assert.Equal(expected, actual);
}
public void ToSql_WithMySqlDialect_WithSqlSelectWithGroupByAndHaving_ReturnsSql()
{
const string expected = "SELECT `p`.`City`, MIN(`p`.`Age`) AS `MinAge`, MAX(`p`.`Age`) AS `MaxAge` FROM `Profile` `p` GROUP BY (`p`.`City`) HAVING `p`.`City` IN ('New York', 'London', 'Paris')";
var p = new SqlTable("Profile", "p");
var actual = Sql
.Select(p + "City", SqlAggregate.Min(p + "Age", "MinAge"), SqlAggregate.Max(p + "Age", "MaxAge"))
.From(p)
.GroupBy(p + "City")
.Having(SqlExpression.In(p + "City", "New York", "London", "Paris"))
.Go()
.ToSql(new MySqlDialect());
Assert.Equal(expected, actual);
}
public void Select_WithGroupByWithRealLifeQuery_ReturnsQuery()
{
SqlTable profiles = new SqlTable("Profile", "p");
var actual = Sql
.Select(profiles + "Age", SqlAggregate.Count(profiles + "Id", "Count"))
.From(profiles)
.GroupBy(profiles + "Age")
.Having(SqlExpression.GreaterThanOrEqual(profiles + "Age", 18))
.ToSql();
const string expected = "SELECT [p].[Age], COUNT([p].[Id]) AS [Count] FROM [Profile] [p] GROUP BY ([p].[Age]) HAVING [p].[Age] >= 18";
Assert.Equal(expected, actual);
}
public void ToSql_WithSqlSelectWithGroupByAndHaving_ReturnsSql()
{
const string expected = "SELECT [p].[City], MIN([p].[Age]) AS [MinAge], MAX([p].[Age]) AS [MaxAge] FROM [Profile] [p] GROUP BY ([p].[City]) HAVING [p].[City] IN ('New York', 'London', 'Paris')";
var p = new SqlTable("Profile", "p");
var actual = Sql
.Select(p + "City", SqlAggregate.Min(p + "Age", "MinAge"), SqlAggregate.Max(p + "Age", "MaxAge"))
.From(p)
.GroupBy(p + "City")
.Having(SqlExpression.In(p + "City", "New York", "London", "Paris"))
.Go()
.ToSql();
Assert.Equal(expected, actual);
}
public void WriteAggregateColumn(string columnName, string tableName, SqlAggregate aggregateType, string outputName)
{
Write(ConvertSqlAggregateToString(aggregateType));
WriteBeginGroup();
if (aggregateType == SqlAggregate.CountDistinct)
{
Write(SqlConstants.DISTINCT);
WriteSpace();
}
WriteColumnName(columnName, tableName);
WriteEndGroup();
WriteSpace();
Write(SqlConstants.AS);
WriteSpace();
WriteColumnName(outputName);
}
public virtual void WriteRankOver(string columnName, string tableName, SqlAggregate aggregateType, bool rankDescending)
{
Write(SqlConstants.RANK_OVER);
WriteBeginGroup();
Write(SqlConstants.ORDER_BY);
WriteSpace();
Write(ConvertSqlAggregateToString(aggregateType));
WriteBeginGroup();
WriteColumnName(columnName, tableName);
WriteEndGroup();
if (rankDescending)
{
WriteSpace();
Write(SqlConstants.DESC);
}
WriteEndGroup();
}
protected override SqlExpression VisitAggregate(SqlAggregate expr)
{
_builder.Append(GetAggregateOperator(expr.Type));
_builder.Append("(");
if (expr.Expression == null)
{
_builder.Append("*");
}
else
{
Visit(expr.Expression);
}
_builder.Append(")");
return(expr);
}
public static string ConvertSqlAggregateToString(SqlAggregate aggregateType)
{
switch (aggregateType)
{
case SqlAggregate.Minimum: return("MIN");
case SqlAggregate.Maximum: return("MAX");
case SqlAggregate.Sum: return("SUM");
case SqlAggregate.Count: return("COUNT");
case SqlAggregate.CountDistinct: return("COUNT");
case SqlAggregate.Average: return("AVG");
case SqlAggregate.StDev: return("STDEV");
default: throw new InvalidOperationException("Invalid aggregate");
}
}
public void SqlAggregateReplacingTest()
{
SqlAggregate a = SqlDml.Count();
SqlAggregate aReplacing = SqlDml.Avg(1, true);
a.ReplaceWith(aReplacing);
bool passed = false;
try {
a.ReplaceWith(1);
}
catch {
passed = true;
}
Assert.IsTrue(passed);
Assert.AreNotEqual(a, aReplacing);
Assert.AreEqual(a.NodeType, aReplacing.NodeType);
Assert.AreEqual(a.Distinct, aReplacing.Distinct);
Assert.AreEqual(a.Expression, aReplacing.Expression);
}
public void SqlAggregateCloneTest()
{
{
SqlAggregate a = SqlDml.Count();
SqlAggregate aClone = (SqlAggregate)a.Clone();
Assert.AreNotEqual(a, aClone);
Assert.AreEqual(a.NodeType, aClone.NodeType);
Assert.AreEqual(a.Distinct, aClone.Distinct);
Assert.AreEqual(aClone.Expression.NodeType, SqlNodeType.Native);
}
Console.WriteLine();
{
SqlAggregate a = SqlDml.Sum(1);
SqlAggregate aClone = (SqlAggregate)a.Clone();
Assert.AreNotEqual(a, aClone);
Assert.AreNotEqual(a.Expression, aClone.Expression);
Assert.AreEqual(a.NodeType, aClone.NodeType);
Assert.AreEqual(a.Distinct, aClone.Distinct);
Assert.AreEqual(a.Expression.NodeType, aClone.Expression.NodeType);
}
}
public virtual void Visit(SqlAggregate node)
{
VisitInternal(node.Expression);
}
/// <summary>
/// Creates a new instance for the specified table, column, and aggregate type.
/// </summary>
/// <param name="table"></param>
/// <param name="columnName"></param>
/// <param name="aggregateType"></param>
/// <param name="alias"></param>
/// <param name="rankDescending"></param>
public SqlRankColumn(ISqlTable table, string columnName, SqlAggregate aggregateType, string alias, bool rankDescending) : base(table, alias)
{
this.ColumnName = columnName;
this.Aggregate = aggregateType;
this.RankDescending = rankDescending;
}
protected virtual SqlExpression VisitAggregate(SqlAggregate expr)
{
return(expr);
}
protected virtual SqlExpression VisitAggregate(SqlAggregate expr)
{
return expr;
}
protected override SqlExpression VisitAggregate(SqlAggregate expr)
{
_builder.Append(GetAggregateOperator(expr.Type));
_builder.Append("(");
if (expr.Expression == null)
_builder.Append("*");
else
Visit(expr.Expression);
_builder.Append(")");
return expr;
}
public virtual string ConvertAggregateToString(SqlAggregate aggregate)
{
return(ConvertSqlAggregateToString(aggregate));
}