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