private Tuple <string, IDictionary <string, object> > GetAggrCommand <TResult>(Expression <Func <T, object> > func, string aggrType, bool isCastType, bool isDistinct)
{
var analysisResult = new ExpressionAnalyzer(func).GetAnalysisResult();
var field = string.Join("", analysisResult.StackList).Replace("[t]", "[t_aggr]");
var typeName = typeof(TResult).Name;
var command = GetSelectCommand(true);
var sql = command.Item1;
var parameters = command.Item2;
var castType = CastType(typeof(TResult));
if (isCastType)
{
field = string.Format("CAST({0} AS {1})", field, castType);
}
if (isDistinct)
{
field = string.Format("DISTINCT {0}", field);
}
sql = string.Format("SELECT {0}({1}) FROM (\r\n{2}) [t_aggr]", aggrType, field, sql);
return(new Tuple <string, IDictionary <string, object> >(sql, parameters));
}
public QueryCommand <T> OrderByDescding(Expression <Func <T, object> > func)
{
var result = new ExpressionAnalyzer(func).GetAnalysisResult();
var field = string.Join("", result.StackList);
if (!orderByConds.ContainsKey(field))
{
orderByConds.Add(field, "DESC");
}
return(this);
}
public Tuple <string, IDictionary <string, object> > GetSelectCommand(bool withoutOrderBy = false)
{
var sqlBuilder = new StringBuilder();
IDictionary <string, object> parameters = null;
// SELECT FROM
var tableName = typeof(T).Name;
var selectFrom = string.Format("SELECT * FROM [{0}] [t] WITH(NOLOCK)", tableName);
sqlBuilder.AppendLine(selectFrom);
// WHERE
if (whereConds.Any())
{
var exp = whereConds[0];
for (int i = 1, iMax = whereConds.Count; i < iMax; i++)
{
exp = exp.And(whereConds[i]);
}
var result = new ExpressionAnalyzer(exp).GetAnalysisResult();
var str = string.Join(" ", result.StackList);
sqlBuilder.AppendLine("WHERE " + str);
parameters = result.ParamList;
}
// ORDER BY
var orderBy = "ORDER BY [t].[Id]";
if (orderByConds.Any())
{
var str = string.Join(", ", orderByConds.Select(x => string.Format("{0} {1}", x.Key, x.Value)));
orderBy = "ORDER BY " + str;
}
// PAGER
if (take.HasValue || skip.HasValue)
{
if (take.HasValue)
{
sqlBuilder.Insert(0, string.Format("SELECT TOP {0} * FROM (", take.Value) + Environment.NewLine);
}
else
{
sqlBuilder.Insert(0, "SELECT * FROM (" + Environment.NewLine);
}
sqlBuilder.AppendLine(") [t_row]");
if (skip.HasValue)
{
sqlBuilder.Replace(selectFrom, string.Format("SELECT *, ROW_NUMBER() OVER ({0}) AS ROW_NUM FROM [{1}] [t] WITH(NOLOCK)", orderBy, tableName));
sqlBuilder.AppendLine(string.Format("WHERE [t_row].[ROW_NUM] > {0}", skip.Value));
}
}
else if (!withoutOrderBy)
{
sqlBuilder.AppendLine(orderBy);
}
return(new Tuple <string, IDictionary <string, object> >(sqlBuilder.ToString(), parameters));
}