/// <summary>
/// Return the approximate distinct count using the APPROX_COUNT_DISTINCT function.
/// </summary>
/// <param name="columnName">Name of the column.</param>
public ILink <long> AsCountApproximate(string columnName)
{
var column = m_Table.Columns[columnName];
AggregateColumns.Add(new CustomAggregateColumn($"APPROX_COUNT_DISTINCT({column.QuotedSqlName})", "RowCount"));
return(ToInt64());
}
/// <summary>
/// Return the approximate row count using the APPROX_COUNT_DISTINCT function.
/// </summary>
/// <remarks>This is only available on tables with a single primary key.</remarks>
public ILink <long> AsCountApproximate()
{
var primaryKeys = m_Table.PrimaryKeyColumns;
if (primaryKeys.Count != 1)
{
throw new MappingException($"{nameof(AsCountApproximate)}() operation isn't allowed on {m_Table.Name} because it doesn't have a single primary key. Please provide a column name.");
}
AggregateColumns.Add(new CustomAggregateColumn($"APPROX_COUNT_DISTINCT({primaryKeys.Single().QuotedSqlName})", "RowCount"));
return(ToInt64());
}
private void AddAggregateColumn(PropertyInfo propInfo)
{
var attr = propInfo.GetCustomAttribute(typeof(AggregateColumn)) as AggregateColumn;
if (attr != null)
{
AggregateColumns.Add(new AggregateAttributeMapping()
{
PropInOutput = propInfo,
PropNameInInput = attr.AggregationProperty,
AggregationMethod = attr.AggregationMethod
});
}
}
protected override void OnDefineColumns()
{
GroupColumns.Add(new Column()
{
Name = "Order Status", FieldAccessor = r => r.OrderStatus
});
GroupColumns.Add(new Column()
{
Name = "Income Receivable", FieldAccessor = r => r.HasIncomeReceivable
});
GroupColumns.Add(new Column()
{
Name = "Open Shipment Request", FieldAccessor = r => r.HasOpenOrderShipmentRequest
});
AggregateColumns.Add(new Column()
{
Name = "Order #", FieldAccessor = r => r.OrderNumber
});
}
/// <inheritdoc/>
protected override string ParametersToString()
{
if (AggregateColumns.Length == 0)
{
return(string.Format(
ToStringFormatGroupOnly,
GroupColumnIndexes.ToCommaDelimitedString()));
}
if (GroupColumnIndexes.Length == 0)
{
return(string.Format(
ToStringFormatAggregateOnly,
AggregateColumns.ToCommaDelimitedString()));
}
return(string.Format(
ToStringFormatFull,
AggregateColumns.ToCommaDelimitedString(),
GroupColumnIndexes.ToCommaDelimitedString()));
}
protected override void OnDefineColumns()
{
GroupColumns.Add(new Column()
{
Name = "Table", FieldAccessor = r => r.TableName
});
GroupColumns.Add(new Column()
{
Name = "Field", FieldAccessor = r => r.FieldName
});
DataColumns.Add(new Column()
{
Name = "Value", FieldAccessor = r => r.Name
});
AggregateColumns.Add(new Column()
{
Name = "# Records", FieldAccessor = r => r.RecordCount
});
}
protected override void OnDefineColumns()
{
GroupColumns.Add(new Column()
{
Name = "Order Year", FieldAccessor = r => r.OrderYear
});
GroupColumns.Add(new Column()
{
Name = "Order Month", FieldAccessor = r => r.OrderMonth
});
DataColumns.Add(new Column()
{
Name = "Order #", FieldAccessor = r => r.OrderNumber
});
DataColumns.Add(new Column()
{
Name = "Order Date", FieldAccessor = r => r.OrderDateTimeUtc, Format = "{0:d}"
});
DataColumns.Add(new Column()
{
Name = "Status Date", FieldAccessor = r => r.StatusDateTimeUtc, Format = "{0:d}"
});
AggregateColumns.Add(new Column()
{
Name = "Cash (1000)", FieldAccessor = r => r.Cash_1000, Format = "{0:c}"
});
AggregateColumns.Add(new Column()
{
Name = "Income Receivable (1100)", FieldAccessor = r => r.IncomeReceivableBalance_1100, Format = "{0:c}"
});
AggregateColumns.Add(new Column()
{
Name = "Square Fees (5100)", FieldAccessor = r => r.SquareFees_5100, Format = "{0:c}"
});
}
private void SetAggregationFunctionsIfNecessary()
{
if (AggregationAction == null && (AggTypeInfo.AggregateColumns.Count > 0 || AggregateColumns?.Count() > 0))
{
FillAggregateAttributeMapping();
AggregationAction = DefineAggregationAction;
}
if (GroupingFunc == null && (AggTypeInfo.GroupColumns.Count > 0 || GroupColumns?.Count() > 0))
{
FillGroupingAttributeMapping();
GroupingFunc = DefineGroupingProperty;
if (StoreKeyAction == null)
{
StoreKeyAction = DefineStoreKeyAction;
}
}
}
public override CommandExecutionToken <OleDbCommand, OleDbParameter> Prepare(Materializer <OleDbCommand, OleDbParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var sqlBuilder = m_Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyRulesForSelect(DataSource);
if (AggregateColumns.IsEmpty)
{
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
}
//Support check
if (!Enum.IsDefined(typeof(SqlServerLimitOption), m_LimitOptions))
{
throw new NotSupportedException($"SQL Server does not support limit option {(LimitOptions)m_LimitOptions}");
}
//Validation
if (m_Skip < 0)
{
throw new InvalidOperationException($"Cannot skip {m_Skip} rows");
}
if (m_Skip > 0 && !m_SortExpressions.Any())
{
throw new InvalidOperationException($"Cannot perform a Skip operation with out a sort expression.");
}
if (m_Skip > 0 && m_LimitOptions != SqlServerLimitOption.Rows)
{
throw new InvalidOperationException($"Cannot perform a Skip operation with limit option {m_LimitOptions}");
}
if (m_Take <= 0)
{
throw new InvalidOperationException($"Cannot take {m_Take} rows");
}
if ((m_LimitOptions == SqlServerLimitOption.TableSampleSystemRows || m_LimitOptions == SqlServerLimitOption.TableSampleSystemPercentage) && m_SortExpressions.Any())
{
throw new InvalidOperationException($"Cannot perform random sampling when sorting.");
}
if ((m_LimitOptions == SqlServerLimitOption.RowsWithTies || m_LimitOptions == SqlServerLimitOption.PercentageWithTies) && !m_SortExpressions.Any())
{
throw new InvalidOperationException($"Cannot perform a WITH TIES operation without sorting.");
}
//SQL Generation
var parameters = new List <OleDbParameter>();
var sql = new StringBuilder();
string?topClause = null;
switch (m_LimitOptions)
{
case SqlServerLimitOption.Rows:
if (!m_SortExpressions.Any())
{
topClause = $"TOP ({m_Take}) ";
}
break;
case SqlServerLimitOption.Percentage:
topClause = $"TOP ({m_Take}) PERCENT ";
break;
case SqlServerLimitOption.PercentageWithTies:
topClause = $"TOP ({m_Take}) PERCENT WITH TIES ";
break;
case SqlServerLimitOption.RowsWithTies:
topClause = $"TOP ({m_Take}) WITH TIES ";
break;
}
if (AggregateColumns.IsEmpty)
{
sqlBuilder.BuildSelectClause(sql, "SELECT " + topClause, null, null);
}
else
{
AggregateColumns.BuildSelectClause(sql, "SELECT ", DataSource, null);
}
sql.Append(" FROM " + m_Table.Name.ToQuotedString());
switch (m_LimitOptions)
{
case SqlServerLimitOption.TableSampleSystemRows:
sql.Append($" TABLESAMPLE SYSTEM ({m_Take} ROWS) ");
if (m_Seed.HasValue)
{
sql.Append($"REPEATABLE ({m_Seed}) ");
}
break;
case SqlServerLimitOption.TableSampleSystemPercentage:
sql.Append($" TABLESAMPLE SYSTEM ({m_Take} PERCENT) ");
if (m_Seed.HasValue)
{
sql.Append($"REPEATABLE ({m_Seed}) ");
}
break;
}
if (m_FilterValue != null)
{
sql.Append(" WHERE (" + sqlBuilder.ApplyAnonymousFilterValue(m_FilterValue, m_FilterOptions) + ")");
sqlBuilder.BuildAnonymousSoftDeleteClause(sql, " AND (", DataSource, ") ");
parameters.AddRange(sqlBuilder.GetParameters());
}
else if (!string.IsNullOrWhiteSpace(m_WhereClause))
{
sql.Append(" WHERE (" + m_WhereClause + ")");
sqlBuilder.BuildAnonymousSoftDeleteClause(sql, " AND (", DataSource, ") ");
parameters = SqlBuilder.GetParameters <OleDbParameter>(m_ArgumentValue);
parameters.AddRange(sqlBuilder.GetParameters());
}
else
{
sqlBuilder.BuildAnonymousSoftDeleteClause(sql, " WHERE ", DataSource, null);
parameters.AddRange(sqlBuilder.GetParameters());
}
if (AggregateColumns.HasGroupBy)
{
AggregateColumns.BuildGroupByClause(sql, " GROUP BY ", DataSource, null);
}
if (m_LimitOptions.RequiresSorting() && !m_SortExpressions.Any())
{
if (m_Table.HasPrimaryKey)
{
sqlBuilder.BuildOrderByClause(sql, " ORDER BY ", m_Table.PrimaryKeyColumns.Select(x => new SortExpression(x.SqlName)), null);
}
else if (StrictMode)
{
throw new InvalidOperationException("Limits were requested, but no primary keys were detected. Use WithSorting to supply a sort order or disable strict mode.");
}
}
else
{
sqlBuilder.BuildOrderByClause(sql, " ORDER BY ", m_SortExpressions, null);
}
switch (m_LimitOptions)
{
case SqlServerLimitOption.Rows:
if (m_SortExpressions.Any())
{
sql.Append(" OFFSET ? ROWS ");
parameters.Add(new OleDbParameter("@offset_row_count_expression", m_Skip ?? 0));
if (m_Take.HasValue)
{
sql.Append(" FETCH NEXT ? ROWS ONLY");
parameters.Add(new OleDbParameter("@fetch_row_count_expression", m_Take));
}
}
//else
//{
// parameters.Add(new OleDbParameter("@fetch_row_count_expression", m_Take));
//}
break;
case SqlServerLimitOption.Percentage:
case SqlServerLimitOption.PercentageWithTies:
case SqlServerLimitOption.RowsWithTies:
break;
}
sql.Append(";");
return(new OleDbCommandExecutionToken(DataSource, "Query " + m_Table.Name, sql.ToString(), parameters));
}
protected override void CombineInputAndOutputMapping()
{
this.AssignInputProperty(GroupColumns);
this.AssignInputProperty(AggregateColumns.Cast <AttributeMappingInfo>().ToList());
}
/// <summary>
/// Prepares the command for execution by generating any necessary SQL.
/// </summary>
/// <param name="materializer">The materializer.</param>
/// <returns>
/// ExecutionToken<TCommand>.
/// </returns>
public override CommandExecutionToken <NpgsqlCommand, NpgsqlParameter> Prepare(Materializer <NpgsqlCommand, NpgsqlParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var sqlBuilder = m_Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyRulesForSelect(DataSource);
if (AggregateColumns.IsEmpty)
{
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
}
//Support check
if (!Enum.IsDefined(typeof(PostgreSqlLimitOption), m_LimitOptions))
{
throw new NotSupportedException($"PostgreSQL does not support limit option {(LimitOptions)m_LimitOptions}");
}
//Validation
if (m_Skip < 0)
{
throw new InvalidOperationException($"Cannot skip {m_Skip} rows");
}
if (m_Skip > 0 && m_LimitOptions != PostgreSqlLimitOption.Rows)
{
throw new InvalidOperationException($"Cannot perform a Skip operation with limit option {m_LimitOptions}");
}
if (m_Take <= 0)
{
throw new InvalidOperationException($"Cannot take {m_Take} rows");
}
if ((m_LimitOptions == PostgreSqlLimitOption.TableSampleBernoulliPercentage || m_LimitOptions == PostgreSqlLimitOption.TableSampleSystemPercentage) && m_SortExpressions.Any())
{
throw new InvalidOperationException($"Cannot perform random sampling when sorting.");
}
//SQL Generation
List <NpgsqlParameter> parameters;
var sql = new StringBuilder();
if (AggregateColumns.IsEmpty)
{
sqlBuilder.BuildSelectClause(sql, "SELECT ", null, null);
}
else
{
AggregateColumns.BuildSelectClause(sql, "SELECT ", DataSource, null);
}
sql.Append(" FROM " + m_Table.Name);
switch (m_LimitOptions)
{
case PostgreSqlLimitOption.TableSampleSystemPercentage:
sql.Append($" TABLESAMPLE SYSTEM ({m_Take}) ");
if (m_Seed.HasValue)
{
sql.Append($"REPEATABLE ({m_Seed}) ");
}
break;
case PostgreSqlLimitOption.TableSampleBernoulliPercentage:
sql.Append($" TABLESAMPLE BERNOULLI ({m_Take}) ");
if (m_Seed.HasValue)
{
sql.Append($"REPEATABLE ({m_Seed}) ");
}
break;
}
if (m_FilterValue != null)
{
sql.Append(" WHERE (" + sqlBuilder.ApplyFilterValue(m_FilterValue, m_FilterOptions) + ")");
sqlBuilder.BuildSoftDeleteClause(sql, " AND (", DataSource, ") ");
parameters = sqlBuilder.GetParameters();
}
else if (!string.IsNullOrWhiteSpace(m_WhereClause))
{
sql.Append(" WHERE (" + m_WhereClause + ")");
sqlBuilder.BuildSoftDeleteClause(sql, " AND (", DataSource, ") ");
parameters = SqlBuilder.GetParameters <NpgsqlParameter>(m_ArgumentValue);
parameters.AddRange(sqlBuilder.GetParameters());
}
else
{
sqlBuilder.BuildSoftDeleteClause(sql, " WHERE ", DataSource, null);
parameters = sqlBuilder.GetParameters();
}
if (AggregateColumns.HasGroupBy)
{
AggregateColumns.BuildGroupByClause(sql, " GROUP BY ", DataSource, null);
}
if (m_LimitOptions.RequiresSorting() && !m_SortExpressions.Any())
{
if (m_Table.HasPrimaryKey)
{
sqlBuilder.BuildOrderByClause(sql, " ORDER BY ", m_Table.PrimaryKeyColumns.Select(x => new SortExpression(x.SqlName)), null);
}
else if (StrictMode)
{
throw new InvalidOperationException("Limits were requested, but no primary keys were detected. Use WithSorting to supply a sort order or disable strict mode.");
}
}
else
{
sqlBuilder.BuildOrderByClause(sql, " ORDER BY ", m_SortExpressions, null);
}
switch (m_LimitOptions)
{
case PostgreSqlLimitOption.Rows:
sql.Append(" OFFSET @offset_row_count_expression");
parameters.Add(new NpgsqlParameter("@offset_row_count_expression", m_Skip ?? 0));
if (m_Take.HasValue)
{
sql.Append(" LIMIT @limit_row_count_expression");
parameters.Add(new NpgsqlParameter("@limit_row_count_expression", m_Take));
}
break;
}
sql.Append(";");
return(new PostgreSqlCommandExecutionToken(DataSource, "Query " + m_Table.Name, sql.ToString(), parameters));
}
public override CommandExecutionToken <SqlCommand, SqlParameter> Prepare(Materializer <SqlCommand, SqlParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var sqlBuilder = m_Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyRulesForSelect(DataSource);
if (m_FunctionArgumentValue != null)
{
sqlBuilder.ApplyArgumentValue(DataSource, m_FunctionArgumentValue);
}
if (AggregateColumns.IsEmpty)
{
var desired = materializer.DesiredColumns();
if (desired == Materializer.AutoSelectDesiredColumns)
{
desired = Materializer.AllColumns;
}
sqlBuilder.ApplyDesiredColumns(desired);
}
//Support check
if (!Enum.IsDefined(typeof(SqlServerLimitOption), m_LimitOptions))
{
throw new NotSupportedException($"SQL Server does not support limit option {(LimitOptions)m_LimitOptions}");
}
if (m_LimitOptions == SqlServerLimitOption.TableSampleSystemRows || m_LimitOptions == SqlServerLimitOption.TableSampleSystemPercentage)
{
throw new NotSupportedException($"SQL Server does not support limit option {(LimitOptions)m_LimitOptions} with table-valued functions");
}
if (m_Seed.HasValue)
{
throw new NotSupportedException($"SQL Server does not setting a random seed for table-valued functions");
}
//Validation
if (m_Skip < 0)
{
throw new InvalidOperationException($"Cannot skip {m_Skip} rows");
}
if (m_Skip > 0 && !m_SortExpressions.Any())
{
throw new InvalidOperationException($"Cannot perform a Skip operation with out a sort expression.");
}
if (m_Skip > 0 && m_LimitOptions != SqlServerLimitOption.Rows)
{
throw new InvalidOperationException($"Cannot perform a Skip operation with limit option {m_LimitOptions}");
}
if (m_Take <= 0)
{
throw new InvalidOperationException($"Cannot take {m_Take} rows");
}
if ((m_LimitOptions == SqlServerLimitOption.RowsWithTies || m_LimitOptions == SqlServerLimitOption.PercentageWithTies) && !m_SortExpressions.Any())
{
throw new InvalidOperationException($"Cannot perform a WITH TIES operation without sorting.");
}
//SQL Generation
List <SqlParameter> parameters;
var sql = new StringBuilder();
string?topClause = null;
switch (m_LimitOptions)
{
case SqlServerLimitOption.Rows:
if (!m_SortExpressions.Any())
{
topClause = $"TOP (@fetch_row_count_expression) ";
}
break;
case SqlServerLimitOption.Percentage:
topClause = $"TOP (@fetch_row_count_expression) PERCENT ";
break;
case SqlServerLimitOption.PercentageWithTies:
topClause = $"TOP (@fetch_row_count_expression) PERCENT WITH TIES ";
break;
case SqlServerLimitOption.RowsWithTies:
topClause = $"TOP (@fetch_row_count_expression) WITH TIES ";
break;
}
if (AggregateColumns.IsEmpty)
{
sqlBuilder.BuildSelectClause(sql, "SELECT " + topClause, null, null);
}
else
{
AggregateColumns.BuildSelectClause(sql, "SELECT ", DataSource, null);
}
sqlBuilder.BuildFromFunctionClause(sql, $" FROM {m_Table.Name.ToQuotedString()} (", " ) ");
if (m_FilterValue != null)
{
sql.Append(" WHERE (" + sqlBuilder.ApplyFilterValue(m_FilterValue, m_FilterOptions) + ")");
sqlBuilder.BuildSoftDeleteClause(sql, " AND (", DataSource, ") ");
parameters = sqlBuilder.GetParameters();
}
else if (!string.IsNullOrWhiteSpace(m_WhereClause))
{
sql.Append(" WHERE (" + m_WhereClause + ")");
sqlBuilder.BuildSoftDeleteClause(sql, " AND (", DataSource, ") ");
parameters = SqlBuilder.GetParameters <SqlParameter>(m_ArgumentValue);
parameters.AddRange(sqlBuilder.GetParameters());
}
else
{
sqlBuilder.BuildSoftDeleteClause(sql, " WHERE ", DataSource, null);
parameters = sqlBuilder.GetParameters();
}
if (AggregateColumns.HasGroupBy)
{
AggregateColumns.BuildGroupByClause(sql, " GROUP BY ", DataSource, null);
}
if (m_LimitOptions.RequiresSorting() && !m_SortExpressions.Any() && StrictMode)
{
throw new InvalidOperationException("Limits were requested without a sort order. Use WithSorting to supply a sort order or disable strict mode.");
}
sqlBuilder.BuildOrderByClause(sql, " ORDER BY ", m_SortExpressions, null);
switch (m_LimitOptions)
{
case SqlServerLimitOption.Rows:
if (m_SortExpressions.Any())
{
sql.Append(" OFFSET @offset_row_count_expression ROWS ");
parameters.Add(new SqlParameter("@offset_row_count_expression", m_Skip ?? 0));
if (m_Take.HasValue)
{
sql.Append(" FETCH NEXT @fetch_row_count_expression ROWS ONLY");
parameters.Add(new SqlParameter("@fetch_row_count_expression", m_Take));
}
}
else
{
parameters.Add(new SqlParameter("@fetch_row_count_expression", m_Take));
}
break;
case SqlServerLimitOption.Percentage:
case SqlServerLimitOption.PercentageWithTies:
case SqlServerLimitOption.RowsWithTies:
parameters.Add(new SqlParameter("@fetch_row_count_expression", m_Take));
break;
}
sql.Append(";");
return(new SqlServerCommandExecutionToken(DataSource, "Query Function " + m_Table.Name, sql.ToString(), parameters));
}
public override CommandExecutionToken <NpgsqlCommand, NpgsqlParameter> Prepare(Materializer <NpgsqlCommand, NpgsqlParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var sqlBuilder = m_Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyRulesForSelect(DataSource);
if (m_FunctionArgumentValue != null)
{
sqlBuilder.ApplyArgumentValue(DataSource, m_FunctionArgumentValue);
}
if (AggregateColumns.IsEmpty)
{
var desired = materializer.DesiredColumns();
if (desired == Materializer.AutoSelectDesiredColumns)
{
desired = Materializer.AllColumns;
}
sqlBuilder.ApplyDesiredColumns(desired);
}
//Support check
if (!Enum.IsDefined(typeof(PostgreSqlLimitOption), m_LimitOptions))
{
throw new NotSupportedException($"PostgreSQL does not support limit option {(LimitOptions)m_LimitOptions}");
}
//Validation
if (m_Skip < 0)
{
throw new InvalidOperationException($"Cannot skip {m_Skip} rows");
}
if (m_Skip > 0 && m_LimitOptions != PostgreSqlLimitOption.Rows)
{
throw new InvalidOperationException($"Cannot perform a Skip operation with limit option {m_LimitOptions}");
}
if (m_Take <= 0)
{
throw new InvalidOperationException($"Cannot take {m_Take} rows");
}
//SQL Generation
List <NpgsqlParameter> parameters;
var sql = new StringBuilder();
if (AggregateColumns.IsEmpty)
{
sqlBuilder.BuildSelectClause(sql, "SELECT ", null, null);
}
else
{
AggregateColumns.BuildSelectClause(sql, "SELECT ", DataSource, null);
}
sqlBuilder.BuildFromFunctionClause(sql, $" FROM {m_Table.Name.ToQuotedString()} (", " ) ");
if (m_FilterValue != null)
{
sql.Append(" WHERE (" + sqlBuilder.ApplyFilterValue(m_FilterValue, m_FilterOptions) + ")");
sqlBuilder.BuildSoftDeleteClause(sql, " AND (", DataSource, ") ");
parameters = sqlBuilder.GetParameters();
}
else if (!string.IsNullOrWhiteSpace(m_WhereClause))
{
sql.Append(" WHERE (" + m_WhereClause + ")");
sqlBuilder.BuildSoftDeleteClause(sql, " AND (", DataSource, ") ");
parameters = SqlBuilder.GetParameters <NpgsqlParameter>(m_ArgumentValue);
parameters.AddRange(sqlBuilder.GetParameters());
}
else
{
sqlBuilder.BuildSoftDeleteClause(sql, " WHERE ", DataSource, null);
parameters = sqlBuilder.GetParameters();
}
if (AggregateColumns.HasGroupBy)
{
AggregateColumns.BuildGroupByClause(sql, " GROUP BY ", DataSource, null);
}
if (m_LimitOptions.RequiresSorting() && !m_SortExpressions.Any() && StrictMode)
{
throw new InvalidOperationException("Limits were requested without a sort order. Use WithSorting to supply a sort order or disable strict mode.");
}
sqlBuilder.BuildOrderByClause(sql, " ORDER BY ", m_SortExpressions, null);
switch (m_LimitOptions)
{
case PostgreSqlLimitOption.Rows:
sql.Append(" OFFSET @offset_row_count_expression");
parameters.Add(new NpgsqlParameter("@offset_row_count_expression", m_Skip ?? 0));
if (m_Take.HasValue)
{
sql.Append(" LIMIT @limit_row_count_expression");
parameters.Add(new NpgsqlParameter("@limit_row_count_expression", m_Take));
}
break;
}
sql.Append(";");
return(new PostgreSqlCommandExecutionToken(DataSource, "Query Function " + m_Table.Name, sql.ToString(), parameters));
}
/// <summary>
/// Prepares the command for execution by generating any necessary SQL.
/// </summary>
/// <param name="materializer"></param>
/// <returns></returns>
public override CommandExecutionToken <OleDbCommand, OleDbParameter> Prepare(Materializer <OleDbCommand, OleDbParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var sqlBuilder = m_Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyRulesForSelect(DataSource);
if (AggregateColumns.IsEmpty)
{
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
}
//Support check
if (!Enum.IsDefined(typeof(AccessLimitOption), m_LimitOptions))
{
throw new NotSupportedException($"Access does not support limit option {(LimitOptions)m_LimitOptions}");
}
//Validation
if (m_Take <= 0)
{
throw new InvalidOperationException($"Cannot take {m_Take} rows");
}
//SQL Generation
List <OleDbParameter> parameters;
var sql = new StringBuilder();
string?topClause = null;
switch (m_LimitOptions)
{
case AccessLimitOption.RowsWithTies:
topClause = $"TOP {m_Take} ";
break;
}
if (AggregateColumns.IsEmpty)
{
sqlBuilder.BuildSelectClause(sql, "SELECT " + topClause, null, null);
}
else
{
AggregateColumns.BuildSelectClause(sql, "SELECT " + topClause, DataSource, null);
}
sql.Append(" FROM " + m_Table.Name.ToQuotedString());
if (m_FilterValue != null)
{
sql.Append(" WHERE (" + sqlBuilder.ApplyFilterValue(m_FilterValue, m_FilterOptions) + ")");
sqlBuilder.BuildSoftDeleteClause(sql, " AND (", DataSource, ") ");
parameters = sqlBuilder.GetParameters(DataSource);
}
else if (!string.IsNullOrWhiteSpace(m_WhereClause))
{
sql.Append(" WHERE (" + m_WhereClause + ")");
sqlBuilder.BuildSoftDeleteClause(sql, " AND (", DataSource, ") ");
parameters = SqlBuilder.GetParameters <OleDbParameter>(m_ArgumentValue);
parameters.AddRange(sqlBuilder.GetParameters(DataSource));
}
else
{
sqlBuilder.BuildSoftDeleteClause(sql, " WHERE ", DataSource, null);
parameters = sqlBuilder.GetParameters(DataSource);
}
if (AggregateColumns.HasGroupBy)
{
AggregateColumns.BuildGroupByClause(sql, " GROUP BY ", DataSource, null);
}
if (m_LimitOptions.RequiresSorting() && !m_SortExpressions.Any())
{
if (m_Table.HasPrimaryKey)
{
sqlBuilder.BuildOrderByClause(sql, " ORDER BY ", m_Table.PrimaryKeyColumns.Select(x => new SortExpression(x.SqlName)), null);
}
else if (StrictMode)
{
throw new InvalidOperationException("Limits were requested, but no primary keys were detected. Use WithSorting to supply a sort order or disable strict mode.");
}
}
else
{
sqlBuilder.BuildOrderByClause(sql, " ORDER BY ", m_SortExpressions, null);
}
sql.Append(";");
return(new AccessCommandExecutionToken(DataSource, "Query " + m_Table.Name, sql.ToString(), parameters));
}
