public override string SelectSql(SqlTableNameWithAlias tableName, IEnumerable <SqlExpressionWithAlias> columns, string sqlWhere, IEnumerable <SqlJoinDefinition> joins = null, string sqlSortExpression = null, int?start = null, int?numRecords = null, string afterSelect = null)
{
if (start == null && numRecords == null)
{
return(base.SelectSql(tableName, columns, sqlWhere, joins, sqlSortExpression));
}
if (start == null)
{
return(base.SelectSql(tableName, columns, sqlWhere, joins, sqlSortExpression, afterSelect: "TOP " + numRecords));
}
string subQueryAlias = SqlNameGenerator.NextTableAlias();
string rowNumAlias = SqlNameGenerator.NextFieldAlias();
int end = (numRecords == null ? int.MaxValue : (numRecords.Value + start - 1)).Value;
SqlExpressionWithAlias rowNumExpression = new SqlExpressionWithAlias("row_number() over (order by " + sqlSortExpression + ")", rowNumAlias);
string[] parts =
{
"select",
subQueryAlias + ".*",
"from",
"(",
base.SelectSql(tableName,columns.Union(new[] { rowNumExpression }), sqlWhere, joins),
")",
"as",
subQueryAlias,
"where",
rowNumAlias,
"between",
start.ToString(),
"and",
end.ToString(),
"order by",
rowNumAlias
};
return(string.Join(" ", parts));
}
public override string SelectSql(SqlTableNameWithAlias tableName, IEnumerable <SqlExpressionWithAlias> columns, string sqlWhere, IEnumerable <SqlJoinDefinition> joins = null, string sqlSortExpression = null, int?start = null, int?numRecords = null, string afterSelect = null)
{
if (start == null && numRecords == null)
{
return(base.SelectSql(tableName, columns, sqlWhere, joins, sqlSortExpression));
}
if (start == null)
{
return(base.SelectSql(tableName, columns, sqlWhere, joins, sqlSortExpression, numRecords: numRecords));
}
string sql = base.SelectSql(tableName, columns, sqlWhere, joins, sqlSortExpression, numRecords: numRecords);
if (start.Value > 1)
{
sql += " OFFSET " + (start - 1);
}
return(sql);
}
public override string DeleteSql(SqlTableNameWithAlias tableName, string sqlWhere, IEnumerable <SqlJoinDefinition> joins)
{
if (joins != null && joins.Any())
{
string sql = "delete from " + QuoteTable(tableName.TableName) + (tableName.Alias != null ? (" " + tableName.Alias + " ") : "");
sql += "using " + string.Join(",", joins.Select(join => QuoteTable(join.Right.Schema.MappedName) + " " + join.Right.Alias));
if (sqlWhere != null)
{
sql += $" where ({sqlWhere})";
}
sql += " and ";
sql += string.Join(" and ", joins.Select(join => QuoteField(join.Left.Alias + "." + @join.Left.Field.MappedName) + "=" + QuoteField(join.Right.Alias + "." + @join.Right.Field.MappedName)));
return(sql);
}
return("delete from " + QuoteTable(tableName.TableName) + (tableName.Alias != null ? (" " + tableName.Alias + " ") : "") + (sqlWhere != null ? (" where " + sqlWhere) : ""));
}